Monthly Comparison by Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Supe - 06 Mar 2008 21:03 GMT

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?

Reply to this Message

Duane Hookom - 06 Mar 2008 22:00 GMT

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

Signature

Duane Hookom
Microsoft Access MVP

> 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
[quoted text clipped - 8 lines]
> 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?

Reply to this Message