Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    39

    Unanswered: Update query: "Operation must use an updatable query" error

    I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now.

    I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access.

    I am trying to perform a calculation based on information in a few tables, and use the result to update another.

    Should be simple, here's what counts from the tables that matter:

    INVENTORY TABLE:
    ComponentPartNumber (key)
    Quantity

    JOB ORDERS TABLE:
    AssemblyNumber (key)
    BuildQuantity

    ASSEMBLY TABLE:
    AssemblyNumber (key)
    ReferenceID (key)
    ComponentPartNumber

    The JOB ORDERS table contains information on how many assemblies we are contracted to build.
    The ASSEMBLY table contains reference ID's for each location in an electronic assembly, and the part number of the component that is placed at each location. One component might be used in several places in an assembly.
    The INVENTORY table contains information on how many component parts we have in inventory.

    Basically, I'm looking to sum up how many of each component is used in a particular assembly, and multiply that by how many assemblies we built. I want to take that number (by component, of course) and update my inventory by subtracting it from the existing Qty.

    No matter what I do, I get the error "Operation must use an updatable query".
    I've tried using a stored query to join ASSEMBLY to JOB ORDERS and precalculate a NewQty so I could do a straightforward "set Qty=NewQty", I've tried doing it all in one query - but I can't get around doing a count(*) at some level - and since I can't do that IN my update statement (since I can't do a "group by"), I have to do it in a stored query. But - I'm not updating that query - so I'm nothing if not puzzled by this error! All joins at all times are simple inner joins.

    Where am I going wrong here?

  2. #2
    Join Date
    Jun 2008
    Posts
    39
    I'm not sure if it'll help since this is just ONE way I've tried it.
    Actual table names used here - not hard to figure out:
    Code:
    UPDATE  Inventory 
    INNER JOIN (Parts INNER JOIN ((AssemblyParts INNER JOIN JobOrders ON (AssemblyParts.AssyPN = JobOrders.AssyPN) AND (AssemblyParts.Rev = JobOrders.Rev)) INNER JOIN AssemblyPartsCounts ON (AssemblyParts.AssyPN = AssemblyPartsCounts.AssyPN) AND (AssemblyParts.Rev = AssemblyPartsCounts.Rev) AND (AssemblyParts.BOMPN = AssemblyPartsCounts.MfgPN)) ON Parts.MfgPN = AssemblyParts.UsedPN)  ON Parts.MfgPN = Inventory.MfgPN
    SET Inventory.Qty = Inventory.Qty-(JobOrders.POQty*AssemblyPartsCounts.Used)
    WHERE AssemblyParts.AssyPN="SCMA-7";
    The stored query AssemblyPartsCounts code is as follows:
    Code:
    SELECT Assemblies.AssyPN, Assemblies.Rev, Assemblies.MfgPN, Count(*) AS Used
    FROM Assemblies
    GROUP BY Assemblies.AssyPN, Assemblies.Rev Assemblies.MfgPN;
    Anything jump out that would cause this error?

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can't update a query that is grouped.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your syntax is wrong too
    Code:
    UPDATE
    SET
    FROM [join]
    WHERE
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Posts
    39
    Exactly hence the confusion - I'm not updating a query that's grouped, the Inventory table is the one being updated.
    I'm joining in the stored query that requires the grouping to perform the calculation (to obtain a count, per part number).

    I'm using Access 2003, and I'm very much disliking the Jet SQL format...
    The syntax that I used passes as correct, in fact it's how Access built it. ??

    Beyond that, I have another concern with the described syntax -
    The problem is in part that I need to use the value from another table (in fact, the value from a stored query that involves the grouping to obtain the count), and the syntax using THAT format wouldn't allow a "SET Inventory.Qty = AssemblyPartsCounts.Used", it would seem.

Posting Permissions

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