MSAccess - Design query to group by anniversary date.

Question

Hi,

Lets say I have a table with these fields

LeaveDate
LeaveType

I want to write a query that groups by an annivesary date.

For example say 8th Feb.

So for this year any dates after 8 Feb would be "2010" and any dates before 8 Feb would show "2009".

I want this to occur for all years data.

Understand??

Malcolm

Answer 1

You could create a query with a calculated column for anniversary date, then group by that column.

Answer 2

Here is how I did it

SELECT Year([tblFoo]![Leave_date])-IIf(DateDiff("d",[tblFoo]![Leave_date],DateSerial(Year([tblFoo]![Leave_date]),2,8))>0,1,0) AS Year_group, Count(tblFoo.ID) AS CountOfID

FROM tblFoo

GROUP BY Year([tblFoo]![Leave_date])-IIf(DateDiff("d",[tblFoo]![Leave_date],DateSerial(Year([tblFoo]![Leave_date]),2,8))>0,1,0);

This counts the number of records for each “Year”. We use something similar for working out birthday years which change from person to person. In that case use can just replace the fixed 2 and 8 with the month and day they were born