Compare 2 files and delete

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I need to compare two files then delete records in one
file that do not have a matching records in the other. I
have tried a couple of ideas but I have not been able to
figure out how to do it. I can compare the two files in
append wizard but not in the delete wizard. It seems I
need to combine the two functions? Does anyone have any
examples of how I can do this?

Thanks Dennis
 
I need to compare two files then delete records in one
file that do not have a matching records in the other. I
have tried a couple of ideas but I have not been able to
figure out how to do it. I can compare the two files in
append wizard but not in the delete wizard. It seems I
need to combine the two functions? Does anyone have any
examples of how I can do this?

Thanks Dennis
Click to expand...

First off - they're not "files", they're Tables. Jargon can be
confusing!

You'll need to build the query yourself; the wizards are pretty
limited. I assume that both tables have the same Primary Key field,
and that matching primary keys is sufficient to identify "matching
records".

First, back up your database - any time you do a mass delete there's a
risk you'll delete too much!

If so, create a Query joining the two tables by that field. Select the
Join line and choose option 2 or 3 - "Show all records in <the table
you want to delete from> and matching records in <the other table>".

Select only the joining field from the second table. Put a criterion
on it of IS NULL.

Open the query to make sure that these are in fact the records you
want to delete. If so, change the query to a DELETE query, and uncheck
the Show box under the field with the "is null" criterion.

The SQL will be something like

DELETE * FROM tableA LEFT JOIN tableB
ON tableA.ID = tableB.ID
WHERE TableB.ID IS NULL;
 
John,

Once I change the below query (Which seems to work
Beautifully) to a delete query, and I run the delete query
Access asks to "specify the table containing the records
you want to delete". 1)How can I do that? and 2)I can
uncheck the show box in a select query then convert it to
a delete query(Which does not offer the option)but the
field still shows up in the query result, is that a
problem?

Thanks,

Dennis
 
If you are working with the query builder, once the query
is changed to a delete query there will be a row
titled "Delete". The options on this row are "From"
and "Where". Select "From" below a field from the table
that you want to delete the records from, and
select "Where" beneath any fields from other tables that
you want to use to specify criteria to restrict the
deletions from the "From" table.

Hope this helps.

-Ted Allen
 
What might be the problem if the query builder does not
let you change any of the fields to from? Each field just
changes back to where?

Thanks,

Dennis
 
Hi Dennis,

I may have given you a little bit of bad info. You may
have to insert the * to be able to specify "from". I
hadn't noticed this before, but it appears that it may be
the case. Maybe Access does this to reinforce the fact
that the entire record will be deleted, not just field
values. Anyway, try double-clicking the * in the field
list for the table that you want to delete records from
(or drag the * to the design grid) and then try
specifying from beneath the *. Let me know if that still
doesn't work.

-Ted Allen
 
Ted,

Yes that works, and when I test the query the records I
want to delete are there. However, when I try to run the
delete part of the query, I get an error message "could
not delete from specified tables". So close! What could
the problem be?
Thanks,

Dennis
 
Hi Dennis,

Sorry so long for the reply, I was off on Fri. Hopefully
you will still check back.

The problem is most likely that you need to open the
query properties and change the unique records setting
to "Yes". Beginning in Access 2000 this defaults
to "No", but must be "Yes" for a multi-table delete query.

Post back if that's not it.

-Ted Allen
 
Back
Top