Action Query: Update & Delete

[Neo-fite]

I have a query that Appends unmatched records from Table 1 to Table 2. Is it possible the Delete the records from Table 1 at the same time they are appended to Table 2?

 

[John Big Booty]

You'll need a second query that identifies which records have been transferred and then run that once the first query has been run.

 

[Neo-fite]

So I need to create the unmatched query, store those results on Table3, append the unmatched records from Table1 to Table2 and then delete the matches between Table3 and Table1?

 

[John Big Booty]

...or your could add a flag to your first table and set that as part of your transfer process and then simply run a delete query based on that flag.

 

[Neo-fite]

hmmm...I'll have to look into the "Flags".

 

[vbaInet]

I can gather from your first post that you already have a query that identifies duplicates. So the steps would be to use that query to:

1. Append to table 2
2. Delete from table 1

In that order.

 

[Neo-fite]

I can gather from your first post that you already have a query that identifies duplicates. So the steps would be to use that query to:

1. Append to table 2
2. Delete from table 1

In that order.

OK, I have 1 query that is an Append query, of which I assume I could easily copy/change to a Delete query....however, once I run the append query, the delete query will not have any records, as the records that were just appended will be in both tables.

 

[vbaInet]

Which was why I mentioned, you append first before deleting. These are two separate steps.

Convert the append query to a SELECT query and use that query in your APPEND and DELETE queries. Run the append query first, followed by the delete query.

 

[John Big Booty]

The OP is using an Unmatched query to do his appending, so once he has run that there should be no unmatched records in his first table making it very difficult to identify the records that need to be deleted. Hence my suggestion of adding a flag field (Yes/No) to his first table. He could then simply check the Yes/No field using his Unmatched query then run an append query based on the status of that Yes/No field then a similar Delete using the same criteria.

 

[vbaInet]

Ah, got you John Big Booty! I don't know why I was thinking that the records will still be there after the Append

 

[John Big Booty]

I'm not saying they won't be there, I'm just saying they won't be Unmatched any longer

 

[vbaInet]

I'm not saying they won't be there, I'm just saying they won't be Unmatched any longer

Obviously

 

[Neo-fite]

How would I go about adding a Y/N field to the records that the Unmatched query returns? I'm going to be using SQL code, as I will be doing this via a connection to Excel.

 

[John Big Booty]

You will first need to add the field to your table.

Then convert your Find Unmatched query to an Update query and simply update the Yes/No field to Yes/True or -1