locked
Struggling with MS Access Joins RRS feed

  • Question

  • Hello everybody,

    I am struggling with creating a query using joins. I have the following related 3 tables.

    Reservation
    ID1 (Primary key)
    Id (Foreign key to Adresses's Id, Many to One, with Referential Integrity)
    Key (Foreign key to Randonnee's Key, Many to One, without Referential Integrity)
    ...
    Nb Personnes

    Adresses
    Id
    ...
    Nom
    Prenom
    ...
    No Tel
    No Tel Prof

    Randonnee
    Key
    Titre
    Date
    ...

    I am looking to create a report diplaying data from the 3 table and allowing me to select the entries in the Reservation table based on the Date field of the Randonnee table.

    I have tried to create a report using the following SQL Query:

    SELECT Reservation.Key, Randonnee.Date, Randonnee.Titre, Adresses.Nom, Adresses.Prenom, Adresses.[No Tel], Adresses.[No Tel Prof], Reservation.[Nb personnes]
    FROM (Reservation LEFT JOIN Randonnee ON Reservation.[Key] = Randonnee.[Key]) LEFT JOIN Adresses ON Reservation.[Id] = Adresses.[Id];

    Doing so, only the fields from the Reservation and Adresses tables are displayed, but not the ones from the Randonnee table (e.g. Randonnee.Date, Randonnee.Titre)

    Any clue on what goes wrong here?

    Thanks in advance
    Thursday, January 5, 2017 12:45 PM

All replies

  • SELECT Reservation.Key, Randonnee.Date, Randonnee.Titre, Adresses.Nom, Adresses.Prenom, Adresses.[No Tel], Adresses.[No Tel Prof], Reservation.[Nb personnes]
    FROM (Reservation LEFT JOIN Randonnee ON Reservation.[Key] = Randonnee.[Key]) LEFT JOIN Adresses ON Reservation.[Id] = Adresses.[Id];

    Hi Artemisia Genipi,

    I cannot see so quick where the SQL query goes wrong, but I can remember that long, long time ago I had also prblems with the construction of joins. I then surrounded each join with round brackets:

        FROM ((Reservation LEFT JOIN Randonnee ON Reservation.[Key] = Randonnee.[Key]) LEFT JOIN Adresses ON Reservation.[Id] = Adresses.[Id]);

    What happens if you try this?

    Instead of a lengthy field specification you could also use    SELECT * FROM   to select all fields.

    Instead of using "Id" as field name, I personally prefer to include the entity name in the field name. For the Reservatgion table that would be (or something like that):

         Reservation
         Reservation_Id (Primary key)
         Adresses_Id (Foreign key to Adresses's Id, Many to One, with Referential Integrity)
         Key_Id (Foreign key to Randonnee's Key, Many to One, without Referential Integrity)

    At least for me it is easier to understand what is joined to what, and it opens a door to automatically generating SQL queries, based on entity names.

    Imb.


    • Edited by Imb-hb Thursday, January 5, 2017 2:03 PM reduced quoted content
    Thursday, January 5, 2017 2:02 PM
  • Hi Artemisia Genipi -

    One other suggestion to help debug your SQL in addition to those from Imb-hb.
    Since you have the Reservation join with Address working, isolate the problem join:

    SELECT Reservation.Key, Randonnee.Date, Randonnee.Titre, Reservation.[Nb personnes]
    FROM (Reservation LEFT JOIN Randonnee ON Reservation.[Key] = Randonnee.[Key])

    Also - what is the field type of the [Key] field? I've encountered Joins using text that fail because spaces.


    -MainSleuth

    Thursday, January 5, 2017 3:06 PM
  • Hi MainSleuth,

    I have made a test by isolating the problematic join... which does not work neither. So, apparently, there must be something with the keys used for the join (there are of text type)...

    I will investigate further and let you know the result.

    Artemisia Genipi

    Thursday, January 5, 2017 5:00 PM
  • Hello Everybody,

    Ok. I finally got the thing workging after having almost torn my hair out. I have tried a lot of changes (reinstating the relationships, changing the data type of the Keys, changing the JOIN type... and so on) without satisfactory results.

    So I exported all of my tables to Excel, created a new set of tables from scratch and loaded the exports into them... and it now works. For what reason it was not working on the original set, I don't know... Migrating from Lotus Approach to MS Access is definitely not a trivial undertaking.

    Anyway, thank you all for your suggestions.

    Artemisia Genipi

    • Proposed as answer by Chenchen Li Tuesday, January 10, 2017 9:00 AM
    Friday, January 6, 2017 2:14 PM
  • Good to hear, the hair grows back (I've been told it is good for character development). Thanks for following up & you have a solution.


    -MainSleuth

    Friday, January 6, 2017 2:24 PM