delete from one table if in another table

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

Guest

I'm a total newbie learning the hard way.

I have two tables of donors to our NGO.

One is a list of those that need contacting for a reason.
The second can be contacted by phone.

I need to delete all those in the phone contactable table from the total
contactable table. But for some reason, I'm just getting the phone
contactable ones from my delete query datasheet. I use the design view
mostly, but the sql is:

DELETE [Table03: SAMS for Mailhouse].*, [Table03: SAMS for
Mailhouse].donor_number
FROM [Table03: SAMS for Mailhouse], [Table08: Sams for Telemarketers]
WHERE (([Table03: SAMS for Mailhouse]![donor_number]=[Table08: Sams for
Telemarketers]![donor_number]));

I tried in design with one table, and with both tables, and I tried the
three types of joins, plus 'no join', but to no success.

cheers
 
Create a query that joins DonorID in both tables. Pull down DonorID from the
total contactable table. This query returns all the donor records in the
total contactable table that also in the phone contactable table. Convert
the query into a Delete query. Run the query to delete all those in the
phone contactable table from the total contactable table.
 
If you are trying to clean up a mess, fine, but if you are continuing
with both tables, I am sorry to have to say this, but your design is a
disaster. You should really get a good book like the Access 2002 (or
whatever version you are using) Developer's Handbook published by
Sybex.

"Learning the hard way" just means you will waste more and more time,
with less and less results. Not only that, it will be much more
difficult for the next volunteer that comes along to do anything with
the db.

You should not have 2 tables, but 1. In it there should be a field to
indicate if the contact can be reached by phone or not
(telemarketers), another for the reason which should be the identity
key from a lookup table *, and any additional fields you may need to
filter records on in the future..

*A lookup table is simply a table where the values rarely change, and
fields are looked up to insert in other tables, for example :tlkpCC:
Identity Type
1 Mastercard
2 Visa
3 Amex

For the specific problem you have now:
You cannot delete records from two tables the way you are attempting
to do. If you want to delete all records in tblDup that exist in
tblMaster, then do it this way:

DELETE tblDup.MyField
FROM tblDup
WHERE (tblDup.MyField) in (select myfield from tblMaster);

Peter
 
Back
Top