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,