How to indicate which table's records get deleted in a 2-table query.

[MLH]

I had a table (tblReturnReceipts) with 47 records and I wanted 18
of its records deleted. So I thought I would be smart and make
another table (a temporary junk table) named BadRRIDs and use
it in the following Select query...

SELECT tblReturnReceipts.RtnRcptID, tblReturnReceipts.CorrespID,
tblReturnReceipts.DateSigned, tblReturnReceipts.OLA,
tblReturnReceipts.OLA_ID, tblReturnReceipts.RRTDstamp,
tblReturnReceipts.RRuser, tblReturnReceipts.Tracked FROM BadRRIDs
INNER JOIN tblReturnReceipts ON BadRRIDs.RRID =
tblReturnReceipts.RtnRcptID;

I wanted to run the query, which lists all the records in
tblReturnReceipts targeted for deletion, select all the rows
with the mouse and press the delete key. For the life of me,
I can only delete the 18 records in the trash table - BadRRIDs.

How do I tell the query to delete the records in the OTHER table?

[Tom van Stiphout]

On Fri, 17 Mar 2006 14:52:58 -0500, MLH <CR**@NorthState.net> wrote:

Delete * from MainTable
where ID in (select ID from BadTable)

-Tom.

I had a table (tblReturnReceipts) with 47 records and I wanted 18
of its records deleted. So I thought I would be smart and make
another table (a temporary junk table) named BadRRIDs and use
it in the following Select query...

SELECT tblReturnReceipts.RtnRcptID, tblReturnReceipts.CorrespID,
tblReturnReceipts.DateSigned, tblReturnReceipts.OLA,
tblReturnReceipts.OLA_ID, tblReturnReceipts.RRTDstamp,
tblReturnReceipts.RRuser, tblReturnReceipts.Tracked FROM BadRRIDs
INNER JOIN tblReturnReceipts ON BadRRIDs.RRID =
tblReturnReceipts.RtnRcptID;

I wanted to run the query, which lists all the records in
tblReturnReceipts targeted for deletion, select all the rows
with the mouse and press the delete key. For the life of me,
I can only delete the 18 records in the trash table - BadRRIDs.

How do I tell the query to delete the records in the OTHER table?

[MLH]

Thx, Tom.

DELETE tblReturnReceipts.RtnRcptID, *
FROM tblReturnReceipts
WHERE tblReturnReceipts.RtnRcptID In (select RRID from BadRRIDs);

worked perfectly.