count records for 12 month period

[Guest]

Hi,

I need a query that will count the number of records that will be returned
over the last 12 month period from Now() or similar.
The field I want to count is "Staff No". The other relevant field is "Date".
This field is in the format "dd/mm/yyyy" for Australia.
I need to count how many entries (records) there are, based on the "Staff
No" field, for the last 12 months. I also need this to be a rolling 12 month
period.
The problem I'm having is it returns a Count of "1" for every "Date" when I
do a "DateDiff" or "DateAdd" criteria instead of a total for that individual
"Staff No" for the 12 month period.
Your help will be much appreciated and help me avoid a lot of sleepless
nights.

TIA,
Kevin

 

[Guest]

I also need this to be a rolling 12 month period.
Use this for criteria --
Between DateAdd("m",-12,Date()-Day(Date())+1 And Date()-Day(Date())

 

[Guest]

Thanks Karl,

The criteria you gave me gave an accurate 12 month rolling period, except I
had to add a ")" after the "+1".
However, I need it to count the number of times a "Staff No" appears in the
field. E.g. Staff No 123456 may appear 14 times over a 12 month period. I
need it to show a count of 14, not a count of 1 "14" times for each date it
occurs on.
I think I may have got it though. I created another query based on the 1st
which supplies a count based on the "Staff No" and "count" but exluding the
"Date" field.
A bit long-winded though. If you can think of a quicker way I'd much
appreciate it.

Thanks heaps,

Kevin

 

[Guest]

If you do not need the dates then use a totals query that does not display
the dates. Change the Group BY to Where for the date field and the dates
will not display. You can output your parameter entry by adding columns like
this --
Start Date: DateAdd("m",-12,Date()-Day(Date())+1)

End Date: Date()-Day(Date())