Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-table Delete Query (A2K)

    I'm attempting to design a delete query calling records from two tables, where if one field from each table matches, then delete all records with that match from one of the tables. I'd appreciate any suggestions or alternate methods.

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

    Re: Multi-table Delete Query (A2K)

    Why do you need alternate methods? The only trick to delete queries is that in 2000 and later you must be sure to add the DISTINCTROW keyword, which can be done from the query grid by right clicking on the gray area of the grid and opening the properties dialog for the query. Set Unique Records to Yes, and that will insert DISTINCTROW and make your query updatable.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multi-table Delete Query (A2K)

    I was looking to make a query updatable and this worked using 2 tables in the query. If I tried using 1 table and 1 union query in the query, I could not make it updatable by this method. Is it possible to accomplish what I am trying to do???
    Thanks, John

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

    Re: Multi-table Delete Query (A2K)

    We don't know enough to provide detailed help, but you can probably use a subquery. If you want to delete records from the table that have a match in the union query, the SQL looks like this:

    DELETE * FROM tblSomething WHERE UniqueID IN (SELECT UniqueID FROM qryUnion)

    You can't do it the other way round - a union query isn't updatable.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multi-table Delete Query (A2K)

    Hi Hans,
    I am trying to update (change not delete) the records in tblwlcostbook. This what the SQL looks like so far:

    SELECT DISTINCTROW tblCostBook.[Mod], tblCostBook.SN, tblCostBook.LN, qryJoinJNOP.op, tblCostBook.JN, tblCostBook.UCost, tblCostBook.OF, tblCostBook.[D#]
    FROM qryJoinJNOP RIGHT JOIN tblCostBook ON qryJoinJNOP.jn = tblCostBook.JN;

    I am not sure how to change this.
    Thank you for your help. John

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

    Re: Multi-table Delete Query (A2K)

    This is a select query, not an update query. You need to explain what you're trying to update and why you have another query in there.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multi-table Delete Query (A2K)

    Hi Charlotte-
    I am using a union query to bring together the numbers (jn) and the description (op) from a purchased part number table and a labor table. In the select query I am bringing together the Cost Book that has no descriptions, just numbers, and the union query for the description of the number. When I run this select query, I would like to be able to change the information in the fields of the records when we make changes in manufacturing. We might want to change a number because we are using a different part number for a certain model. I also have a field that lets me print the records in a report in a certain order. BY looking at this select query I can tell whether or not the records are in the correct order when I make a change by looking at the description. If they are not in the right order, I would want to be able to change the field contents for any record in the query. Just looking at numbers (jn) are meaningless without the description (op).
    If this still doesn't make sense, please let me know and I will try again.
    Thank you for your help. John

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

    Re: Multi-table Delete Query (A2K)

    Sorry, but union queries and any queries that include unions are not updatable. That's the nature of the beast and there isn't any workaround. The way to avoid the problem in the first place is through the appropriate use of forms and subforms, not queries.
    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
  •