How to Create a Reverse Crosstab Query (1 Viewer)

trmacdonal

New member
Local time
Today, 02:16
Joined
Oct 1, 2009
Messages
3
Hello,
I have been trying to solve this issue for a while and think there must be a simple way to do it. I have 16 columns in a table. The first is an Item code, then after that is Size_01 - Size_15. Currently each item appears once and there is an quantity amount in each of the size columns. Is there a simple way to make the table that looks like this:

Item Code Size QTY
AAAA Size_01 6
AAAA Size_02 3
...
AAAA Size_15 4


If anyone has done this before I would be very interested to know how you did it.

Thanks,

Trevor
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Aug 30, 2003
Messages
36,118
You can use a UNION query:

SELECT Item, "Size_01" as Size, QTY
FROM TableName
UNION ALL
SELECT Item, "Size_02" as Size, QTY
FROM TableName
...
 

trmacdonal

New member
Local time
Today, 02:16
Joined
Oct 1, 2009
Messages
3
Thank you for the reply. I think that will work. I didn't know I could use a Union Query to select multiple time from the dame table. I thought to do this I was going to need to create 15 individual queries and then union those together. The only problem is that Size_01 is the name of the heading. The quantity is the value in the Size_01 column. How can I put the heading name next to the item code so that I can Identify which line is which size? Right now this is what I have:

Item Code******Size******QTY
AAA***********6*********6
AAA***********3*********3

Where it should be:

Item Code******Size******QTY
AAA***********1*********6
AAA***********2*********3
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Aug 30, 2003
Messages
36,118
No problem, but I think I goofed it slightly. Try this tweak, if the data looks like I think it does:

SELECT Item, "Size_01" as Size, Size_01 AS QTY
FROM TableName
UNION ALL
SELECT Item, "Size_02" as Size, Size_02 AS QTY
FROM TableName
...
 

Users who are viewing this thread

Top Bottom