-----Original Message-----
Hi,
Thank you for using the Microsoft Access Newsgroups.
You wrote:
"I have a temporary table (tblQualifications) containing [EmplNbr],
[RDO](Regular Day Off), [Date] (datepart day of week) and [qual]. If I
want a report for saturday I delete the rows where [RDO] = 7. The problem
is that employees have two days a week off and they will have rows
remaining in the table.
How can I delete the entire instance of [EmplNbr] when [RDO] = 7?"
I take that the dilemna to your issue is that your table is laid out as
follows:
[EmplNbr] [RDO] [Date] [qual]
123 7 xxx xxxx
123 6 xxx xxxx
123 4 xxx xxxx
456 3 xxx xxxx
And you would like to delete all instances of [EmplNbr] = 123 because there
is one records has the value 7 for [RDO]
SOLUTION:
- Create a query where you include your table twice (notice that the 2nd
instance of the table has the name [table name]_1
- Inner Join (link the tables) by [EmplNbr]
- Select the * symbol from the first instance of the table
- Select the field [RDO] from the 2nd instance and for the Criteria place 7
- Change query into a delete query (menu option Query > Delete Query)
Your SQL Syntax basically becomes something like:
DELETE Customers.*, Customers_1.ContactTitle
FROM Customers INNER JOIN Customers AS Customers_1 ON Customers.CustomerID
= Customers_1.CustomerID WHERE (((Customers_1.ContactTitle)="manager"));
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms0 3-026.asp> and/or
to visit Windows Update at
