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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A UNION query:

SELECT [Part#] As PartNbr, [Year1] As [Year], Yr1Demand As Demand
FROM table_name
UNION ALL
SELECT [Part#], [Year2] , Yr2Demand
FROM table_name
UNION ALL
SELECT [Part#], [Year3] , Yr3Demand
FROM table_name

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSnCW6IechKqOuFEgEQLtEQCglJP8SxuwAVm3tkG+sTgaprbSGFAAoJGD
+jHJiA1f9XvZlxB3WAMpEXfG
=TtkM
-----END PGP SIGNATURE-----

 

[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