Tom:
Apologies for not responding to your last post in the original thread, but
it must have slipped through the net.
I'm afraid this is something you can't do entirely in design view. At the
very least you have to enter the subquery as SQL. I think you'll find its
not so scary as might appear at first sight once you get your teeth into it.
Let me deconstruct the SQL statement I sent you line by line to give you an
idea of how it works:
SELECT *
….this line simply returns all columns from the table, by virtue of using
the asterisk in place of a column list. Column BTW is another name for
'field'. Strictly speaking tables have rows and columns rather than records
and fields, which are terms derived from old style file systems.
FROM Table1
….this line determines which table the rows are returned from.
WHERE EXISTS
….this is the start of the query's WHERE clause which restricts the results
to tose rows which match whatever criteria the clause contains. When you
enter criteria in the design grid in query design view you are building the
WHERE clause. The EXISTS predicate means that a row will only be returned if
at least one row is returned by the subquery which follows.
(SELECT *
….the opening parenthesis at the start of this line means that this is the
start of a subquery. In this case the subquery is contained in the outer
query's WHERE clause to restrict its results. As with the outer query SELECT
* returns all columns. These columns won't be returned in the results of the
outer query, however; the reason for using the asterisk in the subquery is
merely for efficiency as it allows Access maximum scope to optimise the query
behind the scenes.
FROM Table2
….this determines which table the subquery returns rows from
WHERE Table2.SomeColumn = Table1.SomeColumn
….this is the start of the subquery's WHERE clause. In this case it
includes three criteria, each of which compares one column of the subquery's
table with the corresponding column of the outer query to determine if the
rows in each are 'duplicates'. When a subquery interacts with the outer
query in this way its known as a 'correlated subquery'. In effect the
subquery runs independently for every row of the outer query to determine if
the row in question should be returned.
AND Table2.SomeOtherColumn = Table1.SomeOtherColumn
AND Table2.YetAnotherColumn = Table1.YetAnotherColumn);
….these two lines are the other two criteria for the subquery's WHERE
clause. The three are evaluated together by the use of the Boolean AND
operators. This means that all three have to be True for the whole WHERE
clause to be True and thus cause the row in the outer query to be returned.
An AND operation like this is the same as entering three criteria on separate
columns on the same line of the grid in query design view. I've used three
pairs of columns to indicate 'duplication' between the two tables here; it
could of course be less or more than two. The closing parenthesis at the end
indicates the end of the subquery, and the semi colon indicates the end of
the complete SQL statement.
I said above that you can't do the above in query design view. I lied! You
could in fact return all 'duplicated' rows simply by joining one table to the
other on all of the columns which determine the 'duplication. This would be
fine for seeing which rows are duplicated, but the query would not be
updatable (or more accurately would only be updatable if the join columns
were uniquely indexed). This means that the query can't be converted into a
'delete query'. With a query using a subquery as above, however, all that's
necessary to turn it into a 'delete query' is to change the very first line
form SELECT * to DELETE * (NB leave the first line of the subquery as SELECT
*).
Hopefully the above has given you an understanding of how the query works
and will enable you to amend the SQL to suit your own table and column names.
I don't think I can explain it any more simply – it’s as Einstein said, "You
should make something as simple as possible, but no more so." (In fact his
actual words were "The supreme goal of all theory is to make the irreducible
basic elements as simple and as few as possible without having to surrender
the adequate representation of a single datum of experience").
Ken Sheridan
Stafford, England
