compare last year and this years sales

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

Guest

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
 
"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?
 
Mike V T said:
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
Click to expand...

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

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 said:
Mike V T said:
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
Click to expand...

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
Click to expand...
 
That'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.

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

Allen Browne said:
Mike V T said:
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
Click to expand...

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))
Click to expand...
 
Back
Top