Column data to row data

[arnhold]

Hi

I have this data set and want to make it more effective by making a new column that specifies if the row is "budget" or "actual". And thereby transposing the three last columns to be rows. So instead of having 9 columns, I want 7 (9-3+1), but eight rows.

I will be happy for an answer on this one

Enclosed file ClaimSize.accdb

[June7]

A UNION query will probably achieve this. There is no designer or wizard for UNION, must type (or copy/paste) into SQL View.

SELECT ID, Country, Product, [Small - actual] AS Small, [Mid-size - actual] AS MidSize, [Large - actual] AS Large, "actual" AS Category FROM ClaimSize
UNION SELECT ID, Country, Product, [Small - budget], [Mid-size - budget], [Large - budget], "budget" FROM ClaimSize;

[arnhold]

Thanks a lot! That was just the solution I was looking for!