Union Query: one field repeated, 18 fields stacked

[Mtdew4243]

Hello,

I have seen the post on Union All and manage to make that work. My need has a twist in that I want the current primary key to be repeated in each new record. I have records containing 19 fields, a Carton ID and 18 Serial Numbers. I want the Carton ID repeated in each new record (Col1) and the unique Serial Numbers in Col2.

This combines the serial numbers in one column... how do I add the Carton ID to its own column?
SELECT MasterCarton.[SN:1]
FROM MasterCarton
UNION ALL
SELECT MasterCarton.[SN:2]
FROM MasterCarton
UNION ALL
SELECT MasterCarton.[SN:3]
FROM MasterCarton;


Col1 Col2 Col3 Col4
Dave x1 x2 x3
Bob y1 y2 y3

Converted to:

Col1 Col2
Dave x1
Dave x2
Dave x3
Bob y1
Bob y2
Bob y3

Enough detail? Thanks!

 

[Pyro]

It seems as though your MasterCarton table has not been normalized. I would advise looking into that before going down the path of union queries.

That said:

SELECT MasterCarton.[CartonID], MasterCarton.[SN:1]
FROM MasterCarton
UNION ALL
SELECT MasterCarton.[CartonID], MasterCarton.[SN:2]
FROM MasterCarton
UNION ALL 
SELECT MasterCarton.[CartonID], MasterCarton.[SN:3]
FROM MasterCarton;
...

 

[Mtdew4243]

To Pyro,

HUGE Thanks! That worked perfectly. (It also reminded me of where to look in my class notes to the solution. )

MtDew