Convert From Column to Row (Record)

[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!!!

 

[KARL DEWEY]

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;

 

[banker123]

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

 

[John Spencer]

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
..

 

[banker123]

Awesome, thank you!!

 

[banker123]

Thanks worked great!

 

[banker123]

How do I make this a make table action query?

 

[John Spencer]

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
..