HELP! How to delete recods in one table that match those in another table?

[William Case]

I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.

Here's the SQL code for the select query that DOES work:

SELECT tblEmailLetters_TEMP.*
FROM AAA_Temp_EmailImport INNER JOIN tblEmailLetters_TEMP ON
(AAA_Temp_EmailImport.MsgGroup = tblEmailLetters_TEMP.MsgGroup) AND
(AAA_Temp_EmailImport.MsgDesc = tblEmailLetters_TEMP.MsgDesc);

Here's what happens when I convert the above to a delete query:

DELETE tblEmailLetters_TEMP.*
FROM AAA_Temp_EmailImport INNER JOIN tblEmailLetters_TEMP ON
(AAA_Temp_EmailImport.MsgGroup = tblEmailLetters_TEMP.MsgGroup) AND
(AAA_Temp_EmailImport.MsgDesc = tblEmailLetters_TEMP.MsgDesc);

The delete version fails with a message that says "Could not delete
from specified tables."

I've done this successfully with other data base programs. Why does
this not work?

Thanks folks.

[bebelino]

On Fri, 10 Oct 2003 00:17:44 -0500, William Case
<re**********@nospam.zap> wrote:

I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.

goto the relationships window and doubleclick on the relation between
those two tables. Now you see the two fields in relation. Down the
form you can choose three possibilities. I choose them all three.
Especially the last one is important for you I think: delete ...
Hopefully it works now.
--
bebelino

[LaurenW]

On Fri, 10 Oct 2003 07:34:40 GMT, bebelino <a.*@c.d> wrote:

On Fri, 10 Oct 2003 00:17:44 -0500, William Case
<re**********@nospam.zap> wrote:

I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.

goto the relationships window and doubleclick on the relation between
those two tables. Now you see the two fields in relation. Down the
form you can choose three possibilities. I choose them all three.
Especially the last one is important for you I think: delete ...
Hopefully it works now.

Thanks for this response Bebelino! Are you saying that the query
fails BECAUSE the tables are not linked by a relationship? If so, I
can simply create a relationship on the fly even though BOTH of these
tables are temporary. Correct?

[bebelino]

On Fri, 10 Oct 2003 11:59:39 -0500, LaurenW <re**********@nospam.zap>
wrote:

Thanks for this response Bebelino! Are you saying that the query
fails BECAUSE the tables are not linked by a relationship? If so, I
can simply create a relationship on the fly even though BOTH of these
tables are temporary. Correct?

I suggest you try it out, because I haven't don't this before. I would
access the tables via code and then delete the approriate records.
good luck,
--
bebelino