Comparing current year and prior year data in one report

Comparing current year and prior year data in one report

Post by c3doaXR » Sun, 22 Feb 2004 05:21:06


I do a lot of reporting which compares summary information for this year with summary information for the same period last year, but I cannot figure out how to show it on one page in a report

Example

Jan 04 Jan 0
Sales XXXX XXX
Discounts XXXX XXXX
 
 
 

Comparing current year and prior year data in one report

Post by bGFi » Sun, 22 Feb 2004 05:51:05

Have you tried a crosstab report ?

 
 
 

Comparing current year and prior year data in one report

Post by c3doaXR » Sun, 22 Feb 2004 07:11:08

No, I'm not familiar with crosstab reports. Can you give me an example of how it would work?
 
 
 

Comparing current year and prior year data in one report

Post by Duane Hook » Sun, 22 Feb 2004 09:59:50

I can't figure out how to do this without knowing how your data is
organized. I'm not sure how we can tell you how to get somewhere if we don't
know where you are at. You can group by fields and total other fields.

--
Duane Hookom
MS Access MVP




with summary information for the same period last year, but I cannot figure
out how to show it on one page in a report.
 
 
 

Comparing current year and prior year data in one report

Post by c3doaXR » Wed, 25 Feb 2004 04:21:09

My data fields are: [store#] [date] [gross sales] [discounts] [sales tax] [payment recd] etc. (approx 30 total fields)
I have one row of data for each store for each date. I just have one large table with all stores and dates. I know how to write a query that will give me the raw data for 1 store for this year and last year both, but I don't know how to split that into 2 separate columns on a report. My query results look like this

Store# Date Gross Sales Discounts Ta
1 1/1/03 100 10
1 1/2/03 200 20 1
1 1/1/04 500 50 2
1 1/2/04 600 60 3

I want my report to look like this

Store# 1 2003 200
Gross Sales 300 110
Discounts 30 11
Tax 15 5

I hope this is enough info.
 
 
 

Comparing current year and prior year data in one report

Post by Duane Hook » Wed, 25 Feb 2004 12:52:32

I would probably create a query like
SELECT Year([Date]) as SalesYear, Sum([Gross Sales]) as GrossSalesSum,
Sum([Discounts]) as DiscountsSum, Sum([Tax]) as TaxSum
FROM qselYourQuery
GROUP BY Year([Date]);
Then create a multiple column report that goes across then down. To create
the labels on the left, refer to this KB article
http://www.yqcomputer.com/ ;en-us;208491&Product=acc.

--
Duane Hookom
MS Access MVP




[payment recd] etc. (approx 30 total fields).
large table with all stores and dates. I know how to write a query that
will give me the raw data for 1 store for this year and last year both, but
I don't know how to split that into 2 separate columns on a report. My
query results look like this: