Unmatched query...sort of

[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

 

[Guest]

Your query should be relatively simple, just add the criteria "No" to the
Complete field in your Select query.

 

[John Spencer]

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)

 

[Darrell Childress]

I actually had thought of the same solution and was going to post
something similar. Thanks for the response!
Darrell

 

[Smartin]

Your query should be relatively simple, just add the criteria "No" to the
Complete field in your Select query.

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