Updating a Column Value Based on other Columns in the table

[Userdd]

how to update a column based on the values of other columns?
IdNo RevId MinorRevId Status
789 A a ?
789 B b ?
890 0 a ?
890 0 b ?
890 1 b ?

status is the column used to find the latest Revision id, hence the rule is
for each Id,
Check RevId higher value ( Either B from A,B series or 1 from 0,1 series )* value may be in A,B... series or in 0,1... series
and
Check MinorRevId higher Value ( b from a,b)
and update the status to "Y" for tht record alone.
and for the other records need to updated as "N".
hence the above table will be
IdNo RevId MinorRevId Status


789 A a N
789 B b Y
890 0 a N
890 0 b N
890 1 b Y
For example in case a new entry comes,
IdNo RevId MinorRevId Status
789 A a N
789 B b N
789 B c Y
890 0 a N
890 0 b N
890 1 b Y
the contents should be modified as shown above..

Can someone tell me how to achieve this..
Thank you for your time!!

[June7]

Believe this will require VBA code. Why do you need to do this? A query can be designed to pull records where field value is equal to the Max.

[Userdd]

hi...

while the user inputs data to this table, he wont fill this column, it has to be updated by the database on its own based on the criteria as mentioned above.This is basically to track the latest record....
Later in the report, the records with the status "Y" will be filtered..

can u pls share some sample code... so tht it wud be helpful for me to proceed..

thank you for your time n effort!!!

[June7]

Querying on the Status field does appear easier but getting that field populated is complex.

Updating the Status field could probably be done with a series of Access UPDATE queries. However, if a query could properly update the fields based on the maximum criteria then a query could simply retrieve the records with this same criteria. Example:
SELECT * FROM Table1
WHERE RevId=DMax("RevId","Table1","IdNo='" & [IdNo] & "'") AND MinorRevId=DMax("MinorRevId","Table1","IdNo='" & [IdNo] & "'");

Therefore, no reason to continually update the Status field. However, if you really must, then use this query as basis for an Update to set Status field to Y for these records. Then build another Select query that retrieves all records that don't meet the above criteria and use that one as basis for an Update to set Status field to N for those records.

These queries can be Access saved objects and then run manually or with VBA code in some event such as a button click. So somebody has to remember to do this Update when needed or run them as part of some other process, such as producing a report.