Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: Keeping Query updatable with multiple tables

    I am attempting to do what initially seemed straight forward but I am running into the problem of not being able to update the records from my query.

    The big picture: Database for maintaining a parts catalog. Parts come from different vendors (a part can only have one vendor). Vendors are internally grouped in several ways (One vendor can belong to several groups) for example, vendor abc might be a member of the local vendors group and the active group.

    Current design: 4 tables are involved:
    tblPart, tblVendor, tblGroup and tblVendorGroup.
    tblPart is the part list, with a PartIndex as PK and a field for the MFG

    tblVendor has MFG as PK

    tblGroup is the list of possible groups that a vendor can belong to, it has a groupIndex as PK and a description

    tblVendorGroup has an autonumber index as PK with fields for MFG and groupIndex. It also has a unique index on the MFG\Group combination.

    all 4 tables are related. tblPart links to tblVendor which links to tblVendorGroup which links to tblGroup

    What I want: The desired query will return all the records in tblPart where the MFG is a member of a particular group in an updatable format.

    I tried creating a query with just tblPart and tblVendorGroup and also with tblPart, tblVendor and tblVendorGroup. Every way that I have tried will not update.

    Although MFG is unique for a particular group in the VendorGroup table it is not unique for the entire table therefore I run into the updatable problem

    Is there a way that I can redesign a few things or something that I can do to allow the list to update. Essentially it is a just a list of parts filtered by a specific list of vendors.

    The only method I have found that works is to create a table with the results of query on vendors and groups then set the PK to the MFG field and then create the query with this table and the parts table.
    This really isn't a good solution due to the number of groups and the dynamic nature of them. Group members change over time and the parts list view needs to be for the current list of members. I cannot rely on the fact that the user will update the table prior to getting a list of parts. Also, the view of parts for the active group of vendors would be used many times in the application as a record source.

    Any thoughts?

    Steve

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Post the SQL that you've tried so far. Maybe we can help you tweak it.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You could try changing the Property of the RecordsetType in the query to Dynaset ( Inconsistent Updates) and even though it will allow you to make updates, this is not recommended.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    The inconsistent updates idea does work but why is it not recommended? What problems am I likely to encounter if I take this approach?

    The attached file is a stripped down version of the database and the query that I am trying to have.

    Thanks for the help.
    Attached Files Attached Files

    Steve

Posting Permissions

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