CODEWITHSUNDEEP
sqlms-access
jcarroll
jcarroll

Reputation: 577

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

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.

Upvotes: 0

Views: 68

Answers (1)

xQbert
xQbert

Reputation: 35333

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)

Upvotes: 2

Related Questions