Compare 2 tables and delete from one of them


Tony Williams

I want to compare the records of two tables say Table1 and Table2 and then
produce a form that shows me the duplicates. I then want to delete any
duplicate record from Table1 but not from Table2.

I have a query that finds the duplicates and shows them in a form but when I
delete a record it is deleted from both tables not just Table1. The query is
based on the two tables but there is no join.

Can someone offer an alternative method? I'm a VBA novice so if the answer
includes VBA could you point me in the right direction with some base code.
Many thanks



Jack Cannon


Try the following.

Use an INNER JOIN query and display only the data from table1 something like:

SELECT tblTest1.Test1ID, tblTest1.TestData
FROM tblTest1 INNER JOIN tblTest2 ON tblTest1.TestData = tblTest2.TestData;

Do not try to delete a record by highlighting a
record on the form and hitting the delete key.

Instead put a button on the form and put something like
the following two commands in the ON_CLICK event.

CurrentDb.Execute "DELETE FROM tblTest1 WHERE [Test1ID]=" & Me![Test1ID]

Jack Cannon



Ken Sheridan


You could also show just the rows from Table 1 with matches in Table 2
in a form by basing the form on a query such as:

FROM [Table1]
FROM [Table 2]
WHERE [Table 2].[Field 1] = [Table 1].[Field 1]);

This assumes that the matches are determined by having the same values
in the one column in each, Filed 1 in my example. If the matches are
determined by the values in two or more columns then add the other
column(s) to the subquery's WHERE clause like so:

FROM [Table1]
FROM [Table 2]
WHERE [Table 2].[Field 1] = [Table 1].[Field 1]
AND [Table 2].[Field 2] = [Table 1].[Field 2]
AND [Table 2].[Field 3] = [Table 1].[Field 3]);

You'll then be able to delete individual records from table 1 in the
form in the usual way without affecting those in Table 2.

If you are not familiar with creating a query in SQL then what you do
is open the query designer in the usual way; don't add any tables;
from the View menu select SQL View; type or paste in the SQL as above,
and amend it to use your real table and column names.

Ken Sheridan
Stafford, England

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads