grouping on dates

[SJ]

Hi

Is it possible to query and count the number of records where a date field
is between 01 August and 31 July each year for the past five years.

I am looking to end us with 5 totals

Cheers
A

 

[[MVP] S.Clark]

Your question is confusing in that you've listed an entire year. It's a
fiscal year, but an entire year, nonetheless.

So, the basics are below, and you can work with it from there.

Select Count(*) from tablename WHERE Year(DateField) < (Year(Date) - 5)

 

[Duane Hookom]

I think you should be able to add 5 months or subtract 7 months to/from the
date field and then find the year of that date for grouping.

 

[Alex H]

apologies for confustion

The year in question is an acadmeic year running from 01/08 to31/07. We
might have records created between eg 01/08/04 and 31/07/05 and I need to
group themn together. Likewise for records that have been created between
01/08/03 and 31/07/045 need to be groupedtogether.

Tks
A

 

[Duane Hookom]

That's what I expected and why I suggested. Did you try it? Did it work?