delete query based on presence in another query

  • Thread starter Thread starter james_keegan via AccessMonster.com
  • Start date Start date
J

james_keegan via AccessMonster.com

I'm trying to write a routine to 'clean out' various records from their
source tables after I've copied that data to an 'archive' table. The
application is to manage appointments, and these are appointments which are
now in the past.

I got the "select past appointments" query working ok, and got the "append
those past appointments to the archive table" query working fine too. Now
comes deleting. I'm pretty sure you can't delete from a query(!), so what I
essentially need to do is delete * from tblApts where tblApts.ID is present
in the qryPastApts record set in the field named [aid].

I'm pretty sure I'm going to need some nesting in there somewhere, and I'm so
close I can taste it, but I can't wrap my head around the syntax for the 'is
present in' part.

I barely grok nested selects, and to nest a select inside a delete is
straining my envelope!

If anyone could offer some assistance, I'd greatly appreciate it.

Thanks in advance,

JK

Oh darn! I just solved it! Here's what I came up with, just in case it'll
help someone else!

DELETE *
FROM tblApts AS A
WHERE (((A.id)=(select [aid] from qryPastApts as B where a.id=b.aid )));
 
I got the "select past appointments" query working ok, and got the "append
those past appointments to the archive table" query working fine too. Now
comes deleting. I'm pretty sure you can't delete from a query(!),
Click to expand...

Sure you can. If it's an updateable query you can do anything that you
can do in a Table.
so what I
essentially need to do is delete * from tblApts where tblApts.ID is present
in the qryPastApts record set in the field named [aid].
Click to expand...

Several ways:

DELETE * FROM tblApts
WHERE tblApts.ID IN(SELECT AID FROM qryPastApts);

Or, if ID is (as it should be) a unique field,

DELETE tblApts.*
FROM tblApts
INNER JOIN qryPastApts
ON qryPastApts.AID = tblApts.ID;


John W. Vinson[MVP]
 
John said:
Sure you can. If it's an updateable query you can do anything that you
can do in a Table.
Click to expand...

cool. That's good to know. I tried that and it didn't work, but I think it
was due to a problem I found when trying the nested select thing instead of
the join thing.

Thanks John.

JK
 
Back
Top