Compare last week sales to this week or last month to this month

[Bert]

Hi everyone,

I have used Access a little and want to know how to compare last week sales
to this week sales or last month to this month etc

Thanks for any replies
Bert

 

[Allen Browne]

Since you want to summarize the data in the table by a date period, you will
need to use a Total query. Since you want to compare the value against
another date range from the same table, you will need to use a subquery to
get the value for the previous period.

Using the Northwind sample database (and ignoring discounts and freight),
this example shows how to get the sales for a month for each product:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
[Order Details].ProductID,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS Sales
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]),
[Order Details].ProductID;

Then adding the subquery to get the value of the previous month's sales for
the same product, you end up with:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
[Order Details].ProductID,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS Sales,
(SELECT Sum([OD].[Quantity]*[OD].[UnitPrice])
FROM Orders AS O INNER JOIN [Order Details] AS OD
ON O.OrderID = OD.OrderID
WHERE (Year(O.OrderDate) = Year([Orders].[OrderDate]))
AND (Month(O.OrderDate) = Month([Orders].[OrderDate])-1)
AND (OD.ProductID = [Order Details].ProductID)) AS PriorMonth
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]),
[Order Details].ProductID;

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

 

[Bert]

Thanks a lot Allen - I will get to work on it

Since you want to summarize the data in the table by a date period, you will
need to use a Total query. Since you want to compare the value against
another date range from the same table, you will need to use a subquery to
get the value for the previous period.

Using the Northwind sample database (and ignoring discounts and freight),
this example shows how to get the sales for a month for each product:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
[Order Details].ProductID,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS Sales
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]),
[Order Details].ProductID;

Then adding the subquery to get the value of the previous month's sales for
the same product, you end up with:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
[Order Details].ProductID,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS Sales,
(SELECT Sum([OD].[Quantity]*[OD].[UnitPrice])
FROM Orders AS O INNER JOIN [Order Details] AS OD
ON O.OrderID = OD.OrderID
WHERE (Year(O.OrderDate) = Year([Orders].[OrderDate]))
AND (Month(O.OrderDate) = Month([Orders].[OrderDate])-1)
AND (OD.ProductID = [Order Details].ProductID)) AS PriorMonth
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]),
[Order Details].ProductID;

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi everyone,

I have used Access a little and want to know how to compare last week
sales
to this week sales or last month to this month etc

Thanks for any replies
Bert