Isolating records in query

[Gordon]

The solution to this is probably very simple but I just can't see it.
Simplifying it, I have a series of records in a query that look like
this:

ID No

557 257
557 0
559 0
563 257
563 0
563 0
566 0
566 0
570 0
576 285
576 0

I am trying to get the query to return records where for any unique
instance of the ID field, there is no entry in the No field with a
value greater than 0. In the above extract, that would be IDs 559,
566 and 570.

I am using Access 2007. Thanks for any help.

Gordon

 

[Ken Sheridan]

Gordon:

Use the NOT EXISTS predicate against a correlated subquery, e.g.

SELECT *
FROM YourTable AS T1
WHERE NOT EXISTS
(SELECT *
FROM YourTable As T2
WHERE T2.ID = T1.ID
AND T2.No > 0);

The two instances of the table are differentiated by the aliases T1 and T2,
which enables the subquery to be correlated with the outer query. The outer
query will thus only return a row if the subquery returns no rows where the
ID value is the same and the No column contains a value greater than zero.
'YourTable' could of course be the result table of a query rather than a base
table.

Ken Sheridan
Stafford, England

 

[ghetto_banjo]

You could turn on the "Totals" on the query, group by ID number, Sum
on No, and make criteria on the SumofNO to be = 0.

 

[Gordon]

Gordon:

Use the NOT EXISTS predicate against a correlated subquery, e.g.

SELECT *
FROM YourTable AS T1
WHERE NOT EXISTS
    (SELECT *
      FROM YourTable As T2
      WHERE T2.ID = T1.ID
      AND T2.No > 0);

The two instances of the table are differentiated by the aliases T1 and T2,
which enables the subquery to be correlated with the outer query.  The outer
query will thus only return a row if the subquery returns no rows where the
ID value is the same and the No column contains a value greater than zero..  
'YourTable' could of course be the result table of a query rather than a base
table.

Ken Sheridan
Stafford, England










- Show quoted text -

Thanks for the quick responses. Both solutions work and produce the
same results but I have to go with ghetto_banjo's - it runs instantly
whereas for some reason Ken's takes well over a minute to run.

Thanks again to both of you.

Gordon