How to simulate UNPIVOT in Access?
Unfortunately there is no easy way to do this with access. You can do this by using a UNION
to get each value
SELECT ID, A, B, C, [Key 1] As key
FROM Table
WHERE [Key 1] = 3
UNION ALL
SELECT ID, A, B, C, [Key 1] As key
FROM Table
WHERE [Key 1] = 57
UNION ALL
SELECT ID, A, B, C, [Key 2] As key
FROM Table
WHERE [Key 2] = 199
UNION ALL
SELECT ID, A, B, C, [Key 2] As key
FROM Table
WHERE [Key 2] = 234
UNION ALL
SELECT ID, A, B, C, [Key 3] As key
FROM Table
WHERE [Key 3] = 452
This query ...
SELECT ID, A, B, C, [Key 1] AS key_field
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 2] AS key_field
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 3] AS key_field
FROM tblUnpivotSource;
... returns this recordset (using your sample table values as tblUnpivotSource) ...
ID A B C key_field
-- - - - ---------
1 x y z 3
2 x y z 57
1 x y z 199
2 x y z 234
1 x y z 452
2 x y z 452