So I have Welders, Processes, and Dates
Welders perform a Process for certification on X date and they need to keep their records up to date before their certifications expire. I've got an e-mail set up to warn that certifications are going to expire within 2 months. After the warning, I need to update the continuity table so it doesn't keep sending warnings after they expire.
There's ~ 15 welders, 4 possible processes, and I want to pull the latest date for each welder for each process to check if it's still valid.
So let's say Brian is certified in 2 of the 4 processes... FCAW and SMAW, but his SMAW is coming up on needing a refresh. I need to send an email to remind his supervisor to schedule that. He's also got older records in both, but those are irrelevant for this task. I only want to pull the most up to date record for each of his qualified processes...
SELECT DISTINCTROW [FName] & " " & [LName] AS Welder, tblWelder.Active, tblProcess.Process, Max(tblContinuity.WeldDate) AS MaxOfWeldDate, Max(DateAdd("m",4,[WeldDate])) AS Warning, Max(DateDiff("d",Date(),[WeldDate]+180)) AS Expiration, tblContinuity.WarningGiven
FROM tblProcess INNER JOIN (tblWelder INNER JOIN tblContinuity ON tblWelder.WelderKey = tblContinuity.Welder) ON tblProcess.ProcessKey = tblContinuity.Process
GROUP BY [FName] & " " & [LName], tblWelder.Active, tblProcess.Process, tblContinuity.WarningGiven, tblWelder.WelderKey, tblProcess.ProcessKey
HAVING (((tblWelder.Active)=Yes) AND ((Max(DateAdd("m",4,[WeldDate])))<=Date()) AND ((tblContinuity.WarningGiven)=No));
This is close to what I need but not really... and it's not update-able as it has aggregate functions. I would also like to check a box and update tblContinuity.WarningGiven after I send the email. I know I'm doing this wrong and any help is appreciated.
Thanks.