Re: Deleting Unmatched records

Usually, one simple change will solve the problem. Add DistinctRow to the
delete query when you have a join in the delete query. You can add it if
you only have one table in the From clause, but it is not necessary.

DELETE DistinctRow tbl2.*
FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1
WHERE (((tbl1.Key1) Is Null));


"Kelvin Leong" <KelvinLeong@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:305FE7C6-EAE5-42B8-A59C-1CED8FB52829@xxxxxxxxxxxxxxxx
Hi there,

I have 2 tables, namely tbl1 and tbl2, and they are linked by a common
Key1.
Due so some reason the records in tbl1 was deleted but not linked records
in
tbl2.

I have created an unmatching query, using the wizard, on tbl2 with
comparison to tbl1 to filter out which records in tbl1 no longer exist,
while
linked records in tbl2 still do, via this Key1.

SQL View of query:
SELECT tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

I then changed it to a DELETE query and the query became:

SQL View of query:
DELETE tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

However, when I run this query, the system gave me this error message:
"Specify the table containing the records you want to delete."

The following is the Design View of the delete query:
Field | tbl2.* | tbl1.Key1 |
Table | tbl2 | tbl1 |
Delete | Where | Where |
Criteria | | Is Null |
or | | |

I wanted to change the "Where" for the tbl2 but it does not allow me to do
so. How can I delete all of the unmatched records in tbl2 as described
above?

Best regards,

Kelvin



.

Relevant Pages

  • RE: Join two tables with 3 fields; 1 field doesnt match exactly
    ... Another string evaluation would be the Instrfunction, ... trying to join these fields in order to run a query that brings together data ... In Tbl2, ... There could be several engines listed in the options field in Tbl1 but only ...
    (microsoft.public.access.modulesdaovba)
  • RE: Join two tables with 3 fields; 1 field doesnt match exactly
    ... trying to join these fields in order to run a query that brings together data ... In Tbl2, ... There could be several engines listed in the options field in Tbl1 but only ... The Mid function above would select only the first two characters of Tbl1 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Unmatched records
    ... I have created an unmatching query, using the wizard, on tbl2 with ... comparison to tbl1 to filter out which records in tbl1 no longer ... SQL View of query: ... PRIMARY KEY (Key1) ...
    (microsoft.public.access.queries)
  • Re: Combine Tables
    ... Tbl1 is single field: ... Tbl2 has 2 fields: ... that determines where, the dash is. ... THEN use the query and tbl2 to create another query that joins ...
    (comp.databases.ms-access)
  • Re: sequence for tickets
    ... Create a table of number (tblNums) with a single, ... FROM tbl1, tblNums ... You can convert this to a make table or append query to get your records ... my output table is tbl2 ...
    (microsoft.public.access.modulesdaovba)