query 2 years of data from same table

[Guest]

Hello,
i have a table with sales data in by month and year
I would like to query the table so that for each
line item i pull current month sales and last month sales so that i can
use this in a columnar report listing current year and last year side by side.

Any ideas without having to make multiple querie defs
Than ks

 

[Allen Browne]

Use a subquery to sum the value from the previous year.

This example works with the Northwind sample database. You can create a
query there, switch to SQL View (View menu), paste the statement in, and
then switch to design view to see what the query looks like.

It is a normal totals query that groups by the year the the month and sums
the quantity. Then the subquery is like another SELECT query statement that
sums the value from a duplicate copy of the tables, for the same month
number 1 year earlier:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].Quantity) AS SumOfQuantity,
(SELECT Sum([Quantity])
FROM Orders AS A INNER JOIN [Order Details] AS B
ON A.OrderID = B.OrderID
WHERE A.OrderDate Between
DateSerial(Year([Orders].[OrderDate])-1, Month([Orders].[OrderDate]), 1)
And DateSerial(Year([Orders].[OrderDate])-1,
Month([Orders].[OrderDate])+1, 0)) AS PriorYear
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066