My job now is to write an SQL Query in Access (not allowed to use VBA for this) to update the MatMast table into this:
MatMast (After proposed update query)
MatCode (PK), CurrentStatus
10001, INACTIVE <== Picking up ChangeInstance 25678, which is the latest for 10001
10002, ACTIVE <== Picking up ChangeInstance 20296, which is the ONLY rec for 10002
10003, ACTIVE <== Unchanged, as there are no records for this in ChangeLog
Tried various approaches. But, I am not able to write an update query to do this. Please, Please help!
Write a Select query joining the two tables at MatCode. Include all fields from ChangeLog and CurrentStatus from MatMast. This should show the results you want. Also, any time you change the status in the MatMast table, the query will reflect this for all entries.