Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012

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


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

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

    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!


  2. #2
    Join Date
    Oct 2009
    A better approach:

    Remove CurrentStatus from your ChangeLog table.

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts