MAX is still returning all records instead just MAX???

[scott0_1]

Hi all,



I am using the following query to find the maximum date for an employee's qualification:

SELECT tblCourseCatelog.CourseName, Max(tblEmployeeCourseExpiryDates.ExpiryDate) AS MaxOfExpiryDate, tblEmployeeCourseExpiryDates.ID, tblEmployeeCourseExpiryDates.EmpNO
FROM tblCourseCatelog INNER JOIN tblEmployeeCourseExpiryDates ON tblCourseCatelog.CatelogID = tblEmployeeCourseExpiryDates.CatelogID
GROUP BY tblCourseCatelog.CourseName, tblEmployeeCourseExpiryDates.ID, tblEmployeeCourseExpiryDates.EmpNO
HAVING (((tblEmployeeCourseExpiryDates.EmpNO)=12345678));

But 12345678 has two of the same course with different dates and the query is returning both records.

What am I doing wrong? It works fine if I simply do:

SELECT tblEmployeeCourseExpiryDates.ExpiryDate, Max(tblEmployeeCourseExpiryDates.EmpNO) AS MaxOfEmpNO
FROM tblEmployeeCourseExpiryDates
GROUP BY tblEmployeeCourseExpiryDates.ExpiryDate
HAVING (((Max(tblEmployeeCourseExpiryDates.EmpNO))=12345678));

Thanks for the help!

[Micron]

If you add too many fields to the situation, it can generate multiple Max values - one for each record that relates to one or more of the other fields. You would need to either create a query that finds the Max value in one field based on the minimum number of fields required, and use that as the filter for a second query or
see subquery basics

You will note that the example provided is based on returning records related to one meter (entity) and it may not work if more than one entity is involved.

[CJ_London]

it is because you are grouping on tblEmployeeCourseExpiryDates.ID. Remove from your query

[scott0_1]

Ugh. I hate using subqueries! Thanks for the help, I cheated and just deleted the last record from tblEmployeeCourseExpiryDates and inserted a new one. It's cheating, but it works!