I have used a variation of this sql statement successfully in the past but can't get it to work now. I have a table named "RS_Event_All_NE_25_Weekdays" in a personal geodatabase running on arcmap 10.2. I have a field called ROUTE_ID and another field called TRIP_NUM. Every record is an event that happens on a route. The TRIP_NUM gives the number of the trip the route is on when the event occurs. Each route can do up to 3 trips in a day. Most routes will do 2 trips but some do 3 and some do only 1.
I want to use a sql statement to select all the events for each route that happens during the last trip.
Here is what I tried:
SELECT [TRIP_NUM] IN (SELECT MAX( [TRIP_NUM] ) FROM [RS_Event_All_NE_25_Weekdays] GROUP BY [ROUTE_ID] )
This gave a general syntax error. I have read another post on here using the nested sql expession with the MAX clause. I copied that syntax and replaced the field and table names and still got a syntax error.