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