I am just a beginner and not a good one at that but I keep trying. I have never posted but this one is kicking my butt. It seems easy but I am stumped. I can usually find my way around just by reading this forum and applying it to my own project. Some of you may even see some of your work in this. I am about burned out on this project. It really seems like this would be easy but maybe I can not see the forest from the trees. I have spent a lot of time reading the forum but found nothing that made sense to me about this. It may be there I just can not find it. My query is in red below.
SELECT tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.[Date of hire], tblEmployees.[Date Left Cascade], tblEmployees.[Employment Status], tblWeldCodes.[Date Weld Code Provided], tblWeldCodes.[Date Weld Code Turned In], tblWeldCodes.Trainer, tblWeldCodes.[Weld Code], tblWeldCodes.[Initial Certification Date], tblWeldCodes.[Last Certification Date], tblWeldCodes.[View Certification], IIf(DateDiff("m",Now(),[Last Certification Date])<-25,"Production Manager Should Review!") AS Exp
FROM tblEmployees INNER JOIN tblWeldCodes ON tblEmployees.ContactID = tblWeldCodes.[Employee ID]
WHERE (((tblEmployees.[Date Left Cascade]) Is Null) AND ((tblWeldCodes.[Date Weld Code Turned In]) Is Null) AND ((tblWeldCodes.[View Certification]) Is Not Null));
This query relates to a table that has the following:
tblWeldCodes
ID1 Employee Date Weld Code Provided Date Weld Code Turned In Trainer Weld Code Initial Certification Date Last Certification Date
24 54 9/18/2003 Richard H 12/5/2005
32 45 4/29/2003 G 3/7/2006
48 28 8/31/2004 Richard C 5/13/2005
61 38 Richard Z 12/5/2005 5/5/2006
62 38 Richard Z 12/5/2005 12/5/2006
37 63 12/9/2005 J 12/5/2005
As you can see the employee 38 has been re-certified. I would like the above query to pick the last certification for each employee. I re-certify every 6 months and would like it to send me an e-mail to remind me the employees that need to be re-certified. The current query calculates the date but it calculates off every certification not just the last one for each employee. I want the query to just pick the employee (38) last certification date.
I hope I have given enough info to help. You can see that I know just enough to be dangerous! Thanks for the help. I have already used a lot of good info from this forum in the past. Hope I can have some help to solve this one.
SELECT tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.[Date of hire], tblEmployees.[Date Left Cascade], tblEmployees.[Employment Status], tblWeldCodes.[Date Weld Code Provided], tblWeldCodes.[Date Weld Code Turned In], tblWeldCodes.Trainer, tblWeldCodes.[Weld Code], tblWeldCodes.[Initial Certification Date], tblWeldCodes.[Last Certification Date], tblWeldCodes.[View Certification], IIf(DateDiff("m",Now(),[Last Certification Date])<-25,"Production Manager Should Review!") AS Exp
FROM tblEmployees INNER JOIN tblWeldCodes ON tblEmployees.ContactID = tblWeldCodes.[Employee ID]
WHERE (((tblEmployees.[Date Left Cascade]) Is Null) AND ((tblWeldCodes.[Date Weld Code Turned In]) Is Null) AND ((tblWeldCodes.[View Certification]) Is Not Null));
This query relates to a table that has the following:
tblWeldCodes
ID1 Employee Date Weld Code Provided Date Weld Code Turned In Trainer Weld Code Initial Certification Date Last Certification Date
24 54 9/18/2003 Richard H 12/5/2005
32 45 4/29/2003 G 3/7/2006
48 28 8/31/2004 Richard C 5/13/2005
61 38 Richard Z 12/5/2005 5/5/2006
62 38 Richard Z 12/5/2005 12/5/2006
37 63 12/9/2005 J 12/5/2005
As you can see the employee 38 has been re-certified. I would like the above query to pick the last certification for each employee. I re-certify every 6 months and would like it to send me an e-mail to remind me the employees that need to be re-certified. The current query calculates the date but it calculates off every certification not just the last one for each employee. I want the query to just pick the employee (38) last certification date.
I hope I have given enough info to help. You can see that I know just enough to be dangerous! Thanks for the help. I have already used a lot of good info from this forum in the past. Hope I can have some help to solve this one.