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

Rabu, 28 September 2011

RE: [MS_AccessPros] Trying to use Max Date expression

Art-

Try this:

SELECT qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name],
qryApplicantExtended.HomePhone, qryApplicantExtended.MobilePhone,
qryApplicantExtended.WorkPhone, qryApplicantExtended.Inactive,
tlkpGender.GenderName, tblApplicationStatus.ApplicationStatusTypeID,
tblApplicationStatus.StatusDate
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
WHERE qryApplicantExtended.Inactive = -1
AND tblApplicationStatus.StatusDate = (SELECT Max(StatusDate) FROM
tblApplicationStatus As S2 WHERE S2.ApplicationID =
tblApplication.ApplicationID);

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Wednesday, September 28, 2011 6:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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

------------------------------------

Yahoo! Groups Links