Any way to create "reversed" cross tab query?

  • Thread starter Thread starter Luda
  • Start date Start date
L

Luda

Please see example below. Is there is a quick way to create botom table from
the top one? The only thing i know is to write 3 select and than 3 append
queries. But for my task i'll have to create about 120.

Week GM_2010_03 GM_2010_05 GM_2010_06
1/1/2010 562.02 702.41 2641.32
1/8/2010 562.02 702.41 2641.32
1/15/2010 562.02 702.41 2641.32
1/22/2010 562.02 702.41 2641.32
1/29/2010 562.02 702.41 2641.32

Week Value Account
1/1/2010 562.02 GM_2010_03
1/8/2010 562.02 GM_2010_03
1/15/2010 562.02 GM_2010_03
1/22/2010 562.02 GM_2010_03
1/29/2010 562.02 GM_2010_03
1/1/2010 702.41 GM_2010_05
1/8/2010 702.41 GM_2010_05
1/15/2010 702.41 GM_2010_05
1/22/2010 702.41 GM_2010_05
1/29/2010 702.41 GM_2010_05
1/1/2010 2641.32 GM_2010_06
1/8/2010 2641.32 GM_2010_06
1/15/2010 2641.32 GM_2010_06
1/22/2010 2641.32 GM_2010_06
1/29/2010 2641.32 GM_2010_06


Thank you
 
Please see example below. Is there is a quick way to create botom table from
the top one? The only thing i know is to write 3 select and than 3 append
queries. But for my task i'll have to create about 120.

Week GM_2010_03 GM_2010_05 GM_2010_06
1/1/2010 562.02 702.41 2641.32
1/8/2010 562.02 702.41 2641.32
1/15/2010 562.02 702.41 2641.32
1/22/2010 562.02 702.41 2641.32
1/29/2010 562.02 702.41 2641.32

Week Value Account
1/1/2010 562.02 GM_2010_03
1/8/2010 562.02 GM_2010_03
1/15/2010 562.02 GM_2010_03
1/22/2010 562.02 GM_2010_03
1/29/2010 562.02 GM_2010_03
1/1/2010 702.41 GM_2010_05
1/8/2010 702.41 GM_2010_05
1/15/2010 702.41 GM_2010_05
1/22/2010 702.41 GM_2010_05
1/29/2010 702.41 GM_2010_05
1/1/2010 2641.32 GM_2010_06
1/8/2010 2641.32 GM_2010_06
1/15/2010 2641.32 GM_2010_06
1/22/2010 2641.32 GM_2010_06
1/29/2010 2641.32 GM_2010_06
Click to expand...

120 because you have 120 fields in your spreadsheet-style table? Ouch!

A "Normalizing Union Query" is the trick here:

SELECT [Week], [GM_2010_03] AS Value, "GM_2010_03" AS Account
FROM tablename
WHERE [GM_2010_03] IS NOT NULL
UNION ALL
SELECT [Week], [GM_2010_05], "GM_2010_05"
FROM tablename
WHERE [GM_2010_05] IS NOT NULL
UNION ALL
SELECT [Week], [GM_2010_06], "GM_2010_06"
FROM tablename
WHERE [GM_2010_06] IS NOT NULL
UNION ALL
....

<etc etc>

Leave out the WHERE clause if the field value will never be missing, it'll
speed things up. And be sure to use UNION ALL (which includes all records)
rather than UNION, which removes duplicates - an expensive step.

Base an Append query on the UNION query.

With 120 fields I suspect you'll get the dreaded "Query Too Complex" error if
you do this all in one go - you may need three or four for subsets of the
fields. It shouldn't be too hard to automate constructing the SQL of the query
from the field list, either in VBA or in the text editor of your choice.
 
With Jet, you can use a UNION:

SELECT week, GM_2010_03 AS Value, "GM_2010_03" AS Account FROM xtab
UNION ALL
SELECT week, GM_2010_05, "GM_2010_05" FROM xtab
UNION ALL
SELECT week, GM_2010_06, "GM_2010_06" FROM xtab



Vanderghast, Access MVP
 
As people have said you can try a UNION query. HOWEVER, with 3 fields in each
section of the UNION query * 120 (= 360) you may run into a query limit and
get an error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top