0

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.

  • 1
    A personal geodatabase uses the SQL implementation if Microsoft Access. I doubt that is valid syntax. Only the subselect would be needed. – Vince Mar 4 '16 at 0:00
  • Tried this and still getting invalid syntax error: SELECT MAX ([TRIP_NUM]) FROM [RS_Event_All_NE_25_Weekdays] GROUP BY [ROUTE_ID] – geoJshaun Mar 4 '16 at 0:06
  • 1
    Try: [TRIP_NUM] IN (SELECT MAX [TRIP_NUM] FROM [RS_Event_All_NE_25_Weekdays] WHERE [RS_Event_All_NE_25_Weekdays.ROUTE_ID] = [AnotherTable.ROUTE_ID]) – Richard Fairhurst Mar 4 '16 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.