Delete all Records but...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -- I have a table that list a set of records that I need data for
(tblScan) and several tables that contain many more records than I need. How
can I create a Delete Query that will use the vaules I have in tblScan and
compare it to the other tables to delete all the records that do not match
what is in tblScan??

Thanks!!!
 
The first thing to do is make a complete backup of the database in case you
delete the wrong records.

Next you need to exactly define what you mean by a match. Is there one field
where you can match records in the other tables such as an employee number or
SSAN?

If so, link the two tables in a query. Bring down all the fields from the
table with the unneeded records. Run the query and see if it produces the
data that you want to keep. If so make the query a Make Table query and put
this data in another table. When done, delete the original table then name
the new table exactly the same at the deleted one. If you have relationships
set up with referiental integrity enabled, Access might not let you delete
the table.

Another method is a delete query with a subquery. It would look something
like:

DELETE *
FROM tblScan
WHERE [PrimaryKeyField] Not In (SELECT MatchingField
FROM JohnHobby);
 
First, afaik, you can't delete records from more than one table at a time
via Jet queries.

Therefore, make "Find Unmatched" queries for each of your related tables vs
tblScan, then change those SELECT queries to DELETE queries. (Backup db
first).

HTH,
 
First, afaik, you can't delete records from more than one table at a time
via Jet queries.
Click to expand...

well... you can, if you have Cascade Deletes set on the table
relationship. deleting from the "one" side of a relationship will
delete all related records on the "many" side - useful, but dangerous
(just like, say, dynamite).

John W. Vinson [MVP]
 
Of course, if Cascading Deletes are on, you will delete dependent records
whether the table is specified in the SQL or not. The OP didn't indicate the
tables were related in this manner and I assumed not (or he wouldn't have
the orphaned records he wants to delete)

I was focusing on SQL's DELETE statement. The Help entry indicates you can
specify more than one table to Delete from, but I've never been able to get
that to actually happen (or seen an example of the proper syntax). Not sure
if that's just me or a misstatement in the Help entry. (cause, like, that
*never* happens!) :-)
 
Back
Top