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
Tony

 

[Jack Cannon]

Tony,

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]
Me.Requery

Jack Cannon

 

[Ken Sheridan]

Tony:

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:

SELECT *
FROM [Table1]
WHERE EXISTS
(SELECT *
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:

SELECT *
FROM [Table1]
WHERE EXISTS
(SELECT *
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