Home > other >  Select only the most recent records by record ID
Select only the most recent records by record ID

Time:10-29

I have an access database, in it, I have a number of records where I need to pull just the latest information .

Here's the code I am trying to get working :

SELECT c.RecordID, c.Employee, c.DecShotDate, c.DecShotLocation, c.DecReason, c.DecExplanation, e.ID, e.Employee, c.Training, c.CompletedDate, IIf([DecShotLocation]=0,"Hospital", IIf([DecShotLocation]=1,"MCS", IIf([DecShotLocation]=2,"Other Employer",IIf([DecShotLocation]=3,"Pharmacy",IIf([DecShotLocation]=4,"Primary Care",IIf([DecShotLocation]=5,"Urgent Care"," ")))))) AS StrDecLoc
FROM ((EmployeeInformation AS e 
INNER JOIN CompletedTrainings AS c ON e.ID = c.Employee
) AS t
INNER JOIN
(
SELECT MAX(RecordID) AS mID, c.Training 
FROM CompletedTrainings c
)
ON t.RecordID = c.RecordID
INNER JOIN
(
SELECT DISTINCT c.Training, c.RecordID AS dID
FROM CompletedTrainings c
) AS d
ON c.RecordID = d.dID
GROUP BY c.Training
) as M
on M.RecordID = t.RecordID
WHERE c.Training = 69 AND (((c.CompletedDate 365)>=IIf(Forms![frm_Decflu]!FrameAllOrCurrent=1,(c.CompletedDate 365),Date())))
GROUP BY c.RecordID, c.Employee, c.DecShotDate, c.DecShotLocation, c.DecReason, c.DecExplanation, e.ID, e.Employee, c.Training, c.CompletedDate);

The idea here, is I need 1 Training, and I need just the employees who took that training, and only the last time they took it.

Currently I am getting a Syntax error in the JOIN statement.

So the two inner Selects:

(
SELECT MAX(RecordID) AS mID, c.Training 
FROM CompletedTrainings c
)
ON t.RecordID = c.RecordID
INNER JOIN
(
SELECT DISTINCT c.Training, c.RecordID AS dID
FROM CompletedTrainings c
) AS d
ON c.RecordID = d.dID
GROUP BY c.Training
) as M
on M.RecordID = t.RecordID

Work together to get me a list of the last time every training was taken. And the first select

SELECT c.RecordID, c.Employee, c.DecShotDate, c.DecShotLocation, c.DecReason, c.DecExplanation, e.ID, e.Employee, c.Training, c.CompletedDate, IIf([DecShotLocation]=0,"Hospital", IIf([DecShotLocation]=1,"MCS", IIf([DecShotLocation]=2,"Other Employer",IIf([DecShotLocation]=3,"Pharmacy",IIf([DecShotLocation]=4,"Primary Care",IIf([DecShotLocation]=5,"Urgent Care"," ")))))) AS StrDecLoc
FROM ((EmployeeInformation AS e 
INNER JOIN CompletedTrainings AS c ON e.ID = c.Employee

Pulls just the information I need from each of the tables.

Independently these work, except that the first select pulls duplicate trainings (These trainings are done yearly).

I am trying to get rid of that duplication, to produce a yearly report on who has had a specific training.

Sample Tables :

tbl_E

IDNum LastName GivenName Active PersonalInfoColumns EmployeeName
1 Dole Bob -1 stuff Bob Dole
2 Clinton Bill -1 stuff Bill Clinton
3 Bush George HW 0 stuff George HW Bush
4 Reagan Ronald 0 stuff Ronald Reagan
5 Eastwood Clint -1 stuff Clint Eastwood

tbl_C

RecordID Employee Training CompletedDate DSD DSL DR DE
1 1 69 mmddyyyy 0
2 2 74 mmddyyyy
3 3 69 mmddyyyy 1 somewhere 4 somereason
4 4 52 mmddyyyy
5 1 74 mmddyyyy
6 2 69 mmddyyyy 2 somewhere 4 somereason
7 5 69 mmddyyyy 0
... ... ... ... ... ... ... ...
972 1 69 mmddyyyy 1 somewhere 4 somereason
973 5 69 mmddyyyy 1 somewhere 2 somereason
974 1 73 mmddyyyy 1 somewhere 2 somereason
974 2 69 mmddyyyy 0

tbl_T (not relevant but it is linked to tbl_C)

ID TrainingName requalifiy
69 Shots yearly
73 Reference Once
74 CORI Once
52 Training yearly

The ideal output of this query is this :

RecordID Employee Training CompletedDate DSD DSL DR DE
3 3 69 mmddyyyy 1 somewhere 4 somereason
972 1 69 mmddyyyy 1 somewhere 4 somereason
973 5 69 mmddyyyy 1 somewhere 2 somereason
974 2 69 mmddyyyy 0

Assuming employee 3 didn't do it after the first time.

CodePudding user response:

access is a little bizarre in using SQL as standard, so this SQL that would perfectly work in any SQL backend might not in access. Unfortunately at this moment I don't have a chance to try:

SELECT t1.* 
FROM tbl_C t1 
INNER JOIN
  (SELECT Max(recordId) AS maxRecId 
   FROM tbl_C WHERE Training=69 GROUP BY Employee) t2
ON t1.RecordId = t2.maxRecId;

(If you want, you can join to other such as tbl_E to get names)

  • Related