Data mining

[KimTong via AccessMonster.com]

Hi,

I'd like to change data column to row, it's the apposite way with cross tab
query. These are the sample of data:

Product NY NJ CT
AAA 3 5 4
BBB 5 10 6
CCC 6 4

And I'd like to change data above become like these:

Product State Qty
AAA NY 3
AAA NJ 5
AAA CT 4
BBB NY 5
BBB NJ 10
BBB CT 6
CCC NY 6
CCC NJ 4

Thanks in advance

 

[Guest]

You could use a union query

SELECT Product, "NY" as State, [NY] As Qty
FROM tblNoNameGiven
WHERE [NY] is not Null
UNION ALL
SELECT Product, "NJ", [NJ]
FROM tblNoNameGiven
WHERE [NJ] is not Null
UNION ALL
SELECT Product, "CT", [CT]
FROM tblNoNameGiven
WHERE [CT] is not Null;