stack or union multiple fields in MS Access

Beginner’s question here… I have a table of tree measurements being 3 fields: – ID, Diameter_1, Diameter_2

& I wish to get to these 3 fields: – ID, DiameterName, DiameterMeasurement Input and Desired Output

SELECT DISTINCT ID, Diameter_1
FROM tblDiameters
UNION SELECT DISTINCT ID, Diameter_2
FROM tblDiameters;

Though it results in only 2 fields. How may the field: – DiameterMeasurement be brought in?

Many thanks 🙂

Answer

You were on the right track to use a union. Here is one viable approach:

SELECT ID, 'Diameter_1' AS DiameterName, Diameter_1 AS DiameterMeasurement
FROM tblDiameters
UNION ALL
SELECT ID, 'Diameter_2', Diameter_2
FROM tblDiameters
ORDER BY ID, DiameterName;