How Can I Avoid This Access Sql Kludge?

The goal is to return the info from the latest assignment for each 'eligibility'. If there are two assignments with the same date, still only return one row per 'eligibility' with

Solution 1:

Updated From Comment

If given

• multiple assignmentid for each assignmentid
• you want the assignmentid associated with the max assignmentDate
• If more than one assignmentid exist for the max(assignmentDate) then you want the max assignmentid

Change qCurrentAssignment_sub to

qCurrentAssignment_sub

SELECT
        eligibilityid,
        MAX(assignmentid) assignmentid

    FROM
        tblassignment 
        INNERJOIN 
        (SELECT tblassignment.eligibilityid, 
               MAX(AssignmentDate) AssignmentDate 
        FROM   tblassignment 
        GROUPBY tblassignment.eligibilityid) maxAssignmentDate
        ON tblassignment.eligibilityid = maxAssignmentDate.eligibilityid
           and maxAssignmentDate.AssignmentDate = maxAssignmentDate.AssignmentDate
    GROUPBY 
        eligibilityid

Then join back to tblassignment on assignmentid. This makes the join a little cleaner as well

qCurrentAssignment

SELECT tblassignment.assignedto, 
       tblassignment.assignedby, 
       tblassignment.method, 
       tblassignment.assignmentdate 
FROM   tblassignment 
       INNERJOIN qcurrentassignment_sub 
         ON tblassignment.assignmentid = qcurrentassignment_sub.assignmentid