I’m writing a report around students taxi routes (taxis to and from school) and need some assistance.
Please see the attachment which some sample data.
fig 1. output from routes table
this lists the route details for each student. Each students route could be:
* an outbound trip only - a lift from home to school (2 Map ID numbers - MAP_ID1 and MAP_ID2)
* an outbound and inbound trip - a lift from home to school, and from school to home (4 map ID numbers - MAP_ID1, MAP_ID2, MAP_ID3, MAP_ID4)
* an inbound trip only - a lift from school to home (2 Map ID numbers - MAP_ID3 and MAP_ID4)
The MAP_ID* fields indicate the students route start and route end points.
fig 2. output from stops table
this lists all the stops within each route (and the route start and end points). Each route may have multiple stops, e.g.
* route_id 37682 - this route transports one student to and from school
* route_id 37685 - this route transports one student from home to school only
* route_id 37687 - this route transports multiple students from their homes to school, and from school back home
The stop_type column indicates if the stop is at an address (A) or school (B).
fig 3. the desired output
I need the report to list all the stops for each student (and the route start and end point) in columns Outbound and Inbound. The stops need to be concatenated into either column. For example:
* student 208865 (route 37687) - has 3 stops in Outbound -'Student Five (Home Address), Student Six (Home Address), Another School', and 3 stops in inbound - 'Another School, Student Six (Home Address), Student Five (Home Address)'
Any help is appreciated.