Any way to create "reversed" cross tab query?

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
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can do it one of two ways:

1. Make a large UNION query for all 120 Account columns. E.g.:

SELECT Week, "GM_2010_03" As Account, GM_2010_03 As TheValue
FROM table_name

UNION ALL

SELECT Week, "GM_2010_05" As Account, GM_2010_05 As TheValue
FROM table_name

UNION ALL

SELECT Week, "GM_2010_06" As Account, GM_2010_06 As TheValue
FROM table_name

UNION ALL
.... etc. ...

2. You'll have to use a combination of VBA and SQL: a VBA routine to
set up a dynamic SQL statement, and to run the different variations
(each Account number) of the SQL statement.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBST7dm4echKqOuFEgEQJz7ACgrweS6lWi9drxhtQLm4Xm7ZTiLvgAn3vv
wqnhIcDwOkPkARcYX5kU+BKY
=0Tnp
-----END PGP SIGNATURE-----
 
J

John W. Vinson

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.
 
M

Michel Walsh

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
 
J

John Spencer

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
 
Top