Use Find Unmatched records query to delete records from another table

[newbie188]

Hi there.

re: ACCESS 97

I have a dilemma..I have two tables (Inbox) and (Master). I'm trying to implement a solution that will add records from the Inbox into the Master and subsequently remove the same records from the Inbox.

Currently, I have a macro that has two embedded queries: Append query and a Delete query. I have created the Append query based on a Select query-Find unmatched records so that only those records are added to the Master table.
The Append macro is running properly. However, the Delete macro is not.

Initially, I also based the Delete query on the Find Unmatched query but when I run it, I get the error msg "Could delete from specified tables" because the Table in the grid design reads the Unmatched query name.

So, my question is that is there a way for me to do this using a delete query and embed it in a macro or can you suggest another way.

Pls note that I was also trying to setup a Delete query without basing it on the Unmatched query. However, how do I set up the criteria so that it checks to ensure that the CircuitID in the Inbox which has already been added to the Master, can be removed. The CircuitID field is present in both tables as indexed and NO duplicates. We have to link them using an autonumber..one starting at 0 and the other at 1000. Therefore, the only time they are linked is when a query is run.


Hope this makes sense..YOUR HELP IS GREATLY APPRECIATED.

Regards.
Maggie

 

[THoey]

After you get the records into Master, wouldn't the following work (Please test on a back up copy first)?

DELETE FROM Inbox
WHERE Inbox.CircuitID IN
(
SELECT Master.CircuitID
FROM Master
)

You say you don't have any duplicates allowed, so once you append from Inbox to Master, anything that is in Master should be able to be deleted...
Terry M. Hoey

 

[newbie188]

Thank you Terry for your reply. I'll try it out.