Query to combine field?

[emax6]

I have a table, with row similar to the following:
ItemNb1, part1Nb, part1Color, part1VendorNb, part2Nb, part2Color, part2VendorNb

This could be for example
A1111, 10, Red, V23, 20, Red, V22
A1112, 20, Red, V22, 30, Blue, V22
A1113, 50, Pink, V23, 20, Red, V22
Etc..

How can I create a query that will produce an output similar to
A1111, 10, Red, V23
A1111, 20, Red, V22
A1112, 20, Red, V22
A1112, 30, Blue, V22
A1113, 50, Pink, V23
A1113, 20, Red, V22

Note that since I have no right to change the database, I can only use the table I have and eventually create another table to keep all my information before processing. Since this is to make a report sorted by color for example, I could also keep in memory the recordset created by the query.
Any clue would be appreciated.
Thanks in advance.

 

[PHV]

Use a normalization union query:
SELECT ItemNb, part1Nb As partNb, part1Color As partColor, part1VendorNb As partVendor FROM yourTable
UNION SELECT ItemNb, part2Nb, part2Color, part2VendorNb FROM yourTable WHERE part2Color Is Not Null

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

 

[Remou]

How about:
[tt]SELECT tblTable.ItemNb1, tblTable.part1Nb, tblTable.part1Color, tblTable.part1VendorNb
FROM tblTable
UNION
SELECT tblTable.ItemNb1, tblTable.part2Nb, tblTable.part2Color, tblTable.part2VendorNb
FROM tblTable;[/tt]

 

[emax6]

Hummm, so fast..
Thanks..