I'm not sure that I'm interpreting your report example correctly.
When you indicate that you have
5 from SanFrancisco county
Is that 5 men from San Francisco county or 5 of the 40 total men and women who donated in April 2011?
Well I just made that stuff up but its more complex than that. I'm trying to find the easiest way.
We want to know how many patients we saw during time frame...that should be the total number of patient records in the group.
Then of that group we only need to know how many women under 55, women over 55, men under 55,men over 55.
And then of that same group of patients count of (non-gender specifically) income, insurance.
It would pull the data from whatever was entered in the drop downs for each patient record from the patient demographic table.
If possible. Maybe i set it up wrong?
Make any sense?
The key point is in how you want to count the records. The following report format is easy to set up in FileMaker and may do what you want:
Female Patients: 20
Under 55: 5
Over 55: 15
Male Patients: 6
Under 55: 2
Over 55: 4
Total for April 2011: 26
Note that any one patient was counted only one time at the lowest level (age category).
Here's how to set this up from a table of Patients with a found set of all patients who were treated in one month:
Base your report on your patients table. Define a summary field, sPatientCount in this table that computes the "count of" some non-empty field such as PatientID.
Put the treatment date field in the layout header and format it to only display the Month and Year. (This is likely a field from a related table of office visits and you'll need to sort this relationship by date in descending order so that it refers to the most recent office visit for that patient.)
Click on the part label for the Body and press delete to remove it.
Use Part setup.. to add a sub summary part, "when sorted by" your gender field.
Add sPatientCount to this part to compute the total men and total women
Add a sub summary part below it "when sorted by" your age category field.
Put the same sPatientCount field to it to compute the break down by age category
Add a trailing grand summary part to your layout and put sPatientCount in it to compute the grand total count at the end of your report.
To view a monthly report, go to this layout, enter find mode and specify a date such as 4/2011 (US date settings only) or 4/1/2011...4/30/2011 to find all patients with an office visit in April 2011. Then sort your records sorting first by Gender, then by age category to group your records correctly and so that the sub summary parts will appear.
This find and sort process can be scripted to make it faster and easier.
Note: if You try this with a version of FileMaker older than FileMaker 10, you must preview or print the report before you can see the sub summary parts.
"We have a table called PATIENT DEMOGRAPHICS with 10 drop downs"
10 drop-down fields in each record?
Keep in mind that, since each value list appears in the same record, you cannot generate a report which includes the same record in two different value lists. For example: If Mary Johnson was in value list for under 55 and also Female, you cannot get her to appear in both sub summaries since the actual record exists only once.
So if you want a result of:
5 from San Francisco county
18 african american
... then you will need a different approach than sub-summary report. It can be done but I just want to be sure of the format in which you want the results so let's walk through it:
You perform a find for April, 2011. Script then goes to the first value list (maybe Gender) and tallies and generates a line into a variable (holding queue) as:
... then it goes to the next value list (maybe County) and generates:
6 San Francisco
5 San Mateo
10 Santa Cruz
After going through all the value lists, the report for April 2011 will look like:
April 2011 50 people polled:
32 Gender: women
18 Gender: men
11 County: Humbolt
8 County: Lassen
3 County: Mendocino
6 County: San Francisco
5 County: San Mateo
10 County: Santa Cruz
7 County: Shasta
19 Income: High
22 Income: Medium
9 Income: Low
... etc. In this instance, the report result needs to be specific since it all needs to be scripted.
I should add one more thing here: Ideally, the demographics would be a table with the following fields:
Category (income, ethnicity, gender whatever)
Value: high, Mendocino, Men etc
If it were structured as RECORDS instead of fields within records with multiple values within fields then generating your summaries, counts, groupings, reports and demographics would be very simple piece of cake.
So now the ball is in your court ... 1) can you change the structure and 2) if not, what specifically do the results need to look like?
ADDED AFTER SUBMISSION: I should also add that much will depend upon how this data will be grouped comparing these periods to other periods to look for trends. What are the plans for this data beyond this simple report concept?
You dont neccissarily need to change the underlying structure since it will most likely involve scripting anyway you could build a single report output table that looks much like the demographics table LaRetta suggested. You can also add fields to indicate the reporting period etc and produce your report layout off the Report output table.
A report output table may be preferable to Variables because you can manipulate it a bit better and go back to it without having to run the script again. With added key fields like a report run id date and period covered. You could build history to allow you to measure trends. In a sense you are doing some data mining of your raw data to produce some meta-data (data about data) Storing it in tables makes retrieving it faster as your database grows and changes.
I would also consider storing your dropdown values in a reference table that with the fields tablename fieldname and value which will assist in script processing of the data some. It will also allow you to count 0 values rather easily too.
You have made some good points, aammondd.
Since this is static data which will not change, we can manipulate it further instead of using a virtual table (no real concern of multi-user interference and static will be quicker). But the transformation, we both agree, will need to take place. The current structure will not produce the reports required. Thanks for offering your input!
Thanks for your comments.
So maybe some restructuring is needed to generate this kind of demographic report.
LaRetta was right in that there are 10 drop downs for each record...and the kind of report would look like what LaRetta did.
The same person/record would be appearing more than once in the varied drop down lists values if the report worked right.
Betty Rubble: female, asian, over 55, humboldt county, ovarian cancer..etc. There are 10 different drop down and each patient has one answer for each.
@aammondd- "I would also consider storing your dropdown values in a reference table that with the fields tablename fieldname and value which will assist in script processing of the data some. It will also allow you to count 0 values rather easily too." I'm not sure what you mean (again I'm a super beginner) but I think I get the report output table idea.
I'm going to try some stuff out and see what i can do.
Any ideas more than welcome!
What I mean is create a table with the following fields :
Basically you are going to create a list of all your dropdown values and which table and field name they are associated with.
This will allow scripting to loop through this table to processs the information in each of the fields rather than having to build a bunch of case/if statements etc. It will also allow you to find those values that are not present should you wish to.