Query to extract count on weekly basis for year

[Guest]

Hi,

I have a database that includes all accidents drivers have on a date basis.
In the table I have a field for types of accidents, eg pass injury, object
thrown etc.
I wish to run a query to extract the counts of different types of accidents
based on a weekly basis (Sunday to Saturday) from the beginning of the year.
Eg: Dates 31/12/06 - 06/01/07: Count:7 for Pass injury, etc.

TIA & hope I explained it well enough.

Kevin

 

[John W. Vinson]

Hi,

I have a database that includes all accidents drivers have on a date basis.
In the table I have a field for types of accidents, eg pass injury, object
thrown etc.
I wish to run a query to extract the counts of different types of accidents
based on a weekly basis (Sunday to Saturday) from the beginning of the year.
Eg: Dates 31/12/06 - 06/01/07: Count:7 for Pass injury, etc.

TIA & hope I explained it well enough.

Kevin

Create a Query based on the table. Include a calculated field:

WeekNo: DatePart("ww", [AccidentDate])

The DatePart function has some optional parameters - there are several ways
you can identify the "first week" (it might be the Sunday through Saturday
week containing January 1, or the week containing the first Tuesday, or
various other options; see the VBA Help for DatePart).

Make the query into a Totals query; Group By WeekNo and the injury type, and
Count some field that you know is never null (the table's primary key is a
good choice). Or, make it a Crosstab query; use WeekNo as the Row Header and
injury type the Column Header.

John W. Vinson [MVP]