MS Access From Wide Format to Long Format New variable Union ALL

I’m a newbie at MS Access and need some help. I have a table that looks like this:

ID	HDD	CDD	HDDKEY	CDDKEY
186	-1	-1	1	5

and would like the following:

ID	HDD	CDD	KEY
186	-1	0	1
186	0	-1	5

My code -that does not work:

SELECT 
TABL1.ID, 
TABL1.HDD, 
TABL1.CDD, 
'KEY'  = HDDKEY
FROM TABL1

UNION ALL

SELECT 
TABL1.ID, 
TABL1.HDD, 
TABL1.CDD, 
'KEY'  = CDDKEY
FROM TABL1

WHERE (((TABL.ID)=18676));```

Thank you!

Answer

You are close

SELECT TABL1.ID, TABL1.HDD, 0 AS CDD, HDDKEY as [Key]
FROM TABL1
WHERE ID = 186
UNION ALL
SELECT TABL1.ID, 0 AS HDD, TABL1.CDD, CDDKEY as [Key]
FROM TABL1
WHERE ID = 186;

Note that KEY is a poor name for a column because it is a SQL keyword.