delete query using inner join

S

seeker

The following sql in access
DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX],
DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET:DATE],
DETAIL2.[DET:JULDATE], DETAIL2.[DET:ITEMNBR1], DETAIL2.[DET:ITEMNBR2],
DETAIL2.[DET:ITEMNBR3], DETAIL2.[DET:ITEMNBR4], DETAIL2.[DET:ITEMNBR5],
DETAIL2.[DET:ITEMDESC1], DETAIL2.[DET:ITEMDESC2], DETAIL2.[DET:ITEMDESC3],
DETAIL2.[DET:ITEMDESC4], DETAIL2.[DET:ITEMDESC5], DETAIL2.[DET:pRICE1],
DETAIL2.[DET:pRICE2], DETAIL2.[DET:pRICE3], DETAIL2.[DET:pRICE4],
DETAIL2.[DET:pRICE5], DETAIL2.[DET:IPRICE1], DETAIL2.[DET:IPRICE2],
DETAIL2.[DET:IPRICE3], DETAIL2.[DET:IPRICE4], DETAIL2.[DET:IPRICE5],
DETAIL2.[DET:CARD1], DETAIL2.[DET:CARD2], DETAIL2.CardToSend,
DETAIL2.CardToSend1Year, DETAIL2.[DET:REMARK1], DETAIL2.[DET:REMARK2],
DETAIL2.[DET:REMARK3], DETAIL2.[DET:SUBTOT], DETAIL2.[DET:TAXABLE],
DETAIL2.[DET:TAX], DETAIL2.[DET:CARYEAR], DETAIL2.[DET:CARKEY],
DETAIL2.[DET:CARID], DETAIL2.[DET:FORSALE], DETAIL2.[DET:TOMAIL],
DETAIL2.[DET:NOTE], DETAIL2.[DET:DEPT], DETAIL2.[DET:pAYROLLDATE],
DETAIL2.[DET:EMPW1], DETAIL2.[DET:EMPW2], DETAIL2.[DET:EMPP1],
DETAIL2.[DET:EMPO1], DETAIL2.[DET:EMPO2], DETAIL2.[DET:EMPO3],
DETAIL2.[DET:EMPI1], DETAIL2.[DET:EMPI2], DETAIL2.[DET:EMPI3],
DETAIL2.[DET:EMPX1], DETAIL2.[DET:EMPX2], DETAIL2.[DET:EMPX3A],
DETAIL2.[DET:EMPX3B], DETAIL2.[DET:EMPX3C], DETAIL2.[DET:WASH1],
DETAIL2.[DET:WASH2], DETAIL2.[DET:MOTOR], DETAIL2.[DET:pAINTYN],
DETAIL2.[DET:pAINT], DETAIL2.[DET:OUT1], DETAIL2.[DET:OUT2],
DETAIL2.[DET:OUT3], DETAIL2.[DET:INT1], DETAIL2.[DET:INT2],
DETAIL2.[DET:INT3], DETAIL2.[DET:XTDESC1], DETAIL2.[DET:XTDESC2],
DETAIL2.[DET:XTDESC3], DETAIL2.[DET:XTDESC4], DETAIL2.[DET:XTDESC5],
DETAIL2.[DET:XT1], DETAIL2.[DET:XT2], DETAIL2.[DET:XT3A], DETAIL2.[DET:XT3B],
DETAIL2.[DET:XT3C], DETAIL2.[DET:XTP1], DETAIL2.[DET:XTP2],
DETAIL2.[DET:XTP3A], DETAIL2.[DET:XTP3B], DETAIL2.[DET:XTP3C],
DETAIL2.[DET:WASHP1], DETAIL2.[DET:WASHP2], DETAIL2.[DET:pAINTP],
DETAIL2.[DET:OUTP1], DETAIL2.[DET:OUTP2], DETAIL2.[DET:OUTP3],
DETAIL2.[DET:INTP1], DETAIL2.[DET:INTP2], DETAIL2.[DET:INTP3],
qryCountOfInvoices.[CountOfDET:INVOICE]
FROM DETAIL2 INNER JOIN qryCountOfInvoices ON DETAIL2.[DET:CUSTNO] =
qryCountOfInvoices.[DET:CUSTNO]
WHERE
(((DETAIL2.[DET:DATE])<=[forms]![frmarchiveinvoicesoptions]![txtarchivedate])
AND ((DETAIL2.[DET:TAXABLE]) Like
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all','*'))))
AND
((qryCountOfInvoices.[CountOfDET:INVOICE])>=[forms]![frmarchiveinvoicesoptions]![txtnumberofinvoices]));

gives me the error that I need to specify which table I am deleteing from.
I have the count in the joined table to determine the customers with a
certain number of invoices. These invoices need to be deleted from detail2.
If i put the count in a subquery for criteria to be deleted and place
count(det:invoice) it tells me that an aggregate can not be used in a delete
query. Thanks for your help
 
V

vanderghast

Is your query updateable?

If so, remove the list of fields between the keywords DELETE and FROM, you
don't delete individual fields, but rows, and use something like:

DELETE DISTINCTROW detail2.* FROM ....


But again, be sure that your actual query IS updateable. I guess it is not,
seeing a CountOfInvoices.


Vanderghast, Access MVP



seeker said:
The following sql in access
DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX],
DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET:DATE],
DETAIL2.[DET:JULDATE], DETAIL2.[DET:ITEMNBR1], DETAIL2.[DET:ITEMNBR2],
DETAIL2.[DET:ITEMNBR3], DETAIL2.[DET:ITEMNBR4], DETAIL2.[DET:ITEMNBR5],
DETAIL2.[DET:ITEMDESC1], DETAIL2.[DET:ITEMDESC2], DETAIL2.[DET:ITEMDESC3],
DETAIL2.[DET:ITEMDESC4], DETAIL2.[DET:ITEMDESC5], DETAIL2.[DET:pRICE1],
DETAIL2.[DET:pRICE2], DETAIL2.[DET:pRICE3], DETAIL2.[DET:pRICE4],
DETAIL2.[DET:pRICE5], DETAIL2.[DET:IPRICE1], DETAIL2.[DET:IPRICE2],
DETAIL2.[DET:IPRICE3], DETAIL2.[DET:IPRICE4], DETAIL2.[DET:IPRICE5],
DETAIL2.[DET:CARD1], DETAIL2.[DET:CARD2], DETAIL2.CardToSend,
DETAIL2.CardToSend1Year, DETAIL2.[DET:REMARK1], DETAIL2.[DET:REMARK2],
DETAIL2.[DET:REMARK3], DETAIL2.[DET:SUBTOT], DETAIL2.[DET:TAXABLE],
DETAIL2.[DET:TAX], DETAIL2.[DET:CARYEAR], DETAIL2.[DET:CARKEY],
DETAIL2.[DET:CARID], DETAIL2.[DET:FORSALE], DETAIL2.[DET:TOMAIL],
DETAIL2.[DET:NOTE], DETAIL2.[DET:DEPT], DETAIL2.[DET:pAYROLLDATE],
DETAIL2.[DET:EMPW1], DETAIL2.[DET:EMPW2], DETAIL2.[DET:EMPP1],
DETAIL2.[DET:EMPO1], DETAIL2.[DET:EMPO2], DETAIL2.[DET:EMPO3],
DETAIL2.[DET:EMPI1], DETAIL2.[DET:EMPI2], DETAIL2.[DET:EMPI3],
DETAIL2.[DET:EMPX1], DETAIL2.[DET:EMPX2], DETAIL2.[DET:EMPX3A],
DETAIL2.[DET:EMPX3B], DETAIL2.[DET:EMPX3C], DETAIL2.[DET:WASH1],
DETAIL2.[DET:WASH2], DETAIL2.[DET:MOTOR], DETAIL2.[DET:pAINTYN],
DETAIL2.[DET:pAINT], DETAIL2.[DET:OUT1], DETAIL2.[DET:OUT2],
DETAIL2.[DET:OUT3], DETAIL2.[DET:INT1], DETAIL2.[DET:INT2],
DETAIL2.[DET:INT3], DETAIL2.[DET:XTDESC1], DETAIL2.[DET:XTDESC2],
DETAIL2.[DET:XTDESC3], DETAIL2.[DET:XTDESC4], DETAIL2.[DET:XTDESC5],
DETAIL2.[DET:XT1], DETAIL2.[DET:XT2], DETAIL2.[DET:XT3A],
DETAIL2.[DET:XT3B],
DETAIL2.[DET:XT3C], DETAIL2.[DET:XTP1], DETAIL2.[DET:XTP2],
DETAIL2.[DET:XTP3A], DETAIL2.[DET:XTP3B], DETAIL2.[DET:XTP3C],
DETAIL2.[DET:WASHP1], DETAIL2.[DET:WASHP2], DETAIL2.[DET:pAINTP],
DETAIL2.[DET:OUTP1], DETAIL2.[DET:OUTP2], DETAIL2.[DET:OUTP3],
DETAIL2.[DET:INTP1], DETAIL2.[DET:INTP2], DETAIL2.[DET:INTP3],
qryCountOfInvoices.[CountOfDET:INVOICE]
FROM DETAIL2 INNER JOIN qryCountOfInvoices ON DETAIL2.[DET:CUSTNO] =
qryCountOfInvoices.[DET:CUSTNO]
WHERE
(((DETAIL2.[DET:DATE])<=[forms]![frmarchiveinvoicesoptions]![txtarchivedate])
AND ((DETAIL2.[DET:TAXABLE]) Like
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all','*'))))
AND
((qryCountOfInvoices.[CountOfDET:INVOICE])>=[forms]![frmarchiveinvoicesoptions]![txtnumberofinvoices]));

gives me the error that I need to specify which table I am deleteing from.
I have the count in the joined table to determine the customers with a
certain number of invoices. These invoices need to be deleted from
detail2.
If i put the count in a subquery for criteria to be deleted and place
count(det:invoice) it tells me that an aggregate can not be used in a
delete
query. Thanks for your help
Click to expand...
 
D

Daryl S

Seeker -

You don't need to list the fields in the delete. Also, you can only have on
table in the FROM clause (the table you are deleting records from). This
means the inner join has to become part of the WHERE clause so that your
count of invoices will work.

Try this:


DELETE *
FROM DETAIL2
WHERE
((DETAIL2.[DET:CUSTNO] =
qryCountOfInvoices.[DET:CUSTNO]) AND
((DETAIL2.[DET:DATE])<=[forms]![frmarchiveinvoicesoptions]![txtarchivedate])
AND ((DETAIL2.[DET:TAXABLE]) Like
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all','*'))))
AND
((qryCountOfInvoices.[CountOfDET:INVOICE])>=[forms]![frmarchiveinvoicesoptions]![txtnumberofinvoices]));

--
Daryl S


seeker said:
The following sql in access
DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX],
DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET:DATE],
DETAIL2.[DET:JULDATE], DETAIL2.[DET:ITEMNBR1], DETAIL2.[DET:ITEMNBR2],
DETAIL2.[DET:ITEMNBR3], DETAIL2.[DET:ITEMNBR4], DETAIL2.[DET:ITEMNBR5],
DETAIL2.[DET:ITEMDESC1], DETAIL2.[DET:ITEMDESC2], DETAIL2.[DET:ITEMDESC3],
DETAIL2.[DET:ITEMDESC4], DETAIL2.[DET:ITEMDESC5], DETAIL2.[DET:pRICE1],
DETAIL2.[DET:pRICE2], DETAIL2.[DET:pRICE3], DETAIL2.[DET:pRICE4],
DETAIL2.[DET:pRICE5], DETAIL2.[DET:IPRICE1], DETAIL2.[DET:IPRICE2],
DETAIL2.[DET:IPRICE3], DETAIL2.[DET:IPRICE4], DETAIL2.[DET:IPRICE5],
DETAIL2.[DET:CARD1], DETAIL2.[DET:CARD2], DETAIL2.CardToSend,
DETAIL2.CardToSend1Year, DETAIL2.[DET:REMARK1], DETAIL2.[DET:REMARK2],
DETAIL2.[DET:REMARK3], DETAIL2.[DET:SUBTOT], DETAIL2.[DET:TAXABLE],
DETAIL2.[DET:TAX], DETAIL2.[DET:CARYEAR], DETAIL2.[DET:CARKEY],
DETAIL2.[DET:CARID], DETAIL2.[DET:FORSALE], DETAIL2.[DET:TOMAIL],
DETAIL2.[DET:NOTE], DETAIL2.[DET:DEPT], DETAIL2.[DET:pAYROLLDATE],
DETAIL2.[DET:EMPW1], DETAIL2.[DET:EMPW2], DETAIL2.[DET:EMPP1],
DETAIL2.[DET:EMPO1], DETAIL2.[DET:EMPO2], DETAIL2.[DET:EMPO3],
DETAIL2.[DET:EMPI1], DETAIL2.[DET:EMPI2], DETAIL2.[DET:EMPI3],
DETAIL2.[DET:EMPX1], DETAIL2.[DET:EMPX2], DETAIL2.[DET:EMPX3A],
DETAIL2.[DET:EMPX3B], DETAIL2.[DET:EMPX3C], DETAIL2.[DET:WASH1],
DETAIL2.[DET:WASH2], DETAIL2.[DET:MOTOR], DETAIL2.[DET:pAINTYN],
DETAIL2.[DET:pAINT], DETAIL2.[DET:OUT1], DETAIL2.[DET:OUT2],
DETAIL2.[DET:OUT3], DETAIL2.[DET:INT1], DETAIL2.[DET:INT2],
DETAIL2.[DET:INT3], DETAIL2.[DET:XTDESC1], DETAIL2.[DET:XTDESC2],
DETAIL2.[DET:XTDESC3], DETAIL2.[DET:XTDESC4], DETAIL2.[DET:XTDESC5],
DETAIL2.[DET:XT1], DETAIL2.[DET:XT2], DETAIL2.[DET:XT3A], DETAIL2.[DET:XT3B],
DETAIL2.[DET:XT3C], DETAIL2.[DET:XTP1], DETAIL2.[DET:XTP2],
DETAIL2.[DET:XTP3A], DETAIL2.[DET:XTP3B], DETAIL2.[DET:XTP3C],
DETAIL2.[DET:WASHP1], DETAIL2.[DET:WASHP2], DETAIL2.[DET:pAINTP],
DETAIL2.[DET:OUTP1], DETAIL2.[DET:OUTP2], DETAIL2.[DET:OUTP3],
DETAIL2.[DET:INTP1], DETAIL2.[DET:INTP2], DETAIL2.[DET:INTP3],
qryCountOfInvoices.[CountOfDET:INVOICE]
FROM DETAIL2 INNER JOIN qryCountOfInvoices ON DETAIL2.[DET:CUSTNO] =
qryCountOfInvoices.[DET:CUSTNO]
WHERE
(((DETAIL2.[DET:DATE])<=[forms]![frmarchiveinvoicesoptions]![txtarchivedate])
AND ((DETAIL2.[DET:TAXABLE]) Like
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all','*'))))
AND
((qryCountOfInvoices.[CountOfDET:INVOICE])>=[forms]![frmarchiveinvoicesoptions]![txtnumberofinvoices]));

gives me the error that I need to specify which table I am deleteing from.
I have the count in the joined table to determine the customers with a
certain number of invoices. These invoices need to be deleted from detail2.
If i put the count in a subquery for criteria to be deleted and place
count(det:invoice) it tells me that an aggregate can not be used in a delete
query. Thanks for your help
Click to expand...
 
Top