Tom, Bingo! That was it.
Using DELETE Table1.* FROM ... works, and using DELETE Table1.Field1, Table1.Field2, Table1.Field3 FROM ... doesn't. I guess that comes under the heading of "go figure...", but it works, and that is what counts. Many thanks. Far > -----Original Message----- > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes > Sent: Monday, January 23, 2006 10:06 PM > To: AccessDevelopers@yahoogroups.com > Subject: RE: [AccessDevelopers] Specify Table in Delete Query > > If I wanted to delete all orders that belong to customers that are in the > state of New Mexico, I might do a subquery like this: > > DELETE * FROM tblOrder WHERE OrderCustomerID IN (SELECT DISTINCT > CustomerID FROM tblCustomer WHERE CustomerState='NM') > > ...although the following *should* work as well: > > DELETE tblOrder.* > FROM tblOrder INNER JOIN tblCustomer ON > tblCustomer.CustomerID=tblOrder.OrderCustomerID > WHERE tblCustomer.CustomerState='NM' > > > > Tom Oakes > Personal PC Consultants, Inc. > [EMAIL PROTECTED] > 503.230.0911 (O) > 402.578.2648 (C) > 512.727.9497 (F) > > > > > > _____________________________________________ > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of The Professional > Network > Sent: Monday, January 23, 2006 2:26 PM > To: AccessDevelopers@yahoogroups.com > Subject: RE: [AccessDevelopers] Specify Table in Delete Query > > > > Tom, > > You're right, and I guess I didn't pick a very good example to support my > question. > > What I'm looking for is a generic one-step approach for doing deletes with > joined queries. Using a different example: > > > tbl_MasterCustomerList > ================== > PK CustomerNumber > DeleteFlag > > > tbl_RecordsToBeDeletedFromCustomerList > ================================ > PK CustomerNumber > > > I want to delete every record in tblMasterCustomerList that has a matching > record in tbl_RecordsToBeDeletedFromCustomerList, and the information on > what to delete is not in the table where the records will be deleted. > > The way I do it now, which I find very kludgy, is a two step process. I > do an update query with a join to flag the records in > tbl_MasterCustomerList, then follow that with a second query without a > join that does a delete action on the flagged records. > > Since when I attempt to do this in one step Access is asking me to specify > a table in which I will delete the records, it seems logical to me that > the syntax to do the delete in one step exists. I am looking for that > piece of SQL syntax. > > Far > > > -----Original Message----- > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes > Sent: Monday, January 23, 2006 5:00 PM > To: AccessDevelopers@yahoogroups.com > Subject: RE: [AccessDevelopers] Specify Table in Delete Query > > Far, > > It looks like your Department Number exists in the Employees table, > so you don't need to include the tbl_Department at all: > > DELETE * FROM tbl_Employee WHERE DepartmentNumber=10 > > You don't need to specify fields when executing DELETE statements - > it's going to delete entire records, so field specification is > unnecessary. > > Tom Oakes > Personal PC Consultants, Inc. > [EMAIL PROTECTED] > 503.230.0911 (O) > 402.578.2648 (C) > 512.727.9497 (F) > > > > _____________________________________________ > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of The Professional > Network > Sent: Monday, January 23, 2006 11:46 AM > To: AccessDevelopers@YahooGroups.com > Subject: [AccessDevelopers] Specify Table in Delete Query > > > I want to use a query to delete records. The querywould be a join > of two tables that have a 1:M relationship, let's say Employee and > Department. > > I made this query: > > DELETE tbl_Employee.EmployeeNumber, tbl_Employee.EmployeeName, > tbl_Department.DepartmentNumber, tbl_Department.DepartmentName > FROM tbl_Department INNER JOIN tbl_Employee ON > tbl_Department.DepartmentNumber = tbl_Employee.DepartmentNumber > WHERE (((tbl_Department.DepartmentNumber)=10)); > > > On trying to run this query, Access returns the following error: > > Specify the table containing the records you want to delete. > > > What do I need to do to specify that I want the records removed from > the Employee Table? > > TIA, > > Far Farley > Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
<<attachment: winmail.dat>>
