not in criteria fails


E

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.
 
Ad

Advertisements

J

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
 
Ad

Advertisements

E

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top