Sounds like you want a summary report with just one summary row for each specialty even though you have many records for each specialty. THat correct?
Do you know how to create a summary report?
- Define a "count of" summary field
- Create a layout based on your table.
- Change the Body layout part in to a Sub summary part sorted on specialty.
- Put your summary field in this part along with date and specialty.
- Enter browse mode, find the records in the date range you want and then sort your records by specialty to group them together.
For more on summary reports: Creating Filemaker Pro summary reports--Tutorial
thanks for your help.
Actually the table has more fields than those originally listed but i just wanted to sum the specialty field. I have a separate related table for specialty and this has a count field in it which returns the total number of specialty counts in the other table but i cannot for the life of me get the numbers for any date period.
i've tried your method (i think) and that just gives me the total numer of specialty occurences (the number of times the field has been occupied) but not the number of the different types of specialty. i'm not explaining this very well so ;
2 tables liked by; Patients::Specialty = Surgical specialty::specialty
with the count field in the 2nd table being Count(Patients::specialty)
the date only exists in the Patients table
btw the summary field you suggested is for count of specialty?
i've just read the last part about sorting and guess what......thanks so much
If you place the field in a sub summary layout part with no body, ( this is one of the latter "tricks" toward the end of the article whose link I posted ) you'll get a subtotal for each type of specialty if you set your layout up for this type of grouping. The counting summary field can refer to any field in the table that is never empty and you'll get the same result.
You can also define a different relationship between Surgical Specialty and patients that includes addtional field pairs so that this relationship matches both by specialty and also by a pair of dates entered in global date fields in the Surgical Specialty table. A Count function that refers to this new table occurrence will give you the same totals for a report based on the Surgical Specialty table.