How to show yes/no fields as values in MS Access?

Question

I'm new to SQL and having a hard time to query the following table

into something like this

I know UNION is involved here but I can't seem to make this work. Kindly help

Answer 1

You may try something along the lines of the following:

SELECT
    RESP_ID,
    RESP_LVL
FROM
(
    SELECT RESP_ID, 1 AS RESP_LVL FROM yourTable WHERE LEVEL1 = 'Y' UNION ALL
    SELECT RESP_ID, 2 AS RESP_LVL FROM yourTable WHERE LEVEL2 = 'Y' UNION ALL
    SELECT RESP_ID, 3 AS RESP_LVL FROM yourTable WHERE LEVEL3 = 'Y' UNION ALL
    SELECT RESP_ID, 4 AS RESP_LVL FROM yourTable WHERE LEVEL4 = 'Y' UNION ALL
    SELECT RESP_ID, 5 AS RESP_LVL FROM yourTable WHERE LEVEL5 = 'Y' UNION ALL
    SELECT RESP_ID, 6 AS RESP_LVL FROM yourTable WHERE LEVEL6 = 'Y' UNION ALL
    SELECT RESP_ID, 7 AS RESP_LVL FROM yourTable WHERE LEVEL7 = 'Y'
) t
ORDER BY
    RESP_ID,
    RESP_LVL;

I am assuming here that your checkboxes are backed by a CHAR(1) field, which would have the values of Y or N. If not, then you would have to change slightly the logic I used above.