Why don't you use Access query builder to help construct the SQL?
Because access query builder doesn't create nested subquery, and I think without nested subquery is going to work with what I want to do.
Yes you are correct that query didn't work and is mostly because the alias name is not a representation of a nested subquery but instead a join,
however I did created OR operator in join clause before and it works, it will just give you more result because the OR represent either this or that.
What I want to do is I have a bookingLeg table, it has a startID and endID. startID is the station the train starts off, and endID is the station where the train end up.
I want to created a query that joins the startID from the bookingLeg table with the station's stationId and also joined the lineStatoin stationID, and bookingLeg's tid join with train's trainID.
and output the trainID from the train's table, and stationID and stationName from the station table, and distance_miles and time_mins from the lineStation table.
I then want to create another query the same as the one before, and this time with the endID join with the station's stationID and output all the field as from the before query (when join the startID).
I could do this in two separate query with no problems, but the problem is when I join these two subquery and then have each of these fields go in a single row in different columns (as below with the diagram, picture).
I noticed these two query have different results by just one join difference the startID or the endID.
this is my join for the first one with startID
Code:
SELECT startID.trainID, startID.trainName, startID.depart, startID.stationID, startID.stationName, startID.distance_miles, startID.time_mins, startID.trainDepart
FROM (SELECT trains.trainID, trains.trainName, trains.depart, station.stationID, station.stationName, lineStation.distance_miles, lineStation.time_mins,
DateAdd("n", lineStation.time_mins, trains.depart) as trainDepart
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID )
ON trains.trainID = bookingLeg.tid)
) AS startID;
this is my query with the endID, the result of the two output are different.
Code:
SELECT endID.trainID, endID.trainName, endID.depart, endID.stationID, endID.stationName, endID.distance_miles, endID.time_mins, endID.trainDepart
FROM (SELECT trains.trainID, trains.trainName, trains.depart, station.stationID, station.stationName, lineStation.distance_miles, lineStation.time_mins,
DateAdd("n", lineStation.time_mins, trains.depart) as trainDepart
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid)
) AS endID;
The problem is how to put the two together and show all the fields in different columns and rows.