Transforming Data

[nogreatnamesleft]

I have data in a row like this:
Part#; Year1; Year2; Year3; Yr1Demand; Yr2Demand; Yr3Demand

I want the data in columns like this:
Part#; Year 1; Yr1Demand
Part#; Year 2; Yr2Demand
Part#; Year 3; Yr3Demand

I've found a complex way to do this but wonder if there is a simpler, more
efficient way to transform the data.

 

[Duane Hookom]

The standard method would be to create a union query like:

SELECT [Part#], [Year1] as Yr, [Yr1Demand] as Demand
FROM tblNoNameGiven
UNION ALL
SELECT [Part#], [Year2], [Yr2Demand]
FROM tblNoNameGiven
UNION ALL
SELECT [Part#], [Year3], [Yr3Demand]
FROM tblNoNameGiven;

 

[John Spencer]

The simplest way is to use a union query. Union queries can only be
constructed in SQL view.

SELECT [Part#], Year1 as theYear, Yr1Demand as Demand
FROM [SomeTable]
UNION ALL
SELECT [Part#], Year2 as theYear, Yr2Demand as Demand
FROM [SomeTable]
UNION ALL
SELECT [Part#], Year3 as theYear, Yr3Demand as Demand
FROM [SomeTable]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County