Parent - Child relationship question

[toast]

Hello,

I'd like to know how I go about the following:
Each station is allocated a vehicle. That vehicle is then used on call outs. The table design is therefore as follows:

tblVehicle
vehicleID (primary key)

tblStation
stationID (primary key)
vehicleID (foreign key from tblVehicle)

tblCallOuts
calloutID (primary key)
stationID (foreign key from tblStation)

I am struggling to get a subform set up to show all the callouts that a particular vehicle has been on. This is because there is no direct link between tblVehicle and tblCallOuts - the common bridge is tblStation.

What is the best way of going about this? Do I solve it through the Parent / Child fields on the subform, or do I need to modify the table design?

Many thanks in advance for any assistance that is offered

 

[jdraw]

Does a CallOut always involve a vehicle? Only one?
What do you want to record about a callOut?
(start, end, location, reason.....)

If yes, then I would try

tblCallOuts
calloutID (primary key)
stationID (foreign key from tblStation)
vehicleId (FK vehicle)
CalloutStart (Date/Time)
CalloutEnd (Date/Time)

 

[toast]

Thank you for the prompt reply!

I think I may have been unhelpful by trying to simplify the design, so the attached image will hopefully clarify the set up by giving a bigger picture (superfluous fields have still been removed for simplicity).

Having given it more thought, I considered setting the subform control source to:

SELECT tblVehicle.*, tblShift.*, tblCallOut.*
FROM (tblVehicle INNER JOIN tblShift ON tblVehicle.VehicleID = tblShift.VehicleID) INNER JOIN tblCallOut ON tblShift.ShiftID = tblCallOut.ShiftID
WHERE tblVehicle.VehicleID = Forms!Main!NavigationSubForm.Form![VehicleID];

but I haven't managed to get that to work yet.

Is SQL the only way to achieve this?

 

[toast]

I think I have this working using the SQL.

I had an issue with the same field names across different tables, even though I used full references. But by changing the foreign key names the statement above appears to work (presumably aliases may also have worked).

Thank you for the help