Queries fail to find or delete record in child table

[RickSpr]

I have an Access 2000 mailing list database with the following tables:
Table Addresses
Primary key AddressID (autonumber)
Table Contacts
Primary key ContactID (autonumber)
Field MailingAddressID (Long, FK to Addresses)

The relationship from Addresses to Contacts is one-to-many with RI enforced and deletes cascading (but not updates).

There is a record in Addresses (AddressID=332) that has no related records in Contacts. I have verified this both via the Addresses subdatasheet, and by sorting Contacts by MailingAddressID and scanning the list.

The following query, however, fails to find this record:
SELECT Addresses.AddressID, Addresses.DeliveryAddress, Addresses.City, Addresses.StateProv, Addresses.PostalCode, Addresses.Country
FROM Addresses
WHERE (((Addresses.AddressID) Not In (SELECT MailingAddressID FROM Contacts)));

I added a fresh Addresses row with no Contacts row, and it doesn't find that either!

I tried using an outer join instead:
SELECT Addresses.AddressID, Addresses.DeliveryAddress, Addresses.City, Addresses.StateProv, Addresses.PostalCode, Addresses.Country, Contacts.ContactID
FROM Addresses LEFT JOIN Contacts ON Addresses.AddressID = Contacts.MailingAddressID
WHERE (((Contacts.ContactID) Is Null));

This found the rows. I selected the rows and pressed the Delete key. I got the confirmation dialog and OK'd it, and was left with an empty recordset displayed. But when I close the query and reopen it, the rows are back--they weren't deleted at all, and I didn't get any error message.

Anybody know what's going on here? Rick Sprague

 

[sko]

Rick,

You have to make it a delete query:

DELETE Addresses.AddressID, Addresses.DeliveryAddress, Addresses.City, Addresses.StateProv, Addresses.PostalCode, Addresses.Country
FROM Addresses
WHERE (((Addresses.AddressID) Not In (SELECT MailingAddressID FROM Contacts)));


BTW I pasted your sql. The first query returned the correct results. The second query returned the same 'you are about to delete' message. I think you can't delete records directly from the select query result set.

 

[RickSpr]

Thanks, sko. Yes, I realize it will work as a delete query, but this is a maintenance function that only I will use, to get rid of "orphaned" addresses whose contacts have been deleted. (I can't cascade the delete because the Contacts table is the child, not the parent table.)

I wanted to be able to do it from the select query so that I'd have a chance to review what I was deleting before I deleted it. Normally, you can delete from a select query's result set just fine. I still don't know what's different about this one. Rick Sprague

 

[scriverb]

When you have a left join between two tables you cannot use that select query to delete from. Your second select query is using a left join and looking for a null value in the right side of the join. Even though there is no record there, if you were to convert it to a Delete query you would get the error "Specify the table containing the records that you want to delete." which you cannot do. Either way the format of the first query converted to a delete query is the only way of deleting the records. I believe it will also let you delete them based upon the Select while using the Delete key. As SKO said the first Select query looks as if it should select the records in the ADDRESSES table with no select reference to the Contacts record. You should be able to delete manually with that SQL format.

I believe for these reasons ACCESS isn't letting you delete from that query.

Bob Scriver

 

[sko]

And the plot thickens...

This is pasted from my query. It's exactly like your first query (I pasted it) For some reason it's working now. I am viewing the results of the select query, selecting the records I want to delete and hitting the delete button. The record is being deleted. It's no longer in the Address table AND I didn't have to make the delete query. ??

Table Addresses
AddressId (autonumber PK)
etc

Table Contacts
ContactID (autonumber PK)
AddressID (Long, FK)


SELECT Addresses.AddressID, Addresses.DeliveryAddress, Addresses.City, Addresses.StateProv, Addresses.PostalCode, Addresses.Country
FROM Addresses
WHERE (((Addresses.AddressID) Not In (SELECT MailingAddressID FROM Contacts)));


You're saying this query didn't even find the orphan addresses?

 

[scriverb]

SKO, you are exactly right. I suspected that the first query would work both ways. Manual or as a delete query. The second query with the left join is referencing two tables and so when you press the delete button or convert it to a delete query and run it, ACCESS will not delete the record due to the confusion as to which table you want to delete from.

Bob Scriver

 

[sko]

Bob,
I understand why the second query doesn't work but Rick was saying the first one wouldn't work either. In fact, the first one wouldn't work for me the first time I tried it. I had to make it a delete query. Don't know what's different now. Didn't change the table relationships, didn't change the test data. ??

 

[RickSpr]

Yes, the query with the NOT IN subquery doesn't even find the orphaned address. And the query with the outer join appears to let me delete the record, but it isn't actually deleted. So even if Bob is right about not being able to delete from an outer join, there's still a bug, apparently, in that Access is failing to report an error.

The outer join problem I can let go of as a bug, but the NOT IN failure is startling and distressing. It's a valid predicate, isnt it? If so, it should work, consistently, or I'm misunderstanding something and need to correct that. If not, it should be giving me a syntax error.

I'm gonna work around the present problem with an outer join to look at the data I'm about to delete, and a separate delete query to commit it. I still wish I knew what's up, though. Thanks for your insights. Rick Sprague