Query Criteria

[jrobin5881]

I need to return rows based on the latest date. I have the following fields in one table.
Location Rt# Time Date
Main 1 5.30 3/27/2005
Main 1 8.25 5/27/2005
Main 1 6.50 4/10/2005
My data repeats this many times over and I only want to see the row that has the maximum date of 5/27/2005. I'm using microsoft access 2002 and tried using the query wizard with no success. Any help is appreciated

 

[lespaul]

This doesn't work?

SELECT [Location Rt#], [Time], Max([Date]) From TableName GROUP BY [Location Rt#], [Time]

you should avoid using field names TIME and DATE since these are reserved keywords.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lupins46]

In the SQL view of a new query:

SELECT TOP 1 mytable.*
FROM mytable
ORDER BY mytable.[Date] DESC;

Use your own table name.

 

[DotNetGnat]

SELECT Location, Time, Date from Mytable A
WHERE A.Date IN ( SELECT TOP 1 Date from Mytable B
WHERE A.Location=B.Location ORDER BY Date DESC)

-DNG

 

[jrobin5881]

Leslie,

I added the Max([Date]) in the criteria box under the query and got the following error message: " Cannot have a aggregate function in the where clause ([99s].date=Max([date]))" any idea why?

 

[lespaul]

it doesn't need to be in the where clause.

If you have the field names you showed above, paste the query in the SQL view and fix the table name. If you need more fields in the SELECT clause, add them and then add them to the GROUP BY clause. Every non-aggregate field (the ones without SUM, COUNT, MAX, MIN, etc) in the SELECT has to be listed in the GROUP BY.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jrobin5881]

The query runs but it's still returning all dates. Check it out, here's what I put in the SQL view:

SELECT [99s].Location, [99s].RTE, [99s].STR, Max([99s].date) AS Latest
FROM 99s
GROUP BY [99s].Location, [99s].RTE, [99s].STR;

 

[lespaul]

you should be getting the max date for each distinct Location, RTE and STR. So if you have location 1 and RTE 3, 4, and 5 you will get 3 records. With the sample data you provided above you would only get the one record. if you would like to provide additional sample data that is more consistent with what you are actually working with and the results you want to see from that sample data, perhaps we can come up with something that will work for you.

leslie

 

[PHV]

Something like this ?
SELECT A.Location, A.RTE, A.STR, A.date
FROM [99s] AS A INNER JOIN (
SELECT Location, Max([date]) AS Latest FROM [99s] GROUP BY Location
) AS L ON A.Location=L.Location AND A.date=L.Latest

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrobin5881]

PHV - We're close.... I cut and pasted your post and it's giving me only one route per each location. I need to see the row with the most recent date for each route in that location. Here's a sample data set:

location RTE date
00601 001 3/25/2003
00601 002 3/26/2003
00602 001 3/26/2003
00602 001 4/7/2005
00602 002 3/27/2003
00602 002 4/5/2005
00602 003 3/27/2003

I want my query to return
location RTE date
00601 001 3/25/2003
00601 002 3/26/2003
00602 001 4/7/2005
00602 002 4/5/2005
00602 003 3/27/2003

 

[lespaul]

since you also want each RTE you need to add that into the INNER JOIN query:

SELECT A.Location, A.RTE, A.STR, A.date
FROM [99s] AS A INNER JOIN (
SELECT Location, RTE, Max([date]) AS Latest FROM [99s] GROUP BY Location, RTE
) AS L ON A.Location=L.Location AND A.date=L.Latest AND A.RTE = L.RTE

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jrobin5881]

That's the ticket!!! Thank you very much and I'll be re-using that approach with several other projects I have to tackle.