The data conversion you describe is similar to having a budget worksheet
with accounts down the left hand side and months across the top and
needing to convert it to a single column of values with a column
containing the date for the value.
The Access SQL below performed this task. I assume you could use a
similar technique to recast your data. The source was a table called
'data' and its column headings were Account, Org, Project, Account_name
and then 12 columns of Mth01 to Mth12.
SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/07/2005' as
[month], Mth01 as Budget
FROM Data
union all
SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/08/2005',
Mth02
FROM Data
UNION ALL
SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/09/2005',
Mth03
FROM Data
UNION ALL
<snip
UNION ALL SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/06/2006',
Mth12
FROM Data;
I can not understand what kind of pivot table requirement would require
the sort of transformation of the underlying data in the way you
describe. Care to share?
regards...
--
steven1001
------------------------------------------------------------------------
steven1001's Profile:
http://www.excelforum.com/member.php...o&userid=30757
View this thread:
http://www.excelforum.com/showthread...hreadid=522330