How do I break out a record if it meets a certain criteria?

[serino64]

I have table called "ClientTBL" with four fields:
JobName, Division, CRPrice, LSPrice and ServicePrice.

JobName Division CRPrice LSPrice ServicePrice
Record1 Smith Maintenance $0.00 $0.00 $500.00
Record2 Davis Multiple $15,000.00 1,200.00 $500.00

What I need to accomplish here is, if there is a value under “CRPrice” and/or “LSPrice” have those records broken out into their own records.

JobName Division CRPrice LSPrice ServicePrice
Record1 Smith Maintenance $0.00 $0.00 $500.00
Record2 Davis Multiple $15,000.00 1,200.00 $500.00
[blue]Record3 Davis Multiple $15,000.00 $500.00
Record4 Davis Multiple 1,200.00 $500.00
[/blue]

I appreciate your assistance.

 

[dhookom]

I would try a union query like:

SELECT Jobname, Division, CRPrice, LSPrice, ServicePrice
FROM ClientTable
UNION ALL
SELECT Jobname, Division, CRPrice, Null, ServicePrice
FROM ClientTable
WHERE CRPrice > 0
UNION ALL
SELECT Jobname, Division, Null, LSPrice, ServicePrice
FROM ClientTable
WHERE LSPrice > 0;

Duane
Hook'D on Access
MS Access MVP

 

[serino64]

Thank You! Dhookom worked out great.