Date grouped by year 1

[Dave177]

I have a database listing appointments of members by ethnicity [Group]- either White or BME. On the [Appointments] table is a [start_date] field. At the moment I have a query which counts members appointed by ethnicity for three years previously to Now(). For example there could be 100 Whites and 25 BMEs. However, now I want to change the query so that if you ran it on the 25th September 2005, in one field in the query would be the number of appointments for the year 26th Sept 04 to 25/09/05 and then in another field would be the number of appointments for 26/09/03 to 25/09/04 and in a third field it would the appointments made in the year 26/09/02 to 25/09/03. How do you separate the totals so that they will appear in different columns according to [start_date]?This will then be used in a report column to demonstrate changes year by year.
Your help would be greatly appreciated!

Dave

 

[PHV]

You may consider the IIf function (3 times).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dave177]

Of course! Thank you very much.

 

[Dave177]

PH,
Also when I come to writing the report would it be possible to have a label at the top of each column with the date eg. 26/09/04 to 25/09/04 depending when the user runs the report?

Dave