Update the query in Access that sets the equal status value for all records with the same ID

advertisements

I am struggling to write an update query in Access that sets the Status value equal for all record with the same ID if Status contains a value.

The Table1 loooks like this now :

ID       Status
1
1          OK
2
2          OK
3
3

Wanted result is :

ID               Status
1                  OK
1                  OK
2                  OK
2                  OK
3
3


Access UPDATE queries can be finicky. One which uses GROUP BY in a subquery will be treated as "not updatable". You can use the DMax() domain aggregate function to avoid that complaint ...

UPDATE tblOlaOkland AS y
SET y.Status = DMax('[Status]','[YourTable]','[ID]=' & y.ID)
WHERE
        y.Status Is Null
    AND DMax('[Status]','[YourTable]','[ID]=' & y.ID) Is Not Null;

Replace YourTable with the name of your table in both places.