Unpivoting data in Access

[sal]

I inherited a Database with a Table that stores information in fields:

Date SS SM SL

which I summarize in a Query with totals as

Year SumOfSS SumOfSM SumOfSL

What I would like to do is essentially unPivot this data:

Year Class Total
2012 SumOfSS 20
2013 SumOfSM 55
2014 SumOfSL 10

I tried using a Union ALL Query:

SELECT Year, SumOfSS AS Class
FROM [Snow Smolt Annual]
UNION ALL
SELECT Year, SumOfSM AS Class
FROM [Snow Smolt Annual]
UNION ALL
SELECT Year, SumOfSL AS Class
FROM [Snow Smolt Annual]



But the The Microsoft Access database engine cannot find the input table or query. I have carefully checked the spelling, and renamed the query without spaces, but get the same error. Perhaps there is a was to summarize from the original Table instead of the Query. Any suggestions would be appreciated. Thanks.

 

[sal]

Solved:


Select [Snow Smolt Annual].Year, [Snow Smolt Annual].SumOfSS As [Total], "SS" as [Class], 1 as [Order]
From [Snow Smolt Annual];
Union All
Select [Snow Smolt Annual].Year, [Snow Smolt Annual].SumOfSM As [Total], "SM" as [Class], 2 as [Order]
From [Snow Smolt Annual];
Union All
Select [Snow Smolt Annual].Year, [Snow Smolt Annual].SumOfSL As [Total], "SL" as [Class], 3 as [Order]
From [Snow Smolt Annual];
UNION ALL Select [Snow Smolt Annual].Year, [Snow Smolt Annual].SumOfSXL As [Total], "SXL" as [Class], 4 as [Order]
From [Snow Smolt Annual];
Order by [Snow Smolt Annual x size].[Year], [Snow Smolt Annual x size].[Order]