Select only the most recent records by record ID-CodePudding

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)