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

[geolemon]

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

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?

[geolemon]

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:

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:

SELECT Assemblies.AssyPN, Assemblies.Rev, Assemblies.MfgPN, Count(*) AS Used
FROM Assemblies
GROUP BY Assemblies.AssyPN, Assemblies.Rev Assemblies.MfgPN;

[NeoPa]

Any query that links, in any way, to a non-updatable query, is by necessity, non-updatable itself.

Consider updating the data multiple times (A = A + 1) rather than in one go (A = Count()).

[geolemon]

Any query that links, in any way, to a non-updatable query, is by necessity, non-updatable itself.

Consider updating the data multiple times (A = A + 1) rather than in one go (A = Count()).

Not "by necessity"...
I can do this using views in other DBMS's ;-)

I didn't think I created a non-updatable query, as I'm not updating the query - I"m updating the table directly, and attempting to join the query with the group-by merely adjacently, to pull my new value from - as I'd do outside the world of Access.

I was more suspicious of a syntax error, as I've been fighting with this "query builder", finally resorting to molesting an innocent Select query for the right join syntax - which is why I was suspicious of it.

What is the correct work-around? This obviously isn't an uncommon scenario, needing to sum and count to update data, right? ;-)

[geolemon]

Consider updating the data multiple times (A = A + 1) rather than in one go (A = Count()).

Do you mean by simply joining and letting the duplication do the work?
I suppose I can test that easily enough using a Select query to ensure that I'm not inadvertantly causing multiplication or other cartesian-product-like ill effects.

I definitely am a fan of the explicit rather than the implicit by rule... but everyone has to step out of their comfort zone sometime, right?

[NeoPa]

Not "by necessity"...
I can do this using views in other DBMS's ;-)

Oh absolutely. I did intend to explain that this is a specifically MS Access situation.

I didn't think I created a non-updatable query, as I'm not updating the query - I"m updating the table directly, and attempting to join the query with the group-by merely adjacently, to pull my new value from - as I'd do outside the world of Access.

However, as that is now included into the query, the whole query that it is a part of is now non-updatable. Sorry. That's Access-World for you. It does have limitations when compared to grown-up RDBMSs.

I was more suspicious of a syntax error, as I've been fighting with this "query builder", finally resorting to molesting an innocent Select query for the right join syntax - which is why I was suspicious of it.

I never use RIGHT JOINs myself. Only INNER, LEFT & RIGHT supported in Jet though.

What is the correct work-around? This obviously isn't an uncommon scenario, needing to sum and count to update data, right? ;-)

The method I proposed it what I usually use.

Otherwise, I think most people simply use an intermediate (scratch) table to get around the limitations.

[NeoPa]

Do you mean by simply joining and letting the duplication do the work?
I suppose I can test that easily enough using a Select query to ensure that I'm not inadvertantly causing multiplication or other cartesian-product-like ill effects.

If I understand you correctly then, Yes - and it's wise to check for cartesian product side-effects.

I definitely am a fan of the explicit rather than the implicit by rule... but everyone has to step out of their comfort zone sometime, right?

Me too. I would recommend using the explicit even when venturing beyond comfort zones ;)

It can help minimise the damage when you do get lost.

[geolemon]

I never use RIGHT JOINs myself. Only INNER, LEFT & RIGHT supported in Jet though.

Oh, sorry -
by "right", I meant "correct"...
I was struggling with the corrrect join syntax, because I'm used to simply doing something like this:

SELECT * 
FROM TABLE1 
inner join TABLE2 on KEY1=KEY2
inner join TABLE3 on KEY1=KEY3
inner join TABLE4 on KEY3=KEY4

I'm amazed that Access apparently wants some sort of "join order" rather than letting the database query engine do that analysis - at least I imagine that's the function of the absolutely-impossible-to-read groupings of parenthesis that it wraps around everything (reference my code above - that was Access generated!). Yike!

[NeoPa]

I'd argue with you, but I don't like to be on the wrong side ;D

You're absolutely right of course, and I sympathise. Access can provide a decent and easy to get into front-end though. It does have quite a few benefits. Jet SQL isn't really up to grown-up RDBMS standards though.