Turn one record into two records in query!

[jcfrasco]

Hello,

I have a table that is used to track parts and their conditions. The table is structred like this: ID#, Nomenclature, PN, QtyServiceable, QtyRejected, QtyRepairable. What I need to do know is to make a record that has a quantity in more than one of the quantity fields (QtyServiceable, QtyRejected, QtyRepairable) show up as separate rows (This is going to be for a parts report).

Example:

(Before) 124 Part1 123456 2 5 0

(After) 124 Part1 123456 2 5 0
124 Part1 123456 2 5 0

It would be even better if I could created a temporary column and just have the quantities appear there so I can do a sum of the column later.

Thank you in advance for any assistance in this matter.

jcfrasco

 

[JohnDTampaBay]

Is this what you're looking for? Good luck

SELECT IDNum, Nomenclature, PN, QtyServiceable,
       QtyRejected, QtyRepairable
FROM SomeTable
WHERE QtyServiceable > 0
UNION ALL --------------------------
SELECT IDNum, Nomenclature, PN, QtyServiceable,
       QtyRejected, QtyRepairable
FROM SomeTable
WHERE QtyRejected > 0
UNION ALL --------------------------
SELECT IDNum, Nomenclature, PN, QtyServiceable,
       QtyRejected, QtyRepairable
FROM SomeTable
WHERE QtyRepairable > 0
ORDER BY IDNum, Nomenclature, PN

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)