query to turn columns to rows

  • Thread starter Thread starter mcnewsxp
  • Start date Start date
M

mcnewsxp

what's the best way to turn this:

ID 6 11 16 18 26 XR 53 54 56 IS39 89
------------------------------------------------
1234 0 0 0 0 Yes 0 0 0 0 0
5678 0 0 0 0 No 0 0 0 0 0

into

Id Type Result
-----------------------
1234 HP06 0
1234 HP11 0
1234 HP16 0

etc?

tia,
mc
 
One approach would be to send the data to Excel and use the Transpose
function.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Try this adding the other fields ---
SELECT YourTable.ID, "HP06" AS Type, YourTable.[6] AS RESULT
FROM YourTable
WHERE YourTable.[6] Is Not Null
UNION ALL SELECT YourTable.ID, "HP11" AS Type, YourTable.[11] AS RESULT
FROM YourTable
WHERE YourTable.[11] Is Not Null
UNION ALL SELECT YourTable.ID, "HP16" AS Type, YourTable.[16] AS RESULT
FROM YourTable
WHERE YourTable.[16] Is Not Null;
 
Yes (but depends on who is doing it...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top