Re: Help Needed with query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
..

"rich" <rich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3B743926-81F5-4765-A6B1-4DD3EE3911E2@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Maximum number of elements ina an IN() Clause
    ... if the IN clause is the ONLY criteria... ... If you are using MS SQL Server, can you define an heterogenous server, on it, pointing to an Access-Jet database, and push the values in a table there? ... Vanderghast, Access MVP ... would making the passtrough query WITHOUT the in clause and then, ...
    (microsoft.public.access.queries)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • Re: Maximum number of elements ina an IN() Clause
    ... If the IN clause is the only criteria, ... Vanderghast, Access MVP ... but would making the passtrough query WITHOUT the in clause and then, ...
    (microsoft.public.access.queries)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... From the User Interface, in the toolbar, or the menu, when you edit a query, ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)