Delete Query Issue

[James Monroe]

Greetings:

Getting frustrated I can't figure this out....

Two tables are in question. All I want to do is to delete records in
Table1 IF that record also appears in Table2.

Simple as that, but it's driving me nuts. (Perhaps further nuts would
be more accurate.)

Thanks for any help,

James

 

[John Vinson]

Greetings:

Getting frustrated I can't figure this out....

Two tables are in question. All I want to do is to delete records in
Table1 IF that record also appears in Table2.

You'll need to post a bit more information. How are the tables
related? What have you tried (please post the SQL)? What error, if
any, did you get?

Short but HIGHLY SPECULATIVE answer, since I don't know how your
tables are structured: create a Query joining from the Primary Key
field in Table1 to the corresponding Foreign Key field in Table2.
Change the query to a Delete query and select the * pseudo-field from
Table1 as the "delete from" field. The SQL would resemble

DELETE Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID;

 

[Trent]

This should work:

DELETE * FROM TABLE1
WHERE TABLE1.FKID = TABLE2.PKID

I would also check the database relationship between the
tables before you delete.

 

[John Vinson]

This should work:

DELETE * FROM TABLE1
WHERE TABLE1.FKID = TABLE2.PKID

I would also check the database relationship between the
tables before you delete.

ummm... Trent, that actually won't work since you're not including
Table2 in the FROM clause. You can't just pull a table in from left
field!

 

[James Monroe]

ummm... Trent, that actually won't work since you're not including
Table2 in the FROM clause. You can't just pull a table in from left
field!

Here's the SQL that works:

DELETE Table1.*, Table2.JoinedField
FROM Table1 INNER JOIN Table2 ON Table1.JoinedField =
Table2.JoinedField
WHERE (((Table1.JoinedField)=[Table2.JoinedField]));

I realize it's not the exact SQL that you or Trent suggested due to my
poor description of the issue, but it was close enough to get me on
the right track.

I really appreciate the help from both of you!

James