ACCESS FEED: [MS_AccessPros] Trying to use Max Date expression

Rabu, 28 September 2011

[MS_AccessPros] Trying to use Max Date expression

I am trying to filter a query based on max date but it does not work correctly. This is the query:

SELECT qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name], qryApplicantExtended.HomePhone, qryApplicantExtended.MobilePhone, qryApplicantExtended.WorkPhone, qryApplicantExtended.Inactive, tlkpGender.GenderName, tblApplicationStatus.ApplicationStatusTypeID, Max(tblApplicationStatus.StatusDate) AS MaxOfStatusDate
FROM ((qryApplicantExtended INNER JOIN tlkpGender ON qryApplicantExtended.GenderID = tlkpGender.GenderID) INNER JOIN tblApplication ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID) INNER JOIN tblApplicationStatus ON tblApplication.ApplicationID = tblApplicationStatus.ApplicationID
GROUP BY qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name], qryApplicantExtended.HomePhone, qryApplicantExtended.MobilePhone, qryApplicantExtended.WorkPhone, qryApplicantExtended.Inactive, tlkpGender.GenderName, tblApplicationStatus.ApplicationStatusTypeID
HAVING (((qryApplicantExtended.Inactive)=-1));

But is return more records than I expected.

ApplicantID ApplicationStatusTypeID MaxOfStatusDate
596 Deceased 6/27/2011 8:35:27 AM
596 Denied due to Late 6/27/2011 8:35:22 AM
782 In a Unit 8/17/2011 11:15:54 AM
829 Approved for Waiting List 9/7/2004

For applicantID 596 there should be only record display for the date of 6/27/2011 8:35:27 AM

Am I using it wrong?

Thank you ,
Art Lorenzini