Update a row by another row within one table

Question

I am using Access 2016 and I have a table with some data missing. I need to Update the missing data based on other rows of the same table. The table has two Identifyer and two Criteria fields and the Null fields represent the missing data. Fortunately, the data is only missing in the Column Criteria2 and when Criteria1 is not NULL:

Identifyer1  Identifyer2  Criteria1  Criteria2  
  10             a                     A3        
  10             a           X        NULL     
  20             b                     B3     
  30             c                     C3
  40             d                     D3
  40             d           Y        NULL

To fill in the missing data it is sufficient to copy the Criteria2 values of the same Identifyers. So the result should look like this:

Identifyer1  Identifyer2  Criteria1  Criteria2  
  10             a                     A3        
  10             a           X         A3     
  20             b                     B3     
  30             c                     C3
  40             d                     D3
  40             d           Y         D3

I have tried something like this, but I can't complete the Code:

Update table1 Set Criteria2 = (How to copy values here?) Where Criteria1 is not NULL AND Identifyer1 = Identifyer1 AND Identifyer2 = Identifyer2

Answer 1

Since update queries must be updateable in MS Access, consider the domain aggregate, DMax, which corresponds to a correlated subquery:

UPDATE table1 t1 
SET Criteria2 = DMax("Criteria2", "table1", 
                     "Criteria1 IS NOT NULL AND Identifyer1 ='" & t1.Identifyer1 & "' 
                      AND Identifyer2 = '" &  t1.Identifyer2 & "'")