Help Needed with query

[Guest]

Hello,

I need help with a query.

Table looks like this:

IDNum PK, Text
ApplName Text
AccessGranted Y/N
AccessVerifiedDate Date

I need the IDNum, and access verified date for the last application that
was verified. Must have a "Where AccessGranted = True". I think I need
Max(AccessVerifiedDate) in the query but don't know how to include it in the
Select clause.

TIA,
Rich

 

[John Spencer]

One method would use a correlated sub-query in the Select clause

One method would use two queries
Query one - saved as qLastVerified
SELECT ApplName, Max(AccessVerifiedDate) as LastDate
FROM Table
GROUP BY ApplName

Use that and the orginal table in a query joining on ApplName
SELECT Table.*
FROM Table INNER JOIN QLastVerified
On Table.ApplName = QLastVerified.ApplName
AND Table.AccessVerifiedDate = QLastVerified.LastDate

That can be done in one query (normally as long as your field or table names
don't require square brackets to delimit them)
SELECT Table.*
FROM Table INNER JOIN
(SELECT ApplName, Max(AccessVerifiedDate) as LastDate
FROM Table
GROUP BY ApplName) as QLastVerified
On Table.ApplName = QLastVerified.ApplName
AND Table.AccessVerifiedDate = QLastVerified.LastDate

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..