Can't delete record in an unmatched query

[Bdaviskar]

Hello,

Access 2010

I created 3 find unmatched queries from the query wizard.
The first one works great. I can delete a record in the query and it deletes the record in the main table.

The second one allows me to delete a record and gives me the pop up box that says " you are about to delete 1 record". I click yes, but when I check the table or reopen the query the same record reappears. :banghead:

The third query does not even give me the option to delete.:banghead:

I can create a regular select query and delete the records form 2nd and 3rd but not from the queries made from the unmatched wizard.

I am using the same fields in all 3 queries.

I do not have enforce referential integrity or the other 2 options checked in the relationship properties. (not sure if this makes a difference.)

I even tried to redo the unmatched queries but I get the same result for all 3.

I want to be able to delete the records in the other 2 queries. Any help is appreciated Thank you.

 

[CJ_London]

help if you posted the sql to your delete queries

 

[Bdaviskar]

SELECT tblSessionAnc.IDSessionKey, tblSessionAnc.SessionID, tblSessionAnc.SessionDate, [tblAdministratorsAnc]![LastNameAdmin] & ", " & [tblAdministratorsAnc]![First NameAdmin] AS Administrator
FROM tblAdministratorsAnc INNER JOIN (tblSessionAnc LEFT JOIN tblSampleDetailsAnc ON tblSessionAnc.[SessionID] = tblSampleDetailsAnc.[SessionID]) ON tblAdministratorsAnc.ID = tblSessionAnc.Administrator
WHERE (((tblSampleDetailsAnc.SessionID) Is Null))
ORDER BY tblSessionAnc.SessionDate DESC;

The above sql is from the secnd example. It works when I run the unmatched query. It will show me all the records that are in the first table that are not related to the one in the second table, but will not allow me to delete the records.

Thank you, I did not even think about posting sql

 

[CJ_London]

Normally when you are deleting information you only have one table, whereas you are bringing two through - tblSessionAnc and tblAdministratorsAnc.

It is not clear which table to want to delete from - please advise

You may find this link helpful

http://stackoverflow.com/questions/...rom-multiple-tables-in-the-same-sql-statement

I usually use a query along the following lines

DELETE * FROM TableA WHERE not exists (SELECT * FROM TableB AS TMP WHERE PKID=TableA.FKID)

 

[Bdaviskar]

Thank you for taking the time to help.

I will check out and report back.