如何避免这种Access SQL冲突? [英] 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 the assignment picked being arbitrary.

当前方法:

qCurrentAssignment_sub:

qCurrentAssignment_sub:

SELECT tblAssignment.EligibilityID, Max(DateAdd("s",[AssignmentID] Mod 10000,[AssignmentDate])) AS DatePlusIDMod10000
FROM tblAssignment
GROUP BY tblAssignment.EligibilityID;

qCurrentAssignment:

qCurrentAssignment:

SELECT tblAssignment.AssignedTo, tblAssignment.AssignedBy, tblAssignment.Method, tblAssignment.AssignmentDate
FROM qCurrentAssignment_sub INNER JOIN tblAssignment ON 
    (qCurrentAssignment_sub.EligibilityID = tblAssignment.EligibilityID) AND 
    (qCurrentAssignment_sub.DatePlusIDMod10000 = DateAdd("s",[AssignmentID] Mod 10000,[AssignmentDate]));

推荐答案

评论已更新

如果给出

• 每个工作分配ID都有多个工作分配
• 您要让assignid与最大的assignmentDate相关联
• 如果max(assignmentDate)存在一个以上的assignmentid，那么您想要最大的assignid

将qCurrentAssignment_sub更改为

Change qCurrentAssignment_sub to

qCurrentAssignment_sub

qCurrentAssignment_sub

  SELECT
        eligibilityid,
        MAX(assignmentid) assignmentid

    FROM
        tblassignment 
        INNER JOIN 
        (SELECT tblassignment.eligibilityid, 
               MAX(AssignmentDate) AssignmentDate 
        FROM   tblassignment 
        GROUP  BY tblassignment.eligibilityid) maxAssignmentDate
        ON tblassignment.eligibilityid = maxAssignmentDate.eligibilityid
           and maxAssignmentDate.AssignmentDate = maxAssignmentDate.AssignmentDate
    GROUP BY 
        eligibilityid

然后在assignmentid上重新加入到tblassignment.这也使连接更加干净

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

qCurrentAssignment

qCurrentAssignment

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

这篇关于如何避免这种Access SQL冲突?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！