CODEWITHSUNDEEP
sqlms-access
user882670
user882670

Reputation:

Reverse Crosstab Query in Access

In Access 2013, my table has the following structure:

Name   |City   |Q113   |Q213
Peter  |London |20     |30
Sandra |Paris  |40     |50

I want to "reverse a crosstab query", getting the following:

Name   |City   |Period   |Value
Peter  |London |Q113     |20
Peter  |London |Q213     |30
Sandra |Paris  |Q113     |40
Sandra |Paris  |Q213     |50

I tried a Union query, using SQL:

SELECT [Name], [City] ,'Q113' AS [Period], [Q113] AS [Value]
FROM [Database]
UNION ALL
ORDER BY [Name]
       , [City]
       , [Period]

However, it isn't working, I keep getting the error: "Invalid SQL statement expected; 'DELETE','INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'

Googling this error didn't help much, so I guess there is something wrong with the code above? Please help.

Upvotes: 1

Views: 4238

Answers (1)

Taryn
Taryn

Reputation: 247680

You are missing the query after your UNION ALL:

SELECT [Name], [City] ,'Q113' AS [Period], [Q113] AS [Value]
FROM [Database]
UNION ALL
SELECT [Name], [City] ,'Q213' AS [Period], [Q213] AS [Value]
FROM [Database]
ORDER BY [Name], [City], [Period]

You need to add the second part of your query and then place the ORDER BY last.

Upvotes: 2

Related Questions