How to "Transpose" data in Access

[Alwyn]

Hi All



The title is not very descriptive of what I want to achieve, I think an example will be more useful.

I have data that looks as follows:

Product	Scenario 1	Scenario 2	Scenario 3
A	Value 1	Value 1	Value 1
B	Value 2	Value 2	Value 2
C	Value 3	Value 3	Value 3

I need the data in the following format:

Product	Scenario	Scenario Value
A	1	Value 1
A	2	Value 1
A	3	Value 1
B	1	Value 2
B	2	Value 2
B	3	Value 2
C	1	Value 3
C	2	Value 3
C	3	Value 3

If anyone knows how I can do this it would be greatly appreciated!

Regards
Alwyn

[CJ_London]

use a union query - you'll need one for each scenario. Your outcome is how the data should be stored in the first place

[kagoodwin13]

SELECT Product, "1" AS Scenario, [Scenario 1] FROM TableName
UNION ALL
SELECT Product, "2" AS Scenario, [Scenario 2] FROM TableName
UNION ALL
SELECT Product, "3" AS Scenario, [Scenario 3] FROM TableName
ORDER BY Product, Scenario

Product	Scenario	Scenario 1
A	1	Value 1
A	2	Value 1
A	3	Value 1
B	1	Value 2
B	2	Value 2
B	3	Value 2
C	1	Value 3
C	2	Value 3
C	3	Value 3