Tom:
The SQL I sent you is for a query which compares the two tables on the
basis of (for simplicity in the example) three columns, all of which
must have identical values for the rows from Table1 to be returned.
It works by means of a subquery with a series of expressions, one for
each column, tacked together with AND operators. So each expression
must be true for the subquery to return any rows. The expressions
compare the columns in the subquery's table (table2) with those in the
outer query's (Table1). By using the EXISTS predicate in the outer
query's WHERE clause it will only return rows where the subquery
returns at least one row for each of the outer query's rows. So the
outer query will return a row from Table1 if there is an exact match
in any row in Table2 in all of the columns you specify. By excluding
your Comments column from the expressions in the subquery therefore,
it will consequently still detect duplicates irrespective of the
comments you've added.
To test it just paste the SQL statement into a new query in SQL view,
change the table and column names to your own, adding further ANDed
expressions to the subquery for the other columns apart from your
Comments column. It should then return the 'duplicated' rows,
including any to which you've added comments.
You can easily amend the query to an 'append' query to copy the
duplicated rows to an 'archive' table, and to a 'delete' query to
remove duplicated the rows from the original. This can be done in
design view rather than SQL view if you switch to that, saving the
query under a different name for each type. Each month you'd execute
the 'append' query to archive the rows before the 'delete' query of
course.
Ken Sheridan
Stafford, England
On Dec 27, 6:08 pm, Tom wrote:
> Sheridan
> Thank you---but I do not understand your reply
> --
> Tom >> Stay informed about: Deleting Duplicates 
FAQ
Search
Profile
Private Messages
Log in
