Filtering only recent records

[DubboPete]

Hi all,

Irun a query to build a temporary table of staff who have completed a
training course in the last 12 months. Some people have done this
course twice in that period. Can someone advise how to alter the
code below so that is only shows the most recent date entry per
person?

code:

SELECT TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME], TblTraining.COURSECODE,
TblTraining.SERVICE_UNIT, TblTraining.COURSE, TblTraining.COURSEDATE,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster INTO TblTempTraining
FROM TblTraining
WHERE (((TblTraining.COURSECODE)=[forms]![frmEdreports]![Combo10]));

Many thanks in anticipation

DubboPete

 

[Marshall Barton]

Irun a query to build a temporary table of staff who have completed a
training course in the last 12 months. Some people have done this
course twice in that period. Can someone advise how to alter the
code below so that is only shows the most recent date entry per
person?

SELECT TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME], TblTraining.COURSECODE,
TblTraining.SERVICE_UNIT, TblTraining.COURSE, TblTraining.COURSEDATE,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster INTO TblTempTraining
FROM TblTraining
WHERE (((TblTraining.COURSECODE)=[forms]![frmEdreports]![Combo10]));

Change the WHERE clause:

WHERE COURSECODE=Forms!frmEdreports!Combo10
AND COURSEDATE =
(SELECT Max(X.COURSEDATE)
FROM TblTraining As X
WHERE X.COURSECODE = TblTraining.COURSECODE
AND X.EmployeeNumber = TblTraining.EmployeeNumber)

 

[DubboPete]

Hi Marsh

Worked a treat - many thanks mate!

Pete

 

[John Spencer]

An alternative query that may do the same thing

SELECT TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME],
TblTraining.COURSECODE, TblTraining.SERVICE_UNIT,
TblTraining.COURSE
, Max(TblTraining.COURSEDATE) as CourseDate,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster INTO TblTempTraining
FROM TblTraining
WHERE (((TblTraining.COURSECODE)=[forms]![frmEdreports]![Combo10]));
GROUP BY TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME], TblTraining.COURSECODE,
TblTraining.SERVICE_UNIT, TblTraining.COURSE,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[DubboPete]

Thanks John, will try that too and hold it in archive

Pete

An alternative query that may do the same thing

SELECT TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME],
TblTraining.COURSECODE, TblTraining.SERVICE_UNIT,
TblTraining.COURSE
, Max(TblTraining.COURSEDATE) as CourseDate,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster INTO TblTempTraining
FROM TblTraining
WHERE (((TblTraining.COURSECODE)=[forms]![frmEdreports]![Combo10]));
GROUP BY TblTraining.ID, TblTraining.EmployeeNumber,
TblTraining.SURNAME, TblTraining.[FIRST NAME], TblTraining.COURSECODE,
TblTraining.SERVICE_UNIT, TblTraining.COURSE,
TblTraining.STATUS, TblTraining.HOURS, TblTraining.TRAINER,
TblTraining.CostCentre, TblTraining.Cluster

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi Marsh

Worked a treat - many thanks mate!

Pete