Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Non-updatable query (A2003)

    If I have a query that joins a table to itself, is it automatically non-updatable?

    Here is situation. I have a parent/child relationship between 2 tables. For each Parent, there are multiple records in the child table (one record for each Admin). The fields in the child table are (ParentID & AdminID comprise the PK):

    ParentID
    AdminID
    Notes

    On a form, I need to display side-by-side the notes for 2 admins for the same "Parent" record. I created a query joining the child table to itself based on the ParentID, and specifying the proper AdminID. The query works fine, and my form displays the info. Unfortunately, I can't update the fields, as the query is non-updatable.

    I can get around this by changing my form so it displays a record for the Notes from one Admin, then uses a subform to display the notes for the other one. But I don't understand why the original query is non-updatable.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Non-updatable query (A2003)

    There is no unique index on the link field (only), so the query is not updateable.

  4. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Non-updatable query (A2003)

    I redid the query, starting with the Parent table and joining each occurance of the child table to it based on the ParentID field (unique to Parent table). Still not updatable. I then changed the recordset of the query to "Inconsistent Updates", and it allowed me to update it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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