Results 1 to 7 of 7
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Create a JOIN of different tables (example 4) called this join a variable and list

    And then called this join as a symbol or variable, and then have it use to select the items from these joined tables,
    can this be done in Access?

    Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.

    Code:
    SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
    FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
                 INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
                ON trains.trainID = bookingLeg.tid) as firstJOIN
    Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table


    and I have to joined them to get those fields. I called this join firstJOIN

    and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID.

    Can Access do something like this, but syntax it differently?

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    Why don't you use Access query builder to help construct the SQL?

    The use of query alias name is not correct because there is not a nested subquery. Also, I've never seen an OR operator in a join clause. I doubt that will work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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.

    Click image for larger version. 

Name:	11-17-13.png 
Views:	7 
Size:	6.8 KB 
ID:	14408

    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    True, query builder won't build nested query. What I do is create and save the inner query. Then I build the outer query with reference to the first query. Then I copy/paste the SQL from first query into the second, add the parens and AS keyword for the alias. This is how I get correct structure for nested SQL statements I want to use in VBA.

    If you need to include Station table in query just to retrieve the associated StationName for the StartID and EndID, include the table twice. Pull it into the design window twice. They will be named Station and Station_1. Join one to StartID and the other to EndID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Ok, I tried your first method and it was confusing in getting the right result.
    Then I tried your second method with the query builder and it was much more easier and organized, and I think is right (I still have to double check).
    Here is the below code for the second method.

    Code:
    SELECT bookingLeg.bookinglegID, bookingLeg.tid, trains.trainName, station.stationName AS [Station Start Name], station_1.stationName AS [Station End Name], lineStation.distance_miles AS [Starting Distance], lineStation.time_mins AS [Starting Mins], lineStation_1.distance_miles AS [Ending Distance], lineStation_1.time_mins AS [Ending Time]
    FROM lineStation AS lineStation_1 INNER JOIN (((station AS station_1 INNER JOIN (station INNER JOIN bookingLeg ON station.stationID = bookingLeg.startID) ON station_1.stationID = bookingLeg.endID) INNER JOIN trains ON bookingLeg.tid = trains.trainID) INNER JOIN lineStation ON station.stationID = lineStation.stationID) ON lineStation_1.stationID = station_1.stationID
    I would say I would be confuse on when I should use the first method and when the second method?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    I didn't mean the first approach was appropriate for your requirement, it was just offered for future reference.

    Here is a quick tutorial on subqueries http://allenbrowne.com/subquery-01.html

    Use the simple join when you just want to retrieve information associated with a foreign key.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Ok, thanks for the link!! A lot to grasp there.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  2. Join multiple tables to one
    By b6677862 in forum Queries
    Replies: 1
    Last Post: 02-08-2012, 06:59 AM
  3. Outer Join Nested in Inner Join
    By Stevens7 in forum Queries
    Replies: 2
    Last Post: 10-19-2011, 01:34 PM
  4. Join 4 Tables in 1 Query
    By sandlucky in forum Queries
    Replies: 5
    Last Post: 06-12-2011, 06:28 PM
  5. Join for multiple tables?
    By Etownguy in forum Queries
    Replies: 3
    Last Post: 05-30-2011, 04:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums