not in criteria fails

[ESN]

Looking for an updatable list of damaged items for which repairs haven't been initiated. The table Damage contains 49 records. The following SQL works to select damage records whose IDs appear in table Repairs (6 records):

SELECT Damage.ItemID, Damage.DamageDescription
FROM Damage
WHERE (((Damage.DamageID) In (SELECT Repairs.DamageID FROM Repairs)));

If I add "not" before "in" within the where clause, I get 0 records. Shouldn't every record be "in" or "not in" the subquery? What gives? Adding table Repairs to the FROM clause with a left join isn't an option, as that will return a non-updatable recordset.

 

[John W. Vinson]

Looking for an updatable list of damaged items for which repairs haven't been initiated. The table Damage contains 49 records. The following SQL works to select damage records whose IDs appear in table Repairs (6 records):

SELECT Damage.ItemID, Damage.DamageDescription
FROM Damage
WHERE (((Damage.DamageID) In (SELECT Repairs.DamageID FROM Repairs)));

If I add "not" before "in" within the where clause, I get 0 records. Shouldn't every record be "in" or "not in" the subquery? What gives? Adding table Repairs to the FROM clause with a left join isn't an option, as that will return a non-updatable recordset.

Try using a NOT EXISTS clause instead:

SELECT Damage.ItemID, Damage.Description
FROM Damage
WHERE NOT EXISTS(SELECT DamageID FROM Repairs WHERE Repairs.DamageID =
Damage.DamageID)

And note my .sig - this newsgroup was abandoned by Microsoft some years ago
now, and only a few of us ghosts continue to haunt it.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
and see also http://www.utteraccess.com

 

[ESN]

Thanks John - "Exists" works great, though I'm still baffled why "Not In" didn't. I feel like I've used it a thousand times before without problems.