Transpose one row of a table access

Question

I have a table where each row has a Scale Name and then 6 points representing the 6 Reponses to a survey

Scale	Point1	Point2	Point3	Point4	Point5	Point6
Ability	Extremely Unable	Moderately Unable	Somewhat Unable	Somewhat Able	Moderately Able	Extremely Able

I need to figure out how to write a query that will return:

ID	Response
1	Extremely Unable
2	Moderately Unable
3	Somewhat Unable
4	Somewhat Able
5	Moderately Able
6	Extremely Able

I've tried to do this with the crosstab query wizard and SQL but I'm not making any headway.

UPDATE - This is what I ended up with:

SELECT 1 AS ID, [Ref-SCALELIST].Point1 AS Response
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 2 , [Ref-SCALELIST].Point2
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 3, [Ref-SCALELIST].Point3
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 4, [Ref-SCALELIST].Point4
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 5, [Ref-SCALELIST].Point5
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 6, [Ref-SCALELIST].Point6
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
;

"Ability" is going to be replaced with a variable so I can dynamically update a combo box based on which scale is selected. Convoluted, but at least I avoid creating a table or query for each scale in my list (>30)

Answer 1

You could try something like this:

WITH col as(
  select '1' AS c
  UNION ALL
  select '2' AS c
  UNION ALL
  select '3' AS c
  UNION ALL
  select '4' AS c
  UNION ALL
  select '5' AS c
  UNION ALL
  select '6' AS c
  )
  select
  c 'ID',
  CASE c
    when '1' then "Extremely Unable"
    when '2' then "Moderately Unable"
    when '3' then "Somewhat Unable"
    when '4' then "Somewhat Able"
    when '5' then "Moderately Able"
    when '6' then "Extremely Able"
    else null
  END as response
FROM table1 corss join col

db fiddle link