Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Query (Access 2000 SR-1)

    I'm trying to create a delete query that draws its criteria from another table.
    I have two tables
    tbl1.Val A,B,C
    tbl2.Val A,B,C,D,E,F,G,...Z
    I want to delete all occurrences of A,B,C (which are in tbl1) in tbl2.
    I can create a select query which gives me the right result, but when I convert it to a delete query
    I get an error message "Specify the table containing the records you want to delete".
    I want the delete query to dynamically draw from a table that the user can update without
    them having to retype/change the criteria in a delete query.
    Regards
    Michael

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Query (Access 2000 SR-1)

    I assume that Val is the field name. If there is a unique index on Val in both tables (for example the primary key, but that's not necessary), you should be able to use a qyery with the following SQL:

    DELETE tbl2.*
    FROM tbl2 INNER JOIN tbl2 ON tbl1.Val = tbl2.Val;

    If there is no unique key, you'll have to use SQL like this:

    DELETE tbl2.Val
    FROM tbl2
    WHERE tbl2.Val In (SELECT Val FROM tbl1);

    (This should work in all circumstances)

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (Access 2000 SR-1)

    Thanks Hans,
    Yes you are right that Val is a field name.
    I constrained myself by trying to build the delete query from the QBE.
    In certain circumstances it appears that I'd be better off coding the SQL statement directly.
    Your two examples make sense, and more importantly work correctly.

    This query is part of the 'denormalised' database I inherited on which you gave me some previous advice.
    I'm slowly but surely getting to a point where the user does not need to edit queries.

    Thanks again
    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Query (Access 2000 SR-1)

    Since Val() is a VBA function, it isn't a good idea to use that as a field name.
    Charlotte

Posting Permissions

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