Monthly Comparison by Year

S

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?
 
D

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]);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top