Help needed counting dates

[Guest]

Hi,

I have a table whose fields are:
1. date of birth ('dob') and
2. Area (ie geograhical)

I need to have a query divide up the dob's into school years, to then
produce a count of the number of records in each school year (year 1, year 2
etc). Basically I simply need a numbers count for each school yr.

I can specify the dates for each group in critera (between #X#and
#X#...etc), but having done this, access will not let me enter 'Count' in the
totals section - it returns no results.

I believe that it may be possible to produce one sepeate query for each
school year domain, and then run a couting query on these afterwards, but
this will involve 12 queries (I'm looking at 11 school years), wheras I want
to do this in one, and I'm sure it is possible.

Any help appreciated

 

[Guest]

Change [Appointment] to your field.
This is based on whole months for school years.

School Year: IIf(DatePart("m",[Appointment]) Between 8 And
12,DatePart("yyyy",[Appointment]),IIf(DatePart("m",[Appointment]) Between 1
And 5,DatePart("yyyy",[Appointment])-1,0))