R
Ray
hello colleagues in the MATRIX ...
I have a Sales Trend table storing the previous 12 months sales values.
These are "Rolling Months" in that they are always the previous 12 months.
Since they ROLL I can not store the Month Name (or abbrev) in the Field Name
- rather it is variable (changes - as it rolls each month)
example real values :
M12 = Mar 2008 (most recent month)
M1 = Apr 2007 (12 months ago)
State_Sales_Trend Table fields are :
col 1 = M1_Sales (e.g. 32,000)
col 2 = M2_Sales
...
col 12 = M12_Sales
col 13 = State_ID (e.g. NY)
I have a separate table called "Rolling Months" where I store the
Month-Abbrev in 12 separate fields (M12 = "Mar")
I have a query combining the tables and a report that places the Month Names
on top of each column just fine.
The issue is I have many users that want to use the data in EXCEL format
(because this is all they know and dont want to learn MS Access) and want the
correct column headers inplace - how do I use a field name as a 'label' or
name of the column. The crosstab query does well with 1 value field or uses
data ifrom a 'vertical' formatted table. My data is already 'horizontal' (1
rec has all 12 months) and I just want the column names to be replaced in the
query.
Thanks in Advance....
I have a Sales Trend table storing the previous 12 months sales values.
These are "Rolling Months" in that they are always the previous 12 months.
Since they ROLL I can not store the Month Name (or abbrev) in the Field Name
- rather it is variable (changes - as it rolls each month)
example real values :
M12 = Mar 2008 (most recent month)
M1 = Apr 2007 (12 months ago)
State_Sales_Trend Table fields are :
col 1 = M1_Sales (e.g. 32,000)
col 2 = M2_Sales
...
col 12 = M12_Sales
col 13 = State_ID (e.g. NY)
I have a separate table called "Rolling Months" where I store the
Month-Abbrev in 12 separate fields (M12 = "Mar")
I have a query combining the tables and a report that places the Month Names
on top of each column just fine.
The issue is I have many users that want to use the data in EXCEL format
(because this is all they know and dont want to learn MS Access) and want the
correct column headers inplace - how do I use a field name as a 'label' or
name of the column. The crosstab query does well with 1 value field or uses
data ifrom a 'vertical' formatted table. My data is already 'horizontal' (1
rec has all 12 months) and I just want the column names to be replaced in the
query.
Thanks in Advance....