Count of values by month & day over multiple years?

[Guest]

I need to count the number of incidents that occurred for a given month/day
over a period of years.
Eg. How many incidents occurred on 7/4 from 2000 to 2005?

I would also like to do this for time periods such as weeks.
Eg. How many incidents occurred between 12/1-12/7 from 2000 to 2005?

Thanks
dd

 

[Allen Browne]

Presumably you have a table with a date/time field. Will assume its name is
IncidentDate.

In query design, type this into a fresh column in the Field row:
TheDay: Day([IncidentDate])
In another column:
TheMonth: Month([IncidentDate])
In the Criteria row under TheDay, enter:
4
In the Criteria row under TheMonth, enter:
7
Drag IncidentDate into the output grid, and in the Criteria row:
Between #1/1/2000# And #12/31/2005#

It would actually be more efficient to use several of the Criteria rows
under IncidentDate, e.g.:
Between #12/1/2000# And #12/7/2000#
Between #12/1/2000# And #12/7/2001#
Between #12/1/2000# And #12/7/2002#
Between #12/1/2000# And #12/7/2003#
Between #12/1/2000# And #12/7/2004#
Between #12/1/2000# And #12/7/2005#
Access interprets the multiple criteria rows as OR.

 

[Michel Walsh]

Hi,



Slow, but easy:

SELECT COUNT(*) FROM myTable
WHERE Format(myDate, "mmdd") = "0407"


or maybe a little bit faster (but not much)


WHERE DateAdd("yyyy", 2000-Year(myDate), myDate) = #07-04-2000#





Hoping it may help,
Vanderghast, Access MVP