Access SQL Update One Table In Join Based on Value

I have a table in Access called "tempSpring_ASN", with the following fields (among others):

SHP_CUSTOM_5 (AutoNumber)

RECORD_TYPE (Text)

PO_NUM (Text).

I need to change the value of RECORD_TYPE, such that if PO_NUM is the same as PO_NUM in the previous record, then RECORD_TYPE should be "LIN", otherwise (or if it's the first record), RECORD_TYPE should be "HDR".

I've created the following query to get the correct new value for RECORD_TYPE:

SELECT TOP 1 t1.SHP_CUSTOM_5,
    t1.PO_NUM AS CurrentValue,
    NULL AS PreviousValue,
    "HDR" AS RECORD_TYPE
FROM tempSpring_ASN AS t1
ORDER BY t1.SHP_CUSTOM_5
UNION ALL
SELECT t1.SHP_CUSTOM_5,
    t1.PO_NUM AS CurrentValue,
    t2.PO_NUM AS PreviousValue,
    IIf([CurrentValue]=[PreviousValue],'LIN','HDR') AS RECORD_TYPE
FROM tempSpring_ASN AS t1,
    tempSpring_ASN AS t2
WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
ORDER BY t1.SHP_CUSTOM_5;

I've saved that query as "tempSpring_ASN_With_PreviousRow". Now I'm trying to use that to update the original tempSpring_ASN table with the following query:

UPDATE tempSpring_ASN INNER JOIN tempSpring_ASN_With_PreviousRow ON tempSpring_ASN.SHP_CUSTOM_5 = tempSpring_ASN_With_PreviousRow.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = [tempSpring_ASN_With_PreviousRow].[RECORD_TYPE];

but I'm getting, "Operation must use an updateable query." I'm not sure if it's because I'm trying to update one table in a join, or because I'm trying to update a table based on a value in that same table, or due to something else. Regardless, I'm looking for something which works.

Thanks!

Update (no pun intended):

I've tried the following update query:

UPDATE tempSpring_ASN INNER JOIN Table5 ON tempSpring_ASN.SHP_CUSTOM_5 = Table5.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = "zzz";

and it worked fine. The result was that tempSpring_ASN was updated, but Table5 wasn't. Apparently, if two tables are joined in a SQL statement, even if you run an update on one table, it still won't try to update the other table in the join. That being the case, I'm not sure why my original update query won't work. I know that tempSpring_ASN_With_PreviousRow isn't updateable, because it's a UNION query, but I'm not trying to update it. Rather I'm trying to update tempSpring_ASN--the other table in the join, which is updateable.

Update 2: Then I tried using a correlated subquery as follows:

UPDATE tempSpring_ASN AS t
SET t.RECORD_TYPE = (
        SELECT RECORD_TYPE
        FROM (
            SELECT TOP 1 t1.SHP_CUSTOM_5,
                t1.PO_NUM AS CurrentValue,
                NULL AS PreviousValue,
                "HDR" AS RECORD_TYPE
            FROM tempSpring_ASN AS t1
            ORDER BY t1.SHP_CUSTOM_5
            UNION ALL
            SELECT t1.SHP_CUSTOM_5,
                t1.PO_NUM AS CurrentValue,
                t2.PO_NUM AS PreviousValue,
                IIf([CurrentValue] = [PreviousValue], 'LIN', 'HDR') AS RECORD_TYPE
            FROM tempSpring_ASN AS t1,
                tempSpring_ASN AS t2
            WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
            ORDER BY t1.SHP_CUSTOM_5
            )
        WHERE SHP_CUSTOM_5 = t.SHP_CUSTOM_5
        );

but I'm still getting "Operation must use an updateable query."

Update 3:

I believe that the error is due to the fact that I'm using a union query. To isolate the problem, I've tried the following (which would NOT give me the result I want, but would help me diagnose the problem.):

UPDATE tempSpring_ASN AS t
SET t.RECORD_TYPE = (
        SELECT TOP 1 RECORD_TYPE
        FROM tempSpring_ASN_With_PreviousRow
        );

It gave me the same error. So the question now becomes, why can't I use a single value of an output of a union query to set a value in a recordset?

I have also posted this answer within your new question.

Hi AYS,

In Access, an Update query needs to be run on a table. As a UNION query is a combination of multiple sets of records, the result set is no longer a table, and cannot be the object of an Update query as the records in the result set are no longer uniquely identified with any one particular table (even if they theoretically could be). Access is hard-coded to treat every UNION query as read-only, which makes sense when there are multiple underlying tables. There are a number of other conditions (such as a sub-query in the SELECT statement) that also trigger this condition.

Think if it this way: if you were not using TOP 1 and your UNION query returned multiple results, how would JET know which result to apply to the unique record in your table? As such, JET treats all such cases the same.

Unfortunately, this is the case even when all of the data is being derived from the same table. In this case, it is likely that the JET optimizer is simply not smart enough to realize that this is the case and re-phrase the query in a manner that does not use UNION.

In this case, you can still get what you want by re-stating your query in such a way that everything references your base table. For example, you can use the following as a SELECT query to get the PO_NUM value of the previous SHP_CUSTOM_5 record:

SELECT
t1.SHP_CUSTOM_5
, t1.PO_NUM
, t1.SHP_CUSTOM_5 -1 AS PREV_RECORD

, (SELECT
t2.PO_NUM
FROM
tempSpring_ASN As t2
WHERE
t2.SHP_CUSTOM_5 = (t1.SHP_CUSTOM_5 -1)
) AS PREV_PO

FROM
tempSpring_ASN AS t1
;

You can then phrase this as an Update query as follows in order to perform the "LIN" updates:

UPDATE
tempSpring_ASN AS t1 

SET 
t1.RECORD_TYPE = "LIN"

WHERE
t1.PO_NUM=

(
SELECT 
t2.PO_NUM

FROM
tempSpring_ASN As t2

WHERE
t2.SHP_CUSTOM_5 = (t1.SHP_CUSTOM_5 -1)
)
;

This code was successful in the tests I ran with dummy data.

Regarding your "HDR" updates, your are really performing two separate updates. 1) If the PO_NUM matches the previous record's PO_NUM, set RECORD_TYPE to "LIN" 2) If it is the first record, set RECORD_TYPE to "HDR"

It is not clear to me why there would be a benefit to performing these actions within one query. I would recommend performing the HDR update using the "TOP 1" by SHP_CUSTOM_5 method you used in your original SELECT query example, as this will be a relatively simple UPDATE query. It is possible to use IIF() within an Update query, but I do not know what additional benefit you would gain from the additional time and complexity that would be required (it would most likely only be much less readable).

Best of luck!