Un-Crosstab Query 1

[TCARPENTER]

I have a table with the following fields:

ModelID ArmStyle CushionCount ComponentLoc GradeA GradeB GradeC GradeD GradeE
CP C 1 A 0 27 52 79 104

What I need to do is convert this to:

ModelID ArmStyle CushionCount ComponentLoc Grade Value
CP C 1 A A 0
CP C 1 A B 27
CP C 1 A C 52
CP C 1 A D 79
CP C 1 A E 104

Is there an easy way to do this with a query, or multiple queries? I've playing around for a couple of hours - every time I think I get close I wind getting some funky results, I thought I would break the table into 2 queries, one to get ModelID, ArmStyle, CushionCount, and ComponentLoc and then I get lost...

Any help is greatly appreciated.
Thanks

 

[TCARPENTER]

Should have previewed this sorry...

From this:

ModelID	ArmStyle CushionCount	ComponentLoc	GradeA	GradeB	GradeC	GradeD	GradeE
CP	C	 1	        A	        0	27	52	79	104

To this:

ModelID	ArmStyle CushionCount	ComponentLoc	Grade Value
CP	C	 1	        A	        A     0
CP	C	 1	        A	        B     27
CP	C	 1	        A	        C     52
CP	C	 1	        A	        D     79
CP	C	 1	        A	        E     104

 

[dhookom]

Try a normalizing union query like:

SELECT ModelID, ArmStyle, CushionCOunt, ComponentLoc, "A" as Grade, GradeA as TheValue
FROM TableWithNoName
UNION ALL
SELECT ModelID, ArmStyle, CushionCOunt, ComponentLoc, "B", GradeB
FROM TableWithNoName
UNION ALL
SELECT ModelID, ArmStyle, CushionCOunt, ComponentLoc, "C", GradeC
FROM TableWithNoName
UNION ALL
SELECT ModelID, ArmStyle, CushionCOunt, ComponentLoc, "D", GradeD
FROM TableWithNoName
UNION ALL
SELECT ModelID, ArmStyle, CushionCOunt, ComponentLoc, "E", GradeE
FROM TableWithNoName;

Duane
Hook'D on Access
MS Access MVP

 

[TCARPENTER]

Thanks Duane! That was the ticket - have a star!