Many to Many Max Query, updateable

[rawlstonb]

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.

[pbaldy]

This is more of a workaround than an answer, as I doubt you'll get that to be update-able without jumping through hoops that probably make it a performance dog. I'd probably present that data in a form with a button that sent your email and updated the underlying data as appropriate to your needs, then re-queried the form.

[rawlstonb]

This is more of a workaround than an answer, as I doubt you'll get that to be update-able without jumping through hoops that probably make it a performance dog. I'd probably present that data in a form with a button that sent your email and updated the underlying data as appropriate to your needs, then re-queried the form.

tbf I could not care less if it's a performance dog... they are going to update this database once/month... if there's a solution that works, I'm open ears... if you need the db file lmk... it's also plausible to split this into an update query that doesn't have inner joins, as tblContinuity has all the information and update marks needed... but at the same time, the email query needs some cleaning up and I'm not sure where I went wrong there... logically it makes sense but it's not outputting how it should

[pbaldy]

To be honest I'm not sure if it can be made editable, but I was allowing for the possibility. It would likely involve changing the Max() functions to DMax() functions and not grouping, but again I'm not sure since you're also filtering on a max. My eyes glazed over at some point.

Posting the db would certainly let somebody play with it and see what they could do.

[rawlstonb]

posted for people to play with... was attempting to get all the data into one query but it's not mandatory for functionality at all... was playing with some other options

login with brawlston / pw master123... reset pw to master123 and it'll stay default

[rawlstonb]

So I ended up splitting the query into three queries...

1) Searches for the latest record per welder per process to Warn about that is unmarked. This selects the specific record send the to e-mail about.

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, tblWelder.WelderKey, tblProcess.ProcessKey
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));



2) Searches for all records prior to and including the latest that are being flagged by the warning. This selects the previous record an all priors so older records don't re-flag. This might only really be relevant after initial data entry, but I thought it was worth nipping in the bud.

SELECT DISTINCTROW [FName] & " " & [LName] AS Welder, tblWelder.Active, tblProcess.Process, Max(tblContinuity.WeldDate) AS MaxOfWeldDate, DateAdd("m",4,[WeldDate]) AS Warning, tblContinuity.WarningGiven, tblWelder.WelderKey, tblProcess.ProcessKey
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, DateAdd("m",4,[WeldDate]), tblContinuity.WarningGiven, tblWelder.WelderKey, tblProcess.ProcessKey
HAVING (((tblWelder.Active)=Yes) AND ((DateAdd("m",4,[WeldDate]))<=Date()) AND ((tblContinuity.WarningGiven)=No));


3) Update query linked to tblContinuity on MaxOfWeldDate to check the WarningGiven box. This bypasses the the query that isn't updateable do to aggregate functions to update the flag box.

UPDATE DISTINCTROW qryWeldWarn INNER JOIN tblContinuity ON qryWeldWarn.MaxOfWeldDate = tblContinuity.WeldDate SET tblContinuity.WarningGiven = Yes;

I doubt that's the most succinct way of doing it, but it works and outputs correctly. I figured I'd post my solution for future search results if anybody came across the thread.

[Bulzie]

Not sure if your email process is automated (adding new processes and welders would be manual in another database). Just another thought, could you create a query of all welders, processes and max date so you only get 1 record per welder/process. This is your input table

Once a day, autorun this process. Write some code to loop through that table each day checking the dates for each welder/process and if it is time for a reminder, send a warning email. You can add EmailSentDate to the table and fill that in when you send a warning email. When the code runs, if that EmailSentDate is blank, then send warning if required, if a date is there, check to see if you want to send another email (say if you want to send another critical warning after another 2 weeks).

To automate, add a job to the task scheduler on server/PC to open a specific Access database. Have the database open a Form when the database starts. Put your code in the OpenForm Event so it kicks off when the form opens. At end of code put in the Quit command to close the database.