Compare this year/qtr/mo to date with prior year/qtr/mo to date

[BMoroneso]

Hi,
I'm trying to create a report that compares revenues received this
year/qtr/mo/wk with revenues received last year in the comparable
year/qtr/mo/wk, and additionally by sales division and sales manager.

I have the data stored in a table with fields: SlsDivID, SlsMgrID, DtRecd
and AmtRecd. I know I will need to use the date part function, but am not
sure how to get the info in the report I need.

format should be:

SlsDiv - SlsMgr - CY YTD - LY YTD - G/L and % - CY QTD - LY QTD - G/L and %
etc etc.

I have tried pivot tables (not working) and various group and total
functions (also not working).

Sum If (like in excel) would work, but I don't think that's available in
Access...

Thanks in advance!!!

 

[KARL DEWEY]

I do not know what your "G/L and %" means but this should start you on the
road to what you want.
SELECT tblSales.SlsDivID, tblSales.SlsMgrID,
Sum(IIf(Format([DtRecd],"yyyy")=Format(Date(),"yyyy"),[AmtRecd],0)) AS [CY
YTD],
Sum(IIf(Format([DtRecd],"yyyy")=Val(Format(Date(),"yyyy"))-1,[AmtRecd],0)) AS
[LY YTD],
Sum(IIf(Format([DtRecd],"yyyyq")=Format(Date(),"yyyyq"),[AmtRecd],0)) AS [CY
QTD], Sum(IIf(Format([DtRecd],"yyyyq")=Val(Format(Date(),"yyyy"))-1 &
Format(Date(),"q"),[AmtRecd],0)) AS [LY QTD]
FROM tblSales
GROUP BY tblSales.SlsDivID, tblSales.SlsMgrID;