Calculate average interval between dated records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would one calculate the average interval between a set or dates...there could be as few as 2 and as many as 6. Perhaps you would use Dvar or Davg, but i'm unsure. please help. thanks

So far, I created a form that queries the dates i'm looking for. Then, I counted the number of total records. What next?
Thanks much, Melissa
 
Dear Melissa;

There is a trick that simplifies this. Consider this list of dates
and intervals:

1/1/01
1/3/01 2
1/3/01 0
1/10/01 7

You can go to all the work of adding 2 + 0 + 7 to get this. However,
isn't this just the single interval from 1/1/01 to 1/10/01?

Now, to get the first and last dates from the list:

SELECT MIN(YourDate) FROM YourTable
SELECT MAX(YourDate) FROM YourTable

The total interval:

SELECT DateDiff("d", MAX(YourDate), MIN(YourDate)) FROM YourTable

And finally,divide by the number of dates:

SELECT DateDiff("d", MAX(YourDate), MIN(YourDate)) / COUNT(YourDate)
FROM YourTable

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top