Getting results in Rows from columns?

[Guest]

Hi all,

Really hoping you can help -- I just received a number of spreadsheets which
I imported into a single table in Access 2000. The basic structure of the
sheets (and the table) is as follows:

Item # | Color # | SM | MD | LG | XL
12345 | 0506 | 45 | 30 | 50 | 7
99980 | 1009 | 11 | 22 | 33 | 44

(The key would be a combo of Item # and Color #, though for this case, I've
decided to forgo using a Primary key).

I want to write a query which will return the above table as follows:

12345 | 0506 | SM | 45
12345 | 0506 | MD | 30
12345 | 0506 | LG | 50
12345 | 0506 | XL | 7
99999 | 1009 | SM | 11
99999 | 1009 | MD | 22
99999 | 1009 | LG | 33
99999 | 1009 | XL | 44

I've tried using cross-tab queries, but I don't think that's the way to go.
Any suggestions or ideas would be *immensely* appreciated! I'd be happy to
explore different ways of importing the data as well.


Thanks in advance!!

 

[Ken Snell [MVP]]

To "normalize" data from multi-column to multi-row, use a UNION query:

SELECT [Item #] AS ItemNumber, [Color #] AS ColorNumber,
"SM" AS SizeID, [SM] As SizeCount FROM TableName
UNION ALL
SELECT [Item #] AS ItemNumber, [Color #] AS ColorNumber,
"MD" AS SizeID, [MD] As SizeCount FROM TableName
UNION ALL
SELECT [Item #] AS ItemNumber, [Color #] AS ColorNumber,
"LG" AS SizeID, [LG] As SizeCount FROM TableName
SELECT [Item #] AS ItemNumber, [Color #] AS ColorNumber,
"XL" AS SizeID, [XL] As SizeCount FROM TableName
ORDER BY ItemNumber, ColorNumber, SizeID;