Solved Need help for a non-updatable query

[KitaYama]

I have this simple select query :

SELECT
    O.OrderNo,
    O.OrderProductFK,
    O.Plating,
    PP.ProcessFK
FROM
    tblOrders AS O
          INNER JOIN (tblProductsProcesses AS PP
          INNER JOIN tblProducts AS P ON PP.ProductFK = P.ProductPK) ON O.OrderProductFK = P.ProductPK
WHERE
    PP.ProcessFK In (15,16,17,23) AND (O.DeletedOn Is Null)

This query is non-Updatable.
If I pull out tblProductsProcesses, the query is updatable, but the purpose of the query is to update O.Plating, based on PP.ProcessFK.

I tried several methods, used some kind of sub queries (based on my limited knowledge), but in any case the result was a non-updatable query.
I need to update the fields in tblOrders .
Can anyone do some magic and shed a light here?

Adding several DLookup fields to pull data from tblProdductsProcess, solves the problem, But I'm looking for some more efficient ways.

Thanks.

 

[theDBguy]

You said a subquery didn't work? Can you show us that version?

 

[KitaYama]

You said a subquery didn't work? Can you show us that version?

SELECT
    O.Rec_ID,
    O.OrderProductFK,
    O.Plating,
    (SELECT PP.ProcessFK
        FROM
            tblProductsProcesses AS PP
        WHERE
            PP.ProductFK = O.OrderProductFK
           AND PP.ProcessFK IN (15,16,17,23)
     ) AS ProcessFK
FROM
    tblOrders AS O
WHERE
    O.DeletedOn IS NULL;

Thanks for trying.

 

[KitaYama]

This one with a different join :

SELECT
    O.Rec_ID,
    O.OrderProductFK,
    O.Plating,
    PP.ProcessFK
FROM
    (tblOrders AS O
        INNER JOIN tblProducts AS P ON O.OrderProductFK = P.ProductPK)
        INNER JOIN tblProductsProcesses AS PP ON P.ProductPK = PP.ProductFK
WHERE
    PP.ProcessFK IN (15,16,17,23)
    AND O.DeletedOn IS NULL;

 

[The_Doc_Man]

Is the JOIN between tblProductsProcesses and tblOrders a 1:1 link or 1:many? A very common thing to foul an INNER JOIN is if more than one record from one of the JOINing tables contributes to the join, thus leading to ambiguity in the write-back step. Using your aliases, the PP.ProcessFK field comes from a table that, based on what I'm seeing from the IN () clause, probably has multiple values - i.e. more specifically the JOINs involving multiple possible JOINing records from the PP table. Hope that made sense.

 

[KitaYama]

Is the JOIN between tblProductsProcesses and tblOrders a 1:1 link or 1:many?

There is no join between tblProductsProcesses and tblOrders.
tblProducts is in the middle. At one side tblProductsProcessses, at the other side tblOrders.
tblOrders (Many)--->(1)tblProducts(1)<---(Many)tblProductsProcesses

Thanks for trying to help

 

[CJ_London]

Have you tried changing the recordset to from dynaset to dynaset inconsistent updates?

 

[Pat Hartman]

Since there are multiple processes for a product, exactly which process record should update the order table? Why would a field from the many-side be used to update a field on the one-side of the relationship?

 

[ebs17]

but the purpose of the query is to update O.Plating, based on PP.ProcessFK

How exactly should the update be done, manually in the list or via procedure/query? In the second case at least, you can remove PP.ProcessFK from the field list and thus also change the query.

In general, I only update tables where this one table and its fields are in the SELECT part of the main query. The other tables and their links would then have to be moved to the WHERE part. This means that you only have to consider the requirements of the configured referential integrity.

SELECT
   O.OrderNo,
   O.OrderProductFK,
   O.Plating
   [, DLookupX(...) AS ProcessFK]
FROM
   tblOrders AS O
WHERE
   O.OrderProductFK IN
      (
         SELECT
            P.ProductPK
         FROM
            tblProducts AS P
               INNER JOIN tblProductsProcesses AS PP
               ON P.ProductPK = PP.ProductFK
         WHERE
            PP.ProcessFK IN(15, 16, 17, 23)
      )
   AND
   O.DeletedOn Is Null

The optional DLookupX should be a separate function that determines the key for display. It is better to do without it. Using a subquery will fail in Jet SQL.

Second way: If you want to see a lot and change everything by hand, you can also proceed as follows:
1) Create a temporary table from the query content using a make table query
2) Make the changes in the table.
3) Transfer the changes from the temporary table to the original tables involved. In the maximum case, this would be one append query and one update query per table, or 6 queries for three tables to transfer the information back, but in the case described here, only one update query on tblOrders.

 

[KitaYama]

I'm terribly sorry for being late.

@CJ_London To be honest, I have to do some research on this. It's a good opportunity to learn something new.
@Pat Hartman I wrote a long reply, with images and showing what was going on, but it was getting too complicated to explain. So I deleted it. In short, I'm not using the field on many side to edit the field on one side of the same join. The tables belong to two different joins. See #6. I'm trying to use a field on a many side of a join, to edit another field in many side of another join. There's a Product table between the two tables.

@ebs17 You're a real life saver. I really appreciate your help. That section (explaining moving the tables to WHERE, opened a new door to have a better understanding on how things work.

Thanks to all who helped.
I think I can sleep tonight in piece.

Thanks again.

 

[CJ_London]

bit of reading for you:

RecordsetType Property - Microsoft Support

support.microsoft.com

https://www.reddit.com/r/MSAccess/comments/8623jq/recordset_type_dynaset_inconsistent_updates

note that indexing may have an impact - not all queries can be edited by making the dynaset inconsistent