Unmatched query...sort of

  • Thread starter Thread starter Darrell Childress
  • Start date Start date
D

Darrell Childress

I have a table with 4 fields:
SONumber, InvDate, InvAmount, Complete (Yes/No field)

The table will have multiple records for many SONumber's. I need to
write a query that will find all SONumber's that do NOT have an entry
where the Complete field = Yes.
In other words, we do a lot of partial shipments and I need to find
orders that have shipments, but we haven't made the final shipment yet.
Any suggestions? Thanks,
Darrell
 
Your query should be relatively simple, just add the criteria "No" to the
Complete field in your Select query.
 
First, build a query that returns all records where Complete is true(yes)
Then use that in an unmatched query along with you original table.

SQL would look something like

SELECT Distinct SONumber
FROM YourTable LEFT JOIN
(SELECT SONumber
FROM YourTable
WHERE Complete = TRUE) as Finished
ON YourTable.SONumber = Finished.SONumber
WHERE Finished.SONumber is Null

Alternate (may be too slow with large data sets)
SELECT Distinct SONumber
FROM YourTable
WHERE SONumber Not IN
(SELECT Temp.SONumber
FROM YourTable as Temp
WHERE Temp.Complete = TRUE)
 
I actually had thought of the same solution and was going to post
something similar. Thanks for the response!
Darrell
 
Nicholas said:
Your query should be relatively simple, just add the criteria "No" to the
Complete field in your Select query.
Click to expand...

Unfortunately that doesn't work. It will select an SONumber with a "No"
anywhere, even if the same SONumber has a "Yes" somewhere.
 
Back
Top