Comparison Report Help Needed

[TK]

I have a table showing total sales by month for the past two years, broken
down in each month by total of each product. What I want to be able to
accomplish is to prepare a report showing a comparison between Jan 2007 vs.
Jan 2006, etc. for the entire year, as well as be broken down by products as
well.

I don't have any idea on how to start to prepare this report. Any
suggestions would be greatly appreciated.

Thanks,
T

 

[Allen Browne]

Presumably you have a created a Total query that gives you the total per
month per product for this year?

And now you need to add a column that gives the figures from last year. A
subquery could do that. It will depend on your table structure, but the
subquery might be something like this:
(SELECT Sum([Quantity]) AS SumOfQuantity
FROM Sales As Dupe
WHERE Dupe.ProductID = Sales.ProductID
AND Month(Dupe.SaleDate) = Month(Sales.SaleDate)
AND Year(Dupe.SaleDate) = (Year(Sales.SaleDate) - 1))

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html