How to retreive the latest (most recent) date on a series of entries of a subtable

[Francine]

Hello,

I created a database to track all the employees vaccines. I need a report &
query of course of who needs a booster (ex: a vaccine that needs to be
renewed after 10 years). My problem is that the system gives me all the
dates the employee had that vaccine but I need the most recent one and do my
calculation on that one only. Is there a way to get the query to look for a
certain vaccine then look at all the dates for each employees, keep the
latest date and do the calculation so that the report will only give me the
list of all employees who needs a booster shot?

My main table is called Ts-Data (info on employee) and a relationship with
Tss-Vaccines (vaccine date and name) is made on the employee's number.

Here is the SQL of the query I am working on, but of course it sometimes
gives me more than one date per employees:

SQL
SELECT DISTINCTROW [TS-DATA].[Last Name], [TS-DATA].[First Name],
[TS-DATA].[Date of Birth],
[TS-DATA].[No Employee], [TSS-VACCINES].[Date of vaccine],
[TSS-VACCINES].[Vaccine Name],
Format([date of vaccine],"yyyy") AS VacYear,
IIf([datejour]-[vacyear]>"10","oui","non") AS
Booster, Format(Now(),"yyyy") AS DateJour, [TSS-VACCINES].[Vaccine
Name], [TSS-VACCINES].[Date of
vaccine] AS Myfinfo
FROM [TS-DATA] INNER JOIN [TSS-VACCINES] ON [TS-DATA].[No Employee]
= [TSS-VACCINES].[No Employee]
WHERE ((([TSS-VACCINES].[Vaccine Name]) Like "d2*"));

Thanks for your help!

 

[Howard Brody]

You need to GroupBy both [No Employee] and [Vaccine Name]
and use Max([date of vaccine]).

I would do it in two queries: The first to pull the
latest date of each vaccine for each employee and the
second to pull those where it's been more than 10 years:

Query1:
SELECT [TS-DATA].[No Employee], [TSS-VACCINES].[Vaccine
Name], MAX([TSS-VACCINES].[Date of vaccine]) AS
LastVaccine
FROM [TS-DATA] INNER JOIN [TSS-VACCINES] ON [TS-DATA].[No
Employee] = [TSS-VACCINES].[No Employee]
GROUP BY [TS-DATA].[No Employee], [TSS-VACCINES].[Vaccine
Name];

Query2:
SELECT [Query1].[No Employee], [TS-DATA].[Last Name], [TS-
DATA].[First Name], [TS-DATA].[Date of Birth], [Query1].
[Vaccine Name], [Query1].[LastVaccine]
FROM [Query1] INNER JOIN [TS-DATA] ON [Query1].[No
Employee] = [TS-DATA].[No Employee]
WHERE DateSerial(Year([Query1].[LastVaccine])+10, Month
([Query1].[LastVaccine]),Day([Query1].[LastVaccine])) <
Date();

Hope this helps!

Howard Brody