Query Help!!

[rschnabl]

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.

 

[redneckgeek]

Just to get this out of the way... Spaces in field (or table) names makes things awfully hard to read. That's a poor practice.

I would use a subquery to get the record with the highest certification date for each employee.

SELECT EMPLOYEE, MAX([LAST CERTIFICATION]) AS LC
FROM TBLWELDCODES
GROUP BY EMPLOYEE

You'll use that query to find the right matching record

SELECT (put all your fields here)
[COLOR=black]FROM tblEmployees INNER JOIN tblWeldCodes ON tblEmployees.ContactID = tblWeldCodes.[Employee ID] INNER JOIN [/COLOR]
[COLOR=black][COLOR=blue](SELECT EMPLOYEE, MAX([LAST CERTIFICATION]) AS LC FROM TBLWELDCODES) RS [/COLOR][/COLOR]
[COLOR=black][COLOR=blue]ON TBLWWELDCODES.EMPLOYEEID=RS.EMPLOYEE AND TBLWELDCODES.[LAST CERTIFICATION]=RS.LC[/COLOR][/COLOR]
[COLOR=black][COLOR=black]WHERE....[/COLOR][/COLOR]

 

[rschnabl]

Thanks a unch. I am not sure that I understand the sub query part though.

 

[rschnabl]

Thanks a bunch!!