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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thanks a lot Allen - I will get to work on it

Allen Browne said:
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.

Bert said:
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
Click to expand...
Click to expand...
 
Back
Top