Monthly Comparison by Year

[Supe]

I have a query that gives my the date an item was shipped[Date], and the
quantity shipped[InvQty] by Store[Cust] by Item[UPC]. I want to create a
report that will give a monthly total for each item the last two years with a
separate year column so it would appear as below

UPC1 2007 2008
January 100 200
February 110 80
Marcy 120


Assume I need a formular in the field under the years that would tell it to
only total the InvQty for the year 2007 or 2008. Am I on the right track?

 

[Duane Hookom]

You could try a query like:

SELECT Cust, UPC, Month([ShipDate]) as MthNum,
Sum(Abs(Year([ShipDate]) = Year(Date())-1) * [InvQty]) as PrevYear,
Sum(Abs(Year([ShipDate]) = Year(Date())) * [InvQty]) as CurYear
FROM [Query that gives me...]
GROUP BY Cust, UPC, Month([ShipDate]);