Help needed with complex(?) Query.

[Morris.C]

I'm calling this a complex query because I've tried for days to do it, with
no success.

Here goes...
I have a table where the same string can appear in more that one row.
The same table also contains a 'Completed' yes/no field, and an 'Active'
yes/no field.
eg.
PROJECTID PROJECTNumber SubPROJECT Completed? Active
1 P111 2 YES YES
2 P567 1 YES NO
3 P111 3 NO YES
4 P123 1 YES YES
5 P246 4 NO YES

What I want the query to do is give me a list of PROJECTs that are Active,
but also Completed, BUT if the same PROJECTNumber has ANY record that shows
the Completed field as NO, that project does not get displayed.
Therefore, a SELECT query with a 'WHERE Completed=YES AND Active=YES' will
not work because PROJECTNumber 111 will be displayed because it matches
this criteria at PROJECTID-1. BUT because it does NOT match this criteria
at PROJECTID-3, I don't want to see PROJECTNumber 111 at all.
Sounds easy, but I'm stumped.
(Hopefully the example displays properly.

 

[Nikos Yannacopoulos]

Try this type of WHERE clause:

WHERE Completed=YES AND Active=YES AND PROJECTNumber Not In (SELECT
PROJECTNumber FROM TableName WHERE Completed = NO)

Change TableName in the subquery to the real name of the table.

HTH,
Nikos

 

[Morris.C]

Thank you very much for that.
The query took a long time to run, but once I included a GROUP BY
statement, it ran as quick as I would've liked.

Thanks again.

 

[Morris.C]

Thanks for that, worked perfectly...BUT...See my next posting.