Sum by fiscal quarter 1

[planetdrouin]

Hello,

I am relatively new to Access, so...

I have a transaction table, that has a date, description, transaction category and amount. I want to sum the transactions by fiscal quarter and year, where fiscal quaters are Nov-Jan, Feb-Apr, May-Jul, Aug-Oct. The resulting output would look like:

Description Category Yr 01 Qtr1 Qtr2 Qtr3 Qtr4 Yr 02
ABC Rent 56.23 29.56 ...
PG&E Utilities 7.89 5.63 ...

I was able to do this using a crosstab query, however my groupings were based on calendar quarters. My sql included:
Pivot "Qtr" & Format([Date],"q",1,0) In ("Qtr1","Qtr2","Qtr3","Qtr4&quot

How do I do the same thing but with my fiscal quarters and years? Is a crosstab query the best way to do this?

Thanks for the help.

Lawrence

 

[jebry]

Hi!

Write your own function:

Public Function FiscalQuarter(MyDate As Date) As String

Dim strMonth As String

strMonth = Format(MyDate, "mmm&quot

Select Case strMonth
Case "Nov", "Dec", "Jan"
FiscalQuarter = "Qtr1"
Case "Feb", "Mar", "Apr"
FiscalQuarter = "Qtr2"
Case "May", "Jun", "Jul"
FiscalQuarter = "Qtr3"
Case "Aug", "Sep", "Oct"
FiscalQuarter = "Qtr4"
End Select

End Function

Then you use Pivot FiscalQuarter([Date]) In ("Qtr1", "Qtr2", "Qtr3", "Qtr4&quot

BTW, it looks like your field is named Date. If that is the case, you should change it since this is a reserved word in Access and giving your field the same name can confuse Access terribly, try fldDate instead.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[planetdrouin]

Jeff,

Sorry I haven't gotten back to you sooner, but have been out on the road.

Your reply was extremely helpful and works like a charm. I agree about the date, my field was actually named [InvoiceDate], just being lazy in my question.

Lawrence