I have been trying to find a way to calculate the number of days in a found set that contains one 'Date' field (which is the creation date of the record and is in fact a date-type field).

I have created a database to track medication use and want to be able to find the average number of a particular medication taken between a range of dates. Each record has a "Date" field(date), a "Medication" field (text), and a Quantity (number). When performing a find of a range of dates I would like to be able to calculate the average number of pills(Medication) consumed between the oldest and newest records in the found set. I need to divide the sum of the Quantity by the Total of the Number of Days.

Since the Date field that I need to use for this calculation is located in two separate records, separated by a variable number of records I need the first and last record of the found set to be calculated into "Total Number of Days" field a Sub-summary field. I don't have a problem performing the find and sorting, and I have a sub-summary part created in the layout. I just don't know how to calculate the total number of days.

Though I have been using Filemaker for many years this solution is beyond my ability and may also require a script, I just don't know, after hours of searching through reference books and in the Filemaker Community Discussions boards as well as many failed attempts I have not be able to find anything that applies to my particular application.

Thanks in advance or any help that you might offer,

Rick

Basically, count any field that will never be empty (normally, one would use the table's primary key), because that will tell you how large your group is.

Anyway, just have a look into the attached sample file; that should make things clearer.