Query most recent entry

  • Thread starter Thread starter martygeo
  • Start date Start date
M

martygeo

Hi,
I am trying to implement a query to find the most recent records for a
series of datapoints. i successfully used the following SQL statement on a
small trial dataset:

SELECT *
FROM [TableName]
WHERE [DateTimeField] in (Select Max([DateTimeField]) from [TableName] as S
where [Client]=[TableName].[Client])

The problem is that my dataset has 163k records for over 500 "clients" and I
don't have the time to let it process for hours (which it appears to be
doing). Can anyone suggest a more computational efficient query to do the
same thing?

Martin
 
SELECT TableName.*
FROM [TableName] INNER JOIN
(Select Client, Max(DateTimeField) as LastTime from TableName GROUP BY
Client) as S
ON TableName.Client = S.Client
AND TableName.DateTimeField = S.LastTime

This will work if your table and field names follow the naming guidelines -
Letters, Numbers, and the underscore character only. Also, no reserved words
for field names - Date is the most common example of a reserved word. The
sub-query in a from clause cannot have any square brackets in it.

If the sub-query requires brackets, you can do a two-stage query. First build
the query to return the Client and Max date and save that.

NOW, use that query and your table in a second query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top