delete query in Access

In all the db work I have done... I dont' think I've ever tried to delete
from more than 1 table at a time... Don't even know if it can be done.

From my stand point. I'd run 2 deletes one for table1 one for table2.
Now if you are only trying to delete records that exist in both tables and
leave alone the ones that exist solely in one or the other, I'd write an
update query first that changes some record value to a uniq string value
then issue the deltees to get rid of those records wtih a field containintg
that string value. The other option would be to setup relationships so taht
when one is removed, the other is as well.
Cascade deltee... But those are kinda risky if you dont' understand everything.

Good luck and hope this helped,
Q*bert
#@*^$

"gdc" <tangovu@hotmail.com> wrote:
>
>I have this select script which works fine in Access:
>
>SELECT a1.paygroup, a1.adpid, a1.check_dt, a1.erncd
>FROM table1 as a1, table2 as a2
>WHERE a1.PAYGROUP = a2.Paygroup
>AND a1.Number= a2.Number
>AND (a1.Number="000054") AND (a1.CHECK_DT=#8/15/2002#) AND (a1.ERNCD In
("reg","ot"));
>
>But when I change the query to a Delete query, it changed to this:
>
>DELETE a1.PAYGROUP, a1.ADPID, a1.CHECK_DT, a1.ERNCD
>FROM table1 as a1, table2 as a2
>WHERE a1.PAYGROUP = a2.Paygroup
>AND a1.Number= a2.Number
>AND (a1.Number="000054") AND (a1.CHECK_DT=#8/15/2002#) AND (a1.ERNCD In
("reg","ot"));
>
>When I ran the delete query, it is saying "Specify the table containing
the
>records you want to delete". I cant figure out what is missing. Help please.

Ahh much clearer
... Be sure to make a backup copy before trying this I'd hate to lead you
down the wrong path

