Comparing current year and prior year data in one report

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

Guest

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
 
No, I'm not familiar with crosstab reports. Can you give me an example of how it would work?
 
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


swhite said:
I do a lot of reporting which compares summary information for this year
Click to expand...
with summary information for the same period last year, but I cannot figure
out how to show it on one page in a report.
 
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.
 
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://support.microsoft.com/default.aspx?scid=kb;en-us;208491&Product=acc.

--
Duane Hookom
MS Access MVP


swhite said:
My data fields are: [store#] [date] [gross sales] [discounts] [sales tax]
Click to expand...
[payment recd] etc. (approx 30 total fields).
I have one row of data for each store for each date. I just have one
Click to expand...
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:
 
Back
Top