Delete matching records from two tables

[Guest]

I'm pulling records for providers based on terminations and reinstatements
for the past week. It's possible the provider was termed and reinstated
during the week, in which case I don't want them. My current thought process
is to create separate tables for terms and reinstatements, then have a delete
query remove matching records from both tables. However, this is giving me
the "Could not delete from specified tables" error. All three joins are
necessary to ensure matching records. Is it possible to delete records from
both tables this way? If not, can someone suggest a way to ensure both
records are removed.

DELETE [0 Weekly Provider Terms].*, [0 Weekly Provider Reinstates].*
FROM [0 Weekly Provider Reinstates] INNER JOIN [0 Weekly Provider Terms] ON
([0 Weekly Provider Reinstates].doc_suffix = [0 Weekly Provider
Terms].doc_suffix) AND ([0 Weekly Provider Reinstates].tax_id = [0 Weekly
Provider Terms].tax_id) AND ([0 Weekly Provider Reinstates].doc_id = [0
Weekly Provider Terms].doc_id);

 

[George Nicholson]

The Jet SQL Help entry for DELETE indicates that it is possible to delete
from 2 tables at once, but I've never found a way to actually make that
happen. Maybe someone will post an answer in that vein.

My workaround would be this: temporarily store the necessary information
about what to delete and then run separate delete queries on each table.

1) Create a temp table with doc_suffix, tax_id, doc_id fields (we'll come
back to this)

2) create an append query that adds the 3 fields from currently matching
records to the temp table
(change this to a MakeTable query & run it once to take care of Step 1. Then
change it back to an Append query & save it.)

3) create a saved query that deletes any 3-field matches between your temp
table and WPTerms
4) repeat #3 for WPReinstates

5) create a saved query that deletes all records from your temp table.

Repeat steps 2-5 each time you want to delete. Once set up you could easily
attach the entire sequence of queries to a form button. If using code to run
the queries, you could also replace any or all of the saved queries with
SQL.

The suggest clearing & appending to a Temp table rather than using a
Maketable each time is to avoid having saved queries refer to a table that
won't exist at certain times.
If those queries were opened in design view while the table didn't exist,
Access would try to "fix" them. While it's possible to work around that, I
prefer to simply avoid it. (Also, 3rd party tools like Access Analyzer would
report it as design error(s). hmpf!) If you go a strictly-code route (i.e.,
no saved queries), you can safely use MakeTable, deleting the table when
done.

HTH,