Grouping By "Financial Year" 1

[LittleSmudge]

I am working on an Access 97 db that is not of my original design!
The users want some additional funtionality. ( Don't they always )

I have a table like :-
tblAnnualLetter
AnnualLetterId PK ( AutoNumber )
DateSent of Type Date
ReplyNoAction of Type TinyInt
ReplyAction of Type TinyInt
ReplyReturned of Type TinyInt
ReplyNone of Type TinyInt

The DateSent field contains the calendar date that the correspondence was sent.

HOWEVER the users want to report in FINANCIAL Year blocks
Ie. 1st April YearX to 31st March YearX+1

I need a
SELECT [FinancialYear], Sum(Reply .. ..) As [Sum .. ..], etc,
FROM tblAnnualLetter
GROUP BY [FinancialYear]


But I can't fathom out how to get the thing to GROUP BY [FinancialYear]


( I'm quite happy if the data in the [FinancialYear] field is the YearX component ; or anything else that differentiatable.)

Any ideas ?






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

SELECT Year(DateSent)-IIf(Month(DateSent)<=3,1,0) AS FinancialYear, ...
FROM tblAnnualLetter
GROUP BY Year(DateSent)-IIf(Month(DateSent)<=3,1,0)

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

 

[LittleSmudge]

PHV

That is just Sooooooo easy and simple and elegant and why on earth couldn't I see that one !


Thanks PHV




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.