This is probably easy..but I can't figure it out!

[ACTHelp]

Hi Ya'll,

I have a table I'm importing that looks like this
Customer Name Smith Brothers
Product A
Price of A
Quantity of A
Product B
Price of B
Quantity of B
Product C
Price of C
Quantity of C

and is a single row per customer.

I'd like to convert this table into a real table like

Customer Name, Product Name, Product Price, Product Quantity
Smith Brothers A 2.44 3
Smith Brthers B 2.44 1
Smith Brothers C 5.34 6


Is there a simple way?

 

[dhookom]

You could use a union query:

SELECT [Customer Name], [Product A] as Product, 
[Price of A] as Price, [Quantity of A] as Quantity
FROM tblThatLooksLikeThis
UNION ALL
SELECT [Customer Name], [Product B], [Price of B], [Quantity of B]
FROM tblThatLooksLikeThis
WHERE [Product B] is not Null
UNION ALL
SELECT [Customer Name], [Product C], [Price of C], [Quantity of C]
FROM tblThatLooksLikeThis
WHERE [Product C] is not Null;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Use a normalization union query:
SELECT [Customer Name], [Product A] AS [Product Name], [Price of A] AS [Product Price],[Quantity of A] AS [Product Quantity] FROM yourTable
UNION SELECT [Customer Name], [Product B], [Price of B],[Quantity of B] FROM yourTable
UNION SELECT [Customer Name], [Product C], [Price of C],[Quantity of C] FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexMidd]

One table for customers containing all the customer details and a unique identifier as the primary key, and another table for the product details with a unique product id as the primary key and a field to contain the customer id to act as a foreign key. You can then pull out products for particular customers by using a query.

Have fun! )

Alex Middleton

 

[ACTHelp]

Thanks to all for your responses. I used the union approach and it worked well. You've saved me a lot of time!

Thanks, Cindy