|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 13th, 2009, 07:19 PM
posted to microsoft.public.access.queries
|
|||
|
|||
delete query using inner join
The following sql in access
DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX], DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET  ATE], 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 EPT], 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 ATE])=[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 
|
|
#2
December 14th, 2009, 02:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
delete query using inner join
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" wrote in message ... The following sql in access DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX], DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET ATE],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 EPT], 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 ATE])=[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
|
|
#3
December 16th, 2009, 04:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
delete query using inner join
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 ATE])=[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" wrote: The following sql in access DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX], DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET ATE], 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 EPT], 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 ATE])=[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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
