Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2019
    Posts
    16

    Joining another Table yields no results

    When I run the following query I get the expected results:

    SELECT Sponsor_Request_Table.SponsorRequestID_PK,Sponsor_ Request_Table.OldRFID, Sponsor_Request_Table.AirTimeSponsor,Employee_List _Table.EmployeeInitials, Sponsor_Request_Table.Status,Sponsor_Request_Table .RequestedDate, Sponsor_Request_Table.RequiredDate,Sponsor_Request _Table.Notes, Order_Details_Table.Qty,Order_Details_Table.Refurb , Order_Details_Table.DefaultProgramming,Order_Detai ls_Table.OrderDetailNotes, Order_Details_Table.KitID_FK,Order_Details_Table.I temID_FK, Order_Details_Table.TechID_FK


    FROM (Employee_List_Table INNER JOIN Sponsor_Request_TableON Employee_List_Table.EmployeeID_PK = Sponsor_Request_Table.EmployeeID_FK)INNER JOIN Order_Details_Table ON Sponsor_Request_Table.SponsorRequestID_PK =Order_Details_Table.SponsorRequestID_FK
    WHERE (((Sponsor_Request_Table.Status)="OPEN" Or(Sponsor_Request_Table.Status)="PARTIAL"));


    I Then want to add additional details from another table Kit_Table, it yields NO Results...frustrating:

    SELECT Sponsor_Request_Table.SponsorRequestID_PK,Sponsor_ Request_Table.OldRFID, Sponsor_Request_Table.AirTimeSponsor,Employee_List _Table.EmployeeInitials, Sponsor_Request_Table.Status,Sponsor_Request_Table .RequestedDate, Sponsor_Request_Table.RequiredDate,Sponsor_Request _Table.Notes, Order_Details_Table.Qty,Order_Details_Table.Refurb , Order_Details_Table.DefaultProgramming,Order_Detai ls_Table.OrderDetailNotes, Order_Details_Table.KitID_FK,Order_Details_Table.I temID_FK, Order_Details_Table.TechID_FK
    FROM Kit_TableINNER JOIN ((Employee_List_Table INNER JOIN Sponsor_Request_Table ONEmployee_List_Table.EmployeeID_PK = Sponsor_Request_Table.EmployeeID_FK) INNERJOIN Order_Details_Table ON Sponsor_Request_Table.SponsorRequestID_PK =Order_Details_Table.SponsorRequestID_FK) ON Kit_Table.KitID_PK = Order_Details_Table.KitID_FK
    WHERE (((Sponsor_Request_Table.Status)="OPEN" Or(Sponsor_Request_Table.Status)="PARTIAL"));

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    When you do ALL inner joins, you will only return records if records exist in ALL of the tables included in the query. If a certain ID value does not exist across all tables, it won't be returned.
    Also, you mentioned adding in the Kit_Table, but it looks like you set this up to be the main FROM table, and not one of the joining ones.

    Typically, when I do a query that joins a bunch of tables, I identify the "main" table that control how many records I want to return, and then I use this as my "main" FROM table.
    Then, if you do LEFT OUTER joins from this table to the other tables, you will still return ALL records from the MAIN table, even if it does not have matches in all of the other tables.

    See here for details: https://kb.nmsu.edu/page.php?id=79481

    Also one thing to note that often tricks people up, when joining your tables, the join field must match EXACTLY (especially Data Type). If you try joining a field of Text Data Type to a field of Numeric Data Type, it won't work (nothing will match, and your query won't return anything). Also, be on the lookout for extra spaces in your Join fields, which will also cause records not to match.

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

Similar Threads

  1. Joining table?
    By elliemac in forum Database Design
    Replies: 7
    Last Post: 05-04-2019, 02:13 AM
  2. Sum yields #Error in form footer
    By LonghronJ in forum Forms
    Replies: 15
    Last Post: 08-20-2018, 07:47 AM
  3. Replies: 2
    Last Post: 08-21-2017, 10:48 AM
  4. Replies: 3
    Last Post: 09-16-2013, 01:05 PM
  5. My report yields more than one result
    By hawzmolly in forum Reports
    Replies: 16
    Last Post: 07-04-2009, 10:30 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