Deleting Multiple records in a query

[Perfac]

Long ago I learnt how to set up a query that returns records found in table A but not in table B. I use the way through "Append Query" to add the records I wanted to add that was found in A but not yet in B. My challenge is that some records in A may get redundant and have no use. I want to learn how to delete them through "Delete query" or VBA. I just spent a couple of hours testing suggestions, and advice I got from the net. Like setting "Unique records" on the Property sheet to yes in a query. It did not work, maybe my lack of enough brain sells is holding me back again.

[orange]

Delete queries can be quite unforgiving, so proceed with caution. Backup your database, or copy your current table and rename that copy.

Do some testing. Create a table, add some records, then
-use a Select (readOnly) query to identify records you would like to delete.
-if the selected records match you expected records to be deleted, then
-modify the query from Select to Delete
-run the query
-review your table to assure the appropriate records have been deleted.

You can post your activities as outlined above to show readers what stage you are at, and ask more questions if necessary.

Good luck.

[Perfac]

Thank you. I did what you suggest before you replied. Note that I am comparing a Query with a Table (They must return the same records) in a new Select query that returns the records I want to Delete from the Table. The select query returns one field which is in the Table. I then make it a delete query, setting "Unique Records" to yes on the property sheet. When I run it, the error is. "Could not Delete from specified tables".

I also appended the query to a table. Table A and Table B is compared. I added 5 records to Table A and in the Select query the 5 display as I want them to. I then change the Select query to a delete query. When I run it the error is "Could not Delete from specified tables".

Table A holds extra information, I can not clear the Table A, and append the query, else I loose the extra information.

[orange]

With a Delete query, you Delete RECORDS, not fields. The entire record is deleted.

I recommend that you post your database and provide directions detailing the steps to get to the query and table involved. Your "comparing a Query with a Table" is a little confusing.

Always advisable to post code you have tried along with any messages you receive.

W3schools has some great tutorials re SQL/queries. Here's link to DELETE

[Perfac]

Thanks. I will have a look. I will import just a couple of queries and tables into a clean database and then post it as soon as time allows me.

[Perfac]

Hope I done it correct. I looked at the example you suggest. In my case I need to establish the records in Table B that are not in Table A and only delete them. In the posted example there are 12 records in B not in A as you can see in the query. Maybe I am missing something small.

[orange]

SELECT query to identify the FormNameC values in TableB that are not in TableA

SELECT TableB.FormNameC
FROM TableA RIGHT JOIN TableB ON TableA.FormName = TableB.FormNameC
WHERE (((TableA.FormName) Is Null));

Delete query to "tell Access" Delete the records in tableB that are identified in the SELECT query

DELETE TableB.FormNameC
FROM TableB
where FormNameC IN
(SELECT TableB.FormNameC
FROM TableA RIGHT JOIN TableB ON TableA.FormName = TableB.FormNameC
WHERE (((TableA.FormName) Is Null)));



Also Note: My description of designing a SELECT query, checking to make sure result is as expected, then convert to DELETE query --works when only 1 table is involved. Sorry if I was confusing.

I tried to upload a GIF file, but the system kept changing it to jpg. So, I have put the gif into the attached zip file. Open the zip, and watch the gif animation. Good luck.!

[Perfac]

Thank you sir. That works brilliant.

[orange]

You are very welcome.
Hope you enjoyed the gif animation.