Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. (Access 2007)
    I have a table of staff (tblStaff) including StaffID field, and a second table tblLeavers with a few of these staff members. How can I create a delete query that
    will delete those staff in the tblStaff that appear in the second table?
    I think this should be very simple, but I can't get it to work?! Thanks greatly, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try a query with this SQL:

    DELETE * FROM tblStaff WHERE StaffID In (SELECT StaffID FROM tblLeavers)

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi. That's great. (I got there two seconds before I read your answer - must be telepathy?)
    But why? Why can't I just link the two tables in Query Design and delete * from tblStaff WHERE StaffID = tblLeavers!StaffID? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by andrewgibsonsw View Post
    But why? Why can't I just link the two tables in Query Design and delete * from tblStaff WHERE StaffID = tblLeavers!StaffID? Andy.
    It depends on the way the tables are set up. If the query as a select query is not updateable, you won't be able to use it as a delete query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •