compare last year and this years sales

compare last year and this years sales

Post by TWlrZSBWIF » Tue, 07 Dec 2004 12:57:02


I want to design a report which shows last years sales in one column and this
years sales in another, in rows showing individual sales and a total at the
bottom. Thanks
 
 
 

compare last year and this years sales

Post by Duane Hook » Tue, 07 Dec 2004 13:15:23

"in rows showing individual sales" suggests detailed records that are from a
particular date, not this year and last year. Perhaps you could provide
about 10-12 records and how you would like them to appear in the report?

--
Duane Hookom
MS Access MVP

 
 
 

compare last year and this years sales

Post by Allen Brow » Tue, 07 Dec 2004 13:33:41


Here's a couple of solutions. Use the Northwind sample database to see how
they work, and you can then create your own.

The crosstab query gives results for all sales in both years. However, it
uses the years as field names, so it makes it harder to create a report when
the field names are not consistent over the years.

The subquery gives consistent field names ("ThisYear" and "LastYear"), but
only lists products sold in this year and so may miss products that became
obsolete during last year. When you create a report based on a query with a
subquery, you may also hit a snag with "multi-level group-by not allowed".

Solution 1: Crosstab
================
1. Create a query into the Orders and Order Details tables.

2. Choose Crosstab on the Query menu.
Access adds Total and Crosstab rows to the grid.

3. Drag ProductID into the grid from the Order Details table.
In the Crosstab row, choose Row Heading.

4. Type this into the next column in the Field row:
TheYear: Year([OrderDate])
In the Crosstab row, choose Column Heading.

5. Drag Quantity into the grid from the Order Details table.
In the Total row under this field, choose Sum.
In the Crosstab row, choose Calue.

6. Drag OrderDate into the grid from the Orders table.
In the Total row, choose Where.
In the Criteria row, enter this expression to limit the field to this year
and last year:
Between DateSerial(Year(Date())-1,1,1) And
DateSerial(Year(Date()),12,31)


Solution 2: Subquery
================
1. Create a query into the Orders and Order Details tables.

2. Depress the Totals icon on the toolar (upper sigma icon.
Access adds a Total row to the grid.

3. Drag ProductID into the grid from the Order Details table.
Accept Group By in the Total row under this field.

4. Drag OrderDate into the grid from the Orders table.
In the Total row, choose Where.
In the Criteria row, limit this to this year by entering:
Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)

5. Drag Quantity into the grid from Order Details.
(Optional) Insert "ThisYear:" without the quotes in front of Quantity.
In the Total row under this field choose Sum.

6. Paste this into the next column in the Field row.
In the Total row, choose Expression.
This is a subquery that calculates the total for the previous year.
The expression is:
LastYear: (SELECT Sum(Quantity) AS LastYear
FROM Orders AS O INNER JOIN [Order Details] AS OD
ON O.OrderID = OD.OrderID
WHERE (OD.ProductID = [Order Details].ProductID)
AND (O.OrderDate Between DateSerial(Year(Date())-1,1,1)
And DateSerial(Year(Date()),1,0))

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

compare last year and this years sales

Post by hermi » Wed, 08 Dec 2004 00:20:35

llen

FYI
I read the newsgroup frenquenly and try to do the examples to learn more.
In this case I tried solution 1 and have no records returned? Think there
are no sales/orders in this year and previous year in the northwind
database. Solution 2 gives a syntax error?

Herman

"Allen Browne" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
when
a
DateSerial(Year(Date()),12,31)



 
 
 

compare last year and this years sales

Post by Allen Brow » Wed, 08 Dec 2004 01:03:28

hat's correct. There are no sales in Northwind for the last couple of years
unless you enter them.

The expression at step 6 of solution 2 needs another closing bracket at the
end.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...