Union Query Need to combine fields from one table

[natep26]

I have a table called tEm. The fields in the table are BusKey, AnnEm, OzEm, UpEm, MaintEm.

I need to create a table that combines AnnEm, OzEm, UpEm, and MainEm into one field. Not one record, but one field. Example below.

tEm
BusKey AnnEm OzEm UpEm MainEm
01B7_1000 1 2 3 4
01B7_2000 5 6 7 8

NewTable
BusKey Type Value
01B7_1000 AnnEm 1
01B7_1000 OzEm 2
01B7_1000 UpEm 3
01B7_1000 MainEm 4
01B7_2000 AnnEm 5
01B7_2000 OzEm 6
etc
etc

I have a union query set up...

SELECT AnEm as value1
FROM tEm
UNION ALL
SELECT OzEm as value1
FROM tEm
UNION ALL
SELECT UpEm as value1
FROM tEm
UNION ALL
SELECT MainEm as value1
FROM tEm;

...that puts the values in one column correctly, but I have no way of pairing the value with the correct Buskey and Type.

Any help would be appreciated. I realize this isn't considered nomalized behavior, but it has to be this format based on preexisting systems.

Thanks in advance.

 

[Galaxiom]

SELECT BusKey, Type, AnEm as value1
FROM tEm
UNION ALL
SELECT Buskey, Type, OzEm as value1
FROM tEm

The Union query is preforming normilization so you are doing the right thing.

 

[natep26]

Thanks for the reply...It looks close. How do I get the field lables from the old table to populate the "Type" field in the new table?

 

[natep26]

Nevermind...I just replaced "Type" with the actual name in quotes. IE "AnEM", "OzEm", etc