title: Update a table with a value field in ANOTHER table, but selecting unique value from second based on timestamp
body: <p>I have two tables (MatMast and ChangeLog) in an Access Database, where the common key is MatCode.  The tables look like this:</p>
  
  <p><strong>MatMast</strong></p>
  
  <p>MatCode (PK), CurrentStatus</p>
  
  <p>10001, ACTIVE</p>
  
  <p>10002, INACTIVE</p>
  
  <p>10003, ACTIVE</p>
  
  <p><strong>ChangeLog</strong></p>
  
  <p>ChangeInstance (PK), MatCode, Status, TimeStamp</p>
  
  <p>25452, 10001, ACTIVE, 19-Dec-2012 11:21 AM</p>
  
  <p>25678, 10001, INACTIVE, 20-Dec-2012 06:45 PM</p>
  
  <p>20296, 10002, ACTIVE, 20-Dec-2012 03:52 PM</p>
  
  <p>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:</p>
  
  <p><strong>MatMast (After <em>proposed</em> update query)</strong></p>
  
  <p>MatCode (PK), CurrentStatus</p>
  
  <p>10001, INACTIVE     &lt;== Picking up ChangeInstance 25678, which is the latest for 10001</p>
  
  <p>10002, ACTIVE       &lt;== Picking up ChangeInstance 20296, which is the ONLY rec for 10002</p>
  
  <p>10003, ACTIVE       &lt;== Unchanged, as there are no records for this in ChangeLog</p>
  
  <p>Tried various approaches.  But, I am not able to write an update query to do this.  Please, Please help!</p>
  
  <p>Regds,
  SKB</p>