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.
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.