Access 2k report based on cross-tab query

  • Thread starter Thread starter WB
  • Start date Start date
W

WB

I have a cross-tab query that produces the following sample data

Year, Store A, Store B, Store C, Store D, etc.

Each row is a summary of sales for a specified Date Range grouped by year

I want to add calculated values to each Store so the end result would be
like this

Year, Store A, % increase over previous year, Store B, % increase over
previous year, etc.

2007 $162,493.10 7% $68,551.50 17% $18,619.00 -11% $49,745.30 -3%
$25,577.30 13%
2006 $151,325.70 11% $57,200.70 -13% $20,747.90 1% $51,006.10 47%
$22,371.00 -2%
2005 $135,028.00 $64,667.10 $20,587.40 $26,970.70 $22,802.80



I derived the above table by copying and pasting into Excel and then
inserting the additional columns and creating the calculations. I want to
have Access do all of this within the report. Is it possible? Any
suggestions?

WB
 
You should be able to calculate the values in your report by adding text
boxes bound to the calculation.
 
The Detail Section of the report has the bound fields in a single row (Year,
Store A, Store B, Store C, etc.). If I place a text box in the detail
section how do I tell it to calculate between year (Row) 2006 and year (Row)
2007? I can't just say [Store A] / [Store A], the report doesn't know what
specific values I am trying to divide.

WB
 
My mistake, I thought years were column headings.
I suppose one kludgy method would be to create a query based on two copies
of the same crosstab. Join them based on [Year] and [Year] -1.
--
Duane Hookom
Microsoft Access MVP


WB said:
The Detail Section of the report has the bound fields in a single row (Year,
Store A, Store B, Store C, etc.). If I place a text box in the detail
section how do I tell it to calculate between year (Row) 2006 and year (Row)
2007? I can't just say [Store A] / [Store A], the report doesn't know what
specific values I am trying to divide.

WB
Click to expand...
 
Back
Top