I need to convert some fields and data into a seperate table

[JethroUK©]

I need to convert some fields and data:


Pri Key CV JS MS - etc x 9

15 X X X
16 X X -
17 - X
18 X X X
19 X X X
20 X X X


into a seperate table:

Foreign Key Module Stat

15 CV X
15 JS X
15 MS X
16 CV X
16 JS X
16 MS -
deedaa
dee daa

Any ideas?

 

[John Spencer]

UNION queries are your friend.

SELECT [Pri Key] as FKey, "CV" as Module, CV as Stat
FROM [Your Table]
UNION ALL
SELECT [Pri Key], "JS" as Module, JS
FROM [Your Table]
UNION ALL
SELECT [Pri Key], "MS" as Module, MS
FROM [Your Table]
....
UNION ALL
SELECT [Pri Key], "X" as Module, X
FROM [Your Table]

That will normalize the data. Then you can either use the saved union query
as the source for an append or make table query or just use the union query
when you need the normalized data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JethroUK©]

Great Thanks

UNION queries are your friend.

SELECT [Pri Key] as FKey, "CV" as Module, CV as Stat
FROM [Your Table]
UNION ALL
SELECT [Pri Key], "JS" as Module, JS
FROM [Your Table]
UNION ALL
SELECT [Pri Key], "MS" as Module, MS
FROM [Your Table]
...
UNION ALL
SELECT [Pri Key], "X" as Module, X
FROM [Your Table]

That will normalize the data. Then you can either use the saved union
query as the source for an append or make table query or just use the
union query when you need the normalized data.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I need to convert some fields and data:


Pri Key CV JS MS - etc x 9
15 X X X
16 X X -
17 - X
18 X X X
19 X X X
20 X X X


into a seperate table:

Foreign Key Module Stat

15 CV X
15 JS X
15 MS X
16 CV X
16 JS X 16
MS -
deedaa
dee daa

Any ideas?