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 )));
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 )));