Question on Count in Date Range

[rhonda6373]

I am attempting to first calculate the number of days between a date and todays which I have accomplished: TimeElapsed: DateDiff("d",[DtDiscovered],Date()).

I then need to count the number of records that have a calculated number of days (TimeElapsed) above as follows in one query:

between 1-10, between 11-20, 21-30, 30-60, and over 60.

How can I accomplish this?

[ADezii]

@rhonda6373
This can be done by using what is called a Frequency Distribution. Give me a little time, and I'll pu together a simple demo for you.

[rhonda6373]

@ADezii
Great, thank you!

[ADezii]

@rhonda6373
Rather than explain how a Frequency Distribution works, just download the Demo Attachment that I have created for you. Any questions, feel free to ask.

[fdfjc]

@rhonda6373
Hi Rhonda, Access makes it easy to do this in steps. In the first query you have figured the TimeElapsed. In the second query, you can use something like this: IIf([TimeElapsed] Between 29 And 59, 1 , 0) AS 30to60, IIf([TimeElapsed] Between 61 And 89, 1 , 0) AS 60to90 etc. Third query sums up each time period and gives you the total count. You call the last query in your code. I'm not great at explaining, so let me know if this isn't clear.
Brenda

[rhonda6373]

The Frequency Distribution worked great! Thank you so much! We have been working very long hours and this was something a director asked me for yesterday and I did not have the time or the brainpower to figure it out. This allowed me to create the report that was needed.

Brenda, thank you as well! I just now saw your post and will note this solution for future reference as well.