访问SQL查询:查找每个培训课程中每个员工的最新日期条目 [英] Access SQL Query: Find the most recent date entry for each employee for each training course

问题描述

我敢肯定这很简单，但是对于访问特别是SQL来说，这是一个新手，基本上，我在一家安全公司工作，我需要创建一个查询，该查询仅针对每位员工提供每门课程的最新培训.

Im sure this is pretty simple to do but im a complete novice at access especially SQL, basically I work for a safety company and i need to create a query which brings up only the most recent training for each course for each employee.

我有一个可以访问的表，如下所示:

I have a table in access which looks like this:

员工ID ...课程ID ...课程日期...有效日期
.... 12 ...................... 2 ................. 01 /02/2013.....01/02/2014
.... 13 ................... 1 ................. 20 2012年9月9日.... 2012年20月20日
.... 12 ...................... 2 ................. 01 /02/2012.....01/02/2013
.... 12 ................................................................... 15 /06/2013.....15/06/2014
.... 13 ................................................................... 15 /06/2013.....15/06/2014
.... 13 ...................... 1 ................. 19 /09/2011 ..... 19/09/2012

Employee ID... Course ID....Course Date....Valid To date
....12......................2.................01/02/2013.....01/02/2014
....13......................1.................20/09/2012.....20/09/2012
....12......................2.................01/02/2012.....01/02/2013
....12......................3.................15/06/2013.....15/06/2014
....13......................2.................15/06/2013.....15/06/2014
....13......................1.................19/09/2011.....19/09/2012

(很抱歉，句号在HTML上也不是很好)
想要获得的ID是仅列出每位员工每次培训的最新列表，因此如下所示:

(Sorry about the fullstops im not great at HTML either)
What id like to achive is a list of only the most recent of each training per employee, so something that will look like this:

员工ID ...课程ID ...课程日期...有效日期
.... 12 ...................... 2 ................. 01 /02/2013.....01/02/2014
.... 13 ................... 1 ................. 20 2012年9月9日.... 2012年20月20日
.... 12 ................................................................... 15 /06/2013.....15/06/2014
.... 13 ................................................................... 15 /06/2013 ..... 15/06/2014

Employee ID... Course ID....Course Date....Valid To date
....12......................2.................01/02/2013.....01/02/2014
....13......................1.................20/09/2012.....20/09/2012
....12......................3.................15/06/2013.....15/06/2014
....13......................2.................15/06/2013.....15/06/2014

我的桌子上大约有10,000条记录，所以如果我能得到一些帮助的话，它会给我带来很多帮助，我已经尝试解决了好几个星期了！

Theres about 10,000 records on my table so it would help a hell of a lot if I could get some help, ive been trying to solve this for weeks now!

谢谢！

推荐答案

以下查询应返回您想要的结果:

The following query should return the result you desire:

SELECT t1.*
FROM 
    Training t1 
    INNER JOIN 
    (
        SELECT [Employee ID], [Course ID], MAX([Course Date]) AS MaxDate 
        FROM Training 
        GROUP BY [Employee ID], [Course ID]
    ) t2 
        ON t1.[Employee ID]=t2.[Employee ID] 
            AND t1.[Course ID]=t2.[Course ID] 
            AND t1.[Course Date]=t2.MaxDate

这篇关于访问SQL查询:查找每个培训课程中每个员工的最新日期条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！