DELTE *
FROM table2 a2
WHERE
a2.adpid IN (
SELECT file_nbr
FROM table1 a1
WHERE (a1.Number="000054") AND
(a1.CHECK_DT=#8/15/2002#) AND
(a1.ERNCD In("reg","ot")));

"gdc" <tangovu@hotmail.com> wrote:
>
>Thanks for the quick response, but I guess I wasn't being too clear on what
>I need. I have 2 tables, and I want to join them by two common fields.

>If there are records in table2, then I want to delete those records only.
> I dont want to delete the records in table1. Here are some sample data:
>
>table1:
>paygroup file_nbr
>--- ---
>arc 000054
>
>table2:
>paygroup adpid check_dt erncd
>--- -- --- --
>arc 000054 8/15/2002 reg
>arc 000054 8/15/2002 ot
>arc 100100 8/15/2002 reg
>
>If table1.paygroup = table2.paygroup
>and table1.file_nbr = table2.adpid
>and table2.check_dt = #8/15/02#
>and table2.erncd in ("reg","ot")
>then delete the records in table2
>
>From the sample data above, the first 2 records in table2 will be deleted.
>
>
>
>"Q*bert" <luke_davis_76@hotmail.com> wrote:
>>
>>In all the db work I have done... I dont' think I've ever tried to delete
>>from more than 1 table at a time... Don't even know if it can be done.
>>
>>From my stand point. I'd run 2 deletes one for table1 one for table2.
>>Now if you are only trying to delete records that exist in both tables
and
>>leave alone the ones that exist solely in one or the other, I'd write an
>>update query first that changes some record value to a uniq string value
>>then issue the deltees to get rid of those records wtih a field containintg
>>that string value. The other option would be to setup relationships so
>taht
>>when one is removed, the other is as well.
>>Cascade deltee... But those are kinda risky if you dont' understand everything.
>>
>>Good luck and hope this helped,
>>Q*bert
>>#@*^$
>>
>>"gdc" <tangovu@hotmail.com> wrote:
>>>
>>>I have this select script which works fine in Access:
>>>
>>>SELECT a1.paygroup, a1.adpid, a1.check_dt, a1.erncd
>>>FROM table1 as a1, table2 as a2
>>>WHERE a1.PAYGROUP = a2.Paygroup
>>>AND a1.Number= a2.Number
>>>AND (a1.Number="000054") AND (a1.CHECK_DT=#8/15/2002#) AND (a1.ERNCD In
>>("reg","ot"));
>>>
>>>But when I change the query to a Delete query, it changed to this:
>>>
>>>DELETE a1.PAYGROUP, a1.ADPID, a1.CHECK_DT, a1.ERNCD
>>>
>>>When I ran the delete query, it is saying "Specify the table containing
>>the
>>>records you want to delete". I cant figure out what is missing. Help
>please.
>>
>

Your doin good, I'd make the following changes though

Not exactly sure what [unique file_nbr arc_081502] is...
query your building on?

DELETE *
FROM adp_reporting a2
WHERE
a2.adpid IN (
SELECT file_nbr
FROM [unique file_nbr arc_081502] a1
INNER JOIN adp_reporting a2 ON
a1.paygroup=a2.paygroup
AND a2.adpid="000054"
AND a2.CHECK_DT=#8/15/2002#)
AND (a2.ERNCD ="reg" OR a2.erncd="ot"));

Using inner, left or right joins are more effecient than the "in" statement.
Using 3 "INs" in 1 statement is really not the most effecient.
The above represents the most effecient method I can think of to do the delete
statement.
Hope this helped.

Q*bert
@#(*$

>"Q*bert" <luke_davis_76@hotmail.com> wrote:
>>
>>Ahh much clearer
>>... Be sure to make a backup copy before trying this I'd hate to lead
you
>>down the wrong path
>>
>>DELTE *
>>FROM table2 a2
>>WHERE
>>a2.adpid IN (
>>SELECT file_nbr
>>FROM table1 a1
>>WHERE (a1.Number="000054") AND
>>(a1.CHECK_DT=#8/15/2002#) AND
>>(a1.ERNCD In("reg","ot")));
>>
>>
>>
>>"gdc" <tangovu@hotmail.com> wrote:
>>>
>>>Thanks for the quick response, but I guess I wasn't being too clear on
>what
>>>I need. I have 2 tables, and I want to join them by two common fields.
>>
>>>If there are records in table2, then I want to delete those records only.
>>> I dont want to delete the records in table1. Here are some sample data:
>>>
>>>table1:
>>>paygroup file_nbr
>>>--- ---
>>>arc 000054
>>>
>>>table2:
>>>paygroup adpid check_dt erncd
>>>--- -- --- --
>>>arc 000054 8/15/2002 reg
>>>arc 000054 8/15/2002 ot
>>>arc 100100 8/15/2002 reg
>>>
>>>If table1.paygroup = table2.paygroup
>>>and table1.file_nbr = table2.adpid
>>>and table2.check_dt = #8/15/02#
>>>and table2.erncd in ("reg","ot")
>>>then delete the records in table2
>>>
>>>From the sample data above, the first 2 records in table2 will be deleted.
>>>
>>>
>>>
>>>"Q*bert" <luke_davis_76@hotmail.com> wrote:
>>>>
>>>>In all the db work I have done... I dont' think I've ever tried to delete
>>>>from more than 1 table at a time... Don't even know if it can be done.
>>>>
>>>>From my stand point. I'd run 2 deletes one for table1 one for table2.
>>>>Now if you are only trying to delete records that exist in both tables
>>and
>>>>leave alone the ones that exist solely in one or the other, I'd write
>an
>>>>update query first that changes some record value to a uniq string value
>>>>then issue the deltees to get rid of those records wtih a field containintg
>>>>that string value. The other option would be to setup relationships
so
>>>taht
>>>>when one is removed, the other is as well.
>>>>Cascade deltee... But those are kinda risky if you dont' understand everything.
>>>>
>>>>Good luck and hope this helped,
>>>>Q*bert
>>>>#@*^$
>>>>
>>>>"gdc" <tangovu@hotmail.com> wrote:
>>>>>
>>>>>I have this select script which works fine in Access:
>>>>>
>>>>>SELECT a1.paygroup, a1.adpid, a1.check_dt, a1.erncd
>>>>>FROM table1 as a1, table2 as a2
>>>>>WHERE a1.PAYGROUP = a2.Paygroup
>>>>>AND a1.Number= a2.Number
>>>>>AND (a1.Number="000054") AND (a1.CHECK_DT=#8/15/2002#) AND (a1.ERNCD
>In
>>>>("reg","ot"));
>>>>>
>>>>>But when I change the query to a Delete query, it changed to this:
>>>>>
>>>>>DELETE a1.PAYGROUP, a1.ADPID, a1.CHECK_DT, a1.ERNCD
>>>>>
>>>>>When I ran the delete query, it is saying "Specify the table containing
>>>>the
>>>>>records you want to delete". I cant figure out what is missing. Help
>>>please.
>>>>
>>>
>>
>