Results 1 to 6 of 6
  1. #1
    ceebee is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    12

    3 table join

    Hi

    I am trying to perform a query that has 3 tables.

    I read about inner and outer joins, but I find it a bit confusing as a newbie.

    I am trying to join Table A, B, C

    It is for a software inventory - who has it (tblPOC), what software (tblSoftware) and the physical location (tblBldg).

    The tables are joined by foreign keys:

    tblSoftware.POCID
    tblPOC.BldgID



    Any help is greatly appreciated.

    Thank You

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you know how to Join two tables in Query Builder?
    It is very similar, you are just adding a third table.

    In order for us to help you, it important to know the relationships between the tables (one-to-one, one-to-many, many-to-many), and what exactly who you want to return.
    If you do simple inner joins between the tables, it will only return records where all three tables have matches.
    Sometimes, you may want to see ALL the information from one table, along with the matches from the other two.

    So if you would like some help, please provide this information.

  3. #3
    ceebee is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    12
    OK my apologies.

    I am using 3 tables:

    tblSoftware
    tblPOC
    tblBldg

    When I click on the relationships, I can see that:

    tblBldg - tblPOC -> Only include rows where the joined fields from both tables are equal

    tblSoftware - tblPOC -> Include all records from 'tblSoftware' and only those records from 'tblPOC' where the joined fields are equal

    I have attached a picture of the 3 tables

    Click image for larger version. 

Name:	query3tables.PNG 
Views:	7 
Size:	22.6 KB 
ID:	28423


    Thank You

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, then are you all set, or do you still have a question or issue?

  5. #5
    ceebee is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    12
    Unfortunately not all set to go.

    I am getting this error when I try and run a query.

    Click image for larger version. 

Name:	query error.PNG 
Views:	6 
Size:	25.9 KB 
ID:	28426

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, Access doesn't seem to like it a lot of times when you have both Inner Joins and Left/Right Joins in the same query.
    Try changing the Join between tblPOC and tblBuilding so that it shows an arrow going from tblPOC to tblBuilding.

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

Similar Threads

  1. Join A Field In One Table to Two Fields In Another Table
    By Aaron5714 in forum Database Design
    Replies: 3
    Last Post: 05-10-2014, 02:56 PM
  2. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  3. Table Join Help
    By Jonesy in forum Database Design
    Replies: 2
    Last Post: 09-13-2013, 11:35 AM
  4. join table
    By graciemora in forum Queries
    Replies: 2
    Last Post: 10-28-2010, 01:57 PM
  5. Self Join in a Table
    By LornaM in forum Database Design
    Replies: 10
    Last Post: 05-06-2009, 10:29 AM

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