Transforming Data

  • Thread starter Thread starter nogreatnamesleft
  • Start date Start date
N

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.
 
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;
 
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
 
Back
Top