How to find next 5 records for multiple customers

[tedsmith]

I want to quickly show the next 4 departures from a bus timetable where some buses run every 5 minutes while others run every hour.
Is it possible to select in only ONE SQL statement from a large table containing all the departures for a week?
Eg if there are 100 buses then the record set should return maximum only 400 rows irrespective of the frequency of bus departures.
TOP 400 seems only good for the total records, sorting by time gives different numbers for different routes.
Something like repeating a query 100 times counting each route until 4 each are found would be rather slow.

 

[dhookom]

tedsmith,
Can you provide some information about your table(s)? Having a primary key is critical to the solution. You could use a subquery in the criteria. The next 5 assumes a base record for each bus. How do you get the base record for each bus?

Duane
Hook'D on Access
MS Access MVP

 

[tedsmith]

The master timetable has 25,000 records, each one giving the time each bus arrives at one of 72 bus stops throughout the city.
Every 5 mins I create a smaller table of the next 2 hours departures for the query to run on as this has to happen every 250ms to service all the bus stops in around 20 seconds depending on the traffic. The query I am currently using (see below) takes around 100ms to complete

There are over 100 different routes or variants of these. There can be over 500 buses on the road in peak times. Routes take from 10 to 60 minutes to travel.

Simplified example:
Say a bus route A departs from the source every 5 minutes, stops at all 20 bus stops

Say a bus Route B departs every 30 minutes, runs an express version of Route A stopping at only every 5th stop.

Say a bus Route C every 10 minutes and leaves 5 mins later, travels along half of the same route for 10 of the same stops then branches off and services another 10 stops to a different destination.

The arrival signs at stops show only 4 rows
If a simple SELECT query were used, a sign at Stop 10 would show
Route A Frequent 5min (time it takes to get there
Route C Express 7min (saves 3 mins due to being express)
Route A Frequent 10min
Route C Express 14min
Route wouldn't show at all for much of the time because it could be 5th in line

If I used a FIRST of query you would see
Route A Frequent 5min
Route C Different 7min
Route B Express 35min (30 + plus the time it takes to get there)
The 4th row would always be blank.

What I would like to see the first of every route and if there were less than 4 routes, the next again most frequent route to arrive.
Route A Frequent 5min
Route C Express 7min
Route B Different 35min
Route A Frequent 10min

Some other stops have only a 10 min service and a once an hour express service which makes the problem even more obvious.
The screen either fills up with the frequent service or only ever shows 2 rows, Frequent & Express

I guess this could be done by using 2 or more queries but I am hoping to do it in one.

Example of a Select query criteria. It is joined to another table top retrieve which sign to show it on at the bus stop (Inbound or outbound)

Criteria = "SELECT TOP 4 INBWorking.StopID, INBWorking.StationID, INBWorking.RouteNumber, " _
    & "INBWorking.TripEndPlaceAbbrev, INBWorking.ScheduledTime, INBWorking.RealTime, " _
    & "CVDate([RealTime]) AS SearchTime, INBWorking.StopPassTimeSecs, INBWorking.BusNo, " _
    & "Stations.Sign1, Stations.Sign2, Stations.Sign3, Stations.Sign4 " _
    & "FROM INBWorking INNER JOIN Stations ON INBWorking.StopID = Stations.StopID " _
    & " WHERE INBWorking.StopID='" & StopIdOB(MainForm.lblLEDClient(Station)) & "' " _
    & "AND INBWorking.StopPassTimeSecs > " & NowSeconds - 180 & " " _
    & "ORDER BY CVDate([RealTime]);"