Convert From Column to Row (Record)

  • Thread starter Thread starter banker123
  • Start date Start date
B

banker123

I have a table:

ID Price Quantitiy
1 1.00 2

I would like to create a make table query to convert the original
table to something like this:

ID Measure Data
1 Price 1.00
1 Quantity 2

Please help, hopefully ther is a simple solution!!!
 
Look up Excel Paste Special - Transpose for a solution.

Or try this ---
SELECT "Price" AS Measure, [URTable].[Price] AS Data
FROM URTable
UNION ALL SELECT "Quantity" AS Measure, [URTable].[Quantity] AS Data
FROM URTable;
 
The SQL did not return the ID field, but did seem like step in the
right direction, please advise??

results of SQL:
Measure Data
Price 1.00
Quantity 2

desired results
ID Measure Data
1 Price 1.00
1 Quantity 2
 
Add the ID field into the UNION query
SELECT ID, "Price" AS Measure, [URTable].[Price] AS Data
FROM URTable
UNION ALL
SELECT ID, "Quantity" AS Measure, [URTable].[Quantity] AS Data
FROM URTable;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Save the query.

Then use the saved query as the source of a make table query.

--A NEWquery in Design view
--Select the saved query as the source
--Select the fields you want
--SELECT Query: Make Table Query from the menu



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top