Microsoft Access SQL - Select Most Recent "Version" of Record

Question

I'm working on a select query that selects the list of approvers for a given record based on the foreign key EFTRecIDNum. The list of approvers are stored in table tblApprover, with relevant fields EFTRecIDNum, Approver, ApprovalTime, and ApproverAction. The challenge is that I need all records for a given EFTRecIDNum expect where an approver has done the same action more then once (which does have a business purpose). In that case I would need to select the newest record. My data looks like this: +------+-------------+----------+--------------+----------------+ | ID | EFTRecIDNum | Approver | ApprovalTime | ApproverAction | +------+-------------+----------+--------------+----------------+ | 503 | 111 | Person A | 09-Jun-16 | Reviewed | | 865 | 111 | Person B | 10-Jun-16 | Reviewed | | 1517 | 111 | Person C | 11-Jun-16 | Reviewed | | 1610 | 111 | Person C | 12-Jun-16 | Reviewed | | 1743 | 111 | Person D | 13-Jun-16 | Approved | | 1744 | 111 | Person C | 14-Jun-16 | Approved | +------+-------------+----------+--------------+----------------+ Where I need to select everything but ID number 1517.

I've tried a few different things with joins and subselects but always end up only selecting the most recent reviewer/approver.

SELECT
    a.EFTRecIDNum,
    a.Approver, 
    a.ApprovalTime, 
    a.ApproverAction
FROM tblApprover a INNER JOIN 
      (SELECT ApproverAction, MAX(ApprovalTime) as sTime
      FROM tblApprover GROUP BY ApproverAction) b 
on a.ApproverAction=b.ApproverAction and a.ApprovalTime = b.sTime

Please let me know if there is anything I can add or clarify.

EDIT: Added another line of data to clarify.

Why are you joining on just approverAction instead of EFTREcIDNum with approval Time and approver? (select EFTRecIDNum, Approver, ApproverAction max(approvalTime) sTime from tblApprover GROUP BY EFTRecIDNum, Approver, ApproverAction) b ON ... — xQbert
So if ID 1744 for EFTRecIDNum 111 for Person C with approval time 14-Jun-16 with action of Approved existed as well you'd see two records for person C ID 1610 and 1744 right? I'm trying to clarify: I need all records for a given EFTRecIDNum expect where an approver has done the same action more then once as that example isn't in sample data. — xQbert

xQbert xQbert · Accepted Answer · 2017-05-23T14:27:22

Seems to me your join is incomplete you need the other fields...

First we generate a set of records with the highest ID for a ApproverAction, approver and EFTRecIDNum (B). We then join this base set to our entire set (A) on the relevant fields. If time can be identical we can't use time as the max key as this would cause duplication the results, and ID would have to be used; if we can assume you want the max ID (and now I'm assuming ID is unique)

SELECT
    a.EFTRecIDNum,
    a.Approver, 
    a.ApprovalTime, 
    a.ApproverAction
FROM tblApprover a 
INNER JOIN (SELECT ApproverAction, MAX(ApprovalTime) as sTime, Approver, EFTRecIDNum
            FROM tblApprover 
            GROUP BY ApproverAction, Approver, EFTRecIDNum) b 
 on a.ApproverAction = b.ApproverAction 
and a.ApprovalTime = b.sTime
and a.approver = b.approver
and a.eftrecIDNum = b.EFTRecIDNum

Maybe time has duplicates so we may need to key off ID?

SELECT
    a.EFTRecIDNum,
    a.Approver, 
    a.ApprovalTime, 
    a.ApproverAction
FROM tblApprover a 
INNER JOIN (SELECT ApproverAction, MAX(ID) as mID, Approver, EFTRecIDNum
            FROM tblApprover 
            GROUP BY ApproverAction, Approver, EFTRecIDNum) b 
 on a.ApproverAction = b.ApproverAction 
and a.ID = b.mid
and a.approver = b.approver
and a.eftrecIDNum = b.EFTRecIDNum

The only difference between the two queries is what we use to define a "UNIQUE" record in terms of (approver, ApprovalAction, and EFTRecIDNum) If ApprovalTime can be duplicated for a given "Unique" then Approvaltime not unique enough.

Usually ID's are UNIQUE and in Access sequential (if auto number) so I assumed we could key off it as a unique value Since you only wanted 1 record per approveraction, approver, and EFTRecIDNum, we simply had the system pick the one with the highest ID. Then by joining this subset back to the all of the data, we were able to eliminate all but the highest ID per combination of EFTRecIDNum, Approver, and ApproverAction. This allowed us to find the one with the highest time. (and potentially other data if needed)

Microsoft Access SQL - Select Most Recent "Version" of Record

1 Answers