Update Field based on existing record fields

[gammaman]

I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.

select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"

If it is true that I cannot do an Update Select then I am trying to do something in VBA.

How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.

 

[rzw0wr]

I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.

select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"

If it is true that I cannot do an Update Select then I am trying to do something in VBA.

How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.

I am confused here.
If this data is in a query then, unless it is calculated data, it is already stored.

I don't think we are getting the entire picture.

EDIT:
After re-reading this, it looks like your tables need to be normalized. If you are want to store the data from 1 table to another table this is against normalization rules and should be avoided.

Dale