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-

It's very tempting to use a Totals query when you want the Max of something, but in this case, you want the row with the highest value. A Max subquery is the solution.

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 Lorenzini
Sent: Wednesday, September 28, 2011 7:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Trying to use Max Date expression

John,
That was perfect but when you get a few minutes can you breakdown what you did?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, September 28, 2011 12:15 PM
Subject: 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

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links