How do I combine multiple columns of data into one column.

[Guest]

I would like to take the data from a query in the columns below:

Item Number Facility 1 Qty Facility 2 Qty
Facility etc...
1234 50 100
4567 80 75

and change it to this:

Item Number Facility Qty
1234 1 50
1234 2 100
4567 1 80
4567 2 75

 

[Duane Hookom]

You can use a UNION query
SELECT ItemNumber, 1 as Facility, [Facility1Qty] as Qty
FROM YourQuery
UNION ALL
SELECT ItemNumber, 2, [Facility2Qty]
FROM YourQuery
UNION ALL
SELECT ItemNumber, 3, [Facility3Qty]
FROM YourQuery
--etc--