I gather that each date is in a separate record?
I believe that (Latest Appointment - Earliest Appointment)/Number of appointment records will give you the same average value.
There are a number of ways to reference these three numbers in order to compute the average. The best method will depend on the structure of your database.
Yes each date is a separate record in a table "appointments"
Each appointment is related to the client only by a "clientID"
which would be the best way to calculate the latest and earliest appointment? Perhaps something like a Get lowest date, Get Highest date? Which function would be best for this do you think?
Min ( YourDate )
Max ( YourDate )
( Max ( Appointments:: Date ) - Min ( Appointments:: Date ) ) / ( Count ( Appointments:: ClientID ) - 1 )
Using the original sample data in the original DD/MM/YYY format:
(17/1/2009 - 1/1/2009 - 1)/(5 -1)
Produces (16 days -1)/4 = 3.75 ?!!
Correct me if Im wrong, but I think there's an extra "-1" in the numerator there.
Yes, it is (or was - I have edited it out). Thanks for noticing.
Yes thats perfect when you get rid of one of the "-1"s.
Thanks very much guys!