Update a table with a value field in ANOTHER table, but selecting unique value

[skbpillai]

Hi,

I have two tables (MatMast and ChangeLog) in an Access Database, where the common key is MatCode. The tables look like this:

MatMast
MatCode (PK), CurrentStatus
10001, ACTIVE
10002, INACTIVE
10003, ACTIVE

ChangeLog
ChangeInstance (PK), MatCode, Status, TimeStamp
25452, 10001, ACTIVE, 19-Dec-2012 11:21 AM
25678, 10001, INACTIVE, 20-Dec-2012 06:45 PM
20296, 10002, ACTIVE, 20-Dec-2012 03:52 PM

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!

Regds,
SKB

 

[plog]

You most likely shouldn't store this value in the MatMast table. Instead, you should calculate it in a query when you need it. This will require a sub query that looks like this:

SELECT MatCode, Max(TimeSTampe) As LastStatusEntry
FROM ChangeLOG
GROUP BY MatCode;

Name that query 'subMatMastStatus'. Then, create a query based on MatMast, subMatMastStatus and ChangeLog. Make a LEFT JOIN from MatMast to subMatMastStatus and a LEFT JOIN from subMatMastStatus to ChangeLog. Bring in the MatCode from MatMast and then make a conditional statement that shows 'Active' if the CurrentStatus field of ChangeLog is null and shows the CurrentStatus if it is not.

This query will be the one you use to get the status of every MatMast record--no updating anything required.