Results 1 to 4 of 4
  1. #1
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185

    Query with mutiple tables - Join issue

    I have a query that has multiple tables linked together.
    I have 1 table which is causing abnormal behavior. If I link to this table, Left join to the main data table and only want records from this table that match the main table, it only returns the values if the of both tables if they match.


    If I delete this table from the query it works as it should. I'm not sure if I have some setting wrong, or what.

    Any thoughts on what I could check would be greatly appreciated. The joins are correct.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Could you change your Query to SQL View, and copy and paste the code here for us to analyze?
    And could you tell us which of the tables in the query you are trying to do this with?

  3. #3
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185
    Thanks, Joe!
    Total_Supply_Position is the main table. I'm lookin for any parts on the notes and Critical field from the Item_Notes_Collection table. When I add this table, it will not display any records in my query unless I add a record to Item Notes Collection. And then it only returns the information for this one record, rather than all the records from Total_Supply_Position

    SELECT Total_Supply_Position.Product, Total_Supply_Position.[Pur/Mfg], Total_Supply_Position.Item_Number, Total_Supply_Position.Item_Description, Count([Parent Item]) AS [Parent Items], Total_Supply_Position.LPA, Total_Supply_Position.[Quantity Per], Total_Supply_Position.[Yield%], Total_Supply_Position.[TOTAL OH], Total_Supply_Position.ASSY, Total_Supply_Position.QUALITY, Total_Supply_Position.WIP, Total_Supply_Position.WareHouse, Total_Supply_Position.[Store Control], Total_Supply_Position.[6W_Dmnd], Total_Supply_Position.DGR, Total_Supply_Position.[Days On Hand], Total_Supply_Position.[Item Type], Total_Supply_Position.Blanks, Total_Supply_Position.status, Total_Supply_Position.UM, Total_Supply_Position.[Standard Cost], Total_Supply_Position.supplier, Total_Supply_Position.[Supplier name], Total_Supply_Position.origin, Total_Supply_Position.[Country Code], Total_Supply_Position.Country, Total_Supply_Position.[PD Demand], Total_Supply_Position.[Current Demand], Total_Supply_Position.[On Hand], Total_Supply_Position.[Cost Total], Total_Supply_Position.[Safety Stk], Total_Supply_Position.[Week1 Demand], Total_Supply_Position.[Week2 Demand], Total_Supply_Position.[Week 3 Demand], Total_Supply_Position.[Week 4 Demand], Total_Supply_Position.[Week 5 Demand], Total_Supply_Position.[Week 6 Demand], Total_Supply_Position.[Week 7 Demand], Total_Supply_Position.[Week 8 Demand], Total_Supply_Position.[Week 9 Demand], Total_Supply_Position.[Week 10 Demand], Total_Supply_Position.[Week 11 Demand], Total_Supply_Position.[Week 12 Demand], Total_Supply_Position.[PD Supply], Total_Supply_Position.[Current Supply], Total_Supply_Position.[Week1 Supply], Total_Supply_Position.[Week2 Supply], Total_Supply_Position.[Week 3 Supply], Total_Supply_Position.[Week 4 Supply], Total_Supply_Position.[Week5 Supply], Total_Supply_Position.[Week 6 Supply], Total_Supply_Position.[Week7 Supply], Total_Supply_Position.[Week 8 Supply], Total_Supply_Position.[Week 9 Supply], Total_Supply_Position.[Week10 Supply], Total_Supply_Position.[Week11 Supply], Total_Supply_Position.[Week12 Supply], Total_Supply_Position.[PD On hand], Total_Supply_Position.[Current On hand], Total_Supply_Position.[Week1 On hand], Total_Supply_Position.[Week2 On hand], Total_Supply_Position.[Week3 On hand], Total_Supply_Position.[Week4 on hand], Total_Supply_Position.[Week 5 on hand], Total_Supply_Position.[Week6 on hand], Total_Supply_Position.[Week 7 on hand], Total_Supply_Position.[Week 8 on hand], Total_Supply_Position.[Week 9 on hand], Total_Supply_Position.[Week 10 on hand], Total_Supply_Position.[Week 11 on hand], Total_Supply_Position.[Week 12 on hand], Total_Supply_Position.[% of On Time Schedules], Total_Supply_Position.[Order Issue Time], Total_Supply_Position.[Calculated Supplier Time], Total_Supply_Position.[Transit Time], Total_Supply_Position.[In Transit - ASN], Total_Supply_Position.[Open Firm], Total_Supply_Position.[Target Days On Hand Calc], Item_Notes_Collection.Notes, IIf([Critical]=0,0,-1) AS Expedite
    FROM (Total_Supply_Position LEFT JOIN Parent_Component_Temp ON Total_Supply_Position.Item_Number = Parent_Component_Temp.[Component Item ]) LEFT JOIN Item_Notes_Collection ON Total_Supply_Position.Item_Number = Item_Notes_Collection.Item_Number
    GROUP BY Total_Supply_Position.Product, Total_Supply_Position.[Pur/Mfg], Total_Supply_Position.Item_Number, Total_Supply_Position.Item_Description, Total_Supply_Position.LPA, Total_Supply_Position.[Quantity Per], Total_Supply_Position.[Yield%], Total_Supply_Position.[TOTAL OH], Total_Supply_Position.ASSY, Total_Supply_Position.QUALITY, Total_Supply_Position.WIP, Total_Supply_Position.WareHouse, Total_Supply_Position.[Store Control], Total_Supply_Position.[6W_Dmnd], Total_Supply_Position.DGR, Total_Supply_Position.[Days On Hand], Total_Supply_Position.[Item Type], Total_Supply_Position.Blanks, Total_Supply_Position.status, Total_Supply_Position.UM, Total_Supply_Position.[Standard Cost], Total_Supply_Position.supplier, Total_Supply_Position.[Supplier name], Total_Supply_Position.origin, Total_Supply_Position.[Country Code], Total_Supply_Position.Country, Total_Supply_Position.[PD Demand], Total_Supply_Position.[Current Demand], Total_Supply_Position.[On Hand], Total_Supply_Position.[Cost Total], Total_Supply_Position.[Safety Stk], Total_Supply_Position.[Week1 Demand], Total_Supply_Position.[Week2 Demand], Total_Supply_Position.[Week 3 Demand], Total_Supply_Position.[Week 4 Demand], Total_Supply_Position.[Week 5 Demand], Total_Supply_Position.[Week 6 Demand], Total_Supply_Position.[Week 7 Demand], Total_Supply_Position.[Week 8 Demand], Total_Supply_Position.[Week 9 Demand], Total_Supply_Position.[Week 10 Demand], Total_Supply_Position.[Week 11 Demand], Total_Supply_Position.[Week 12 Demand], Total_Supply_Position.[PD Supply], Total_Supply_Position.[Current Supply], Total_Supply_Position.[Week1 Supply], Total_Supply_Position.[Week2 Supply], Total_Supply_Position.[Week 3 Supply], Total_Supply_Position.[Week 4 Supply], Total_Supply_Position.[Week5 Supply], Total_Supply_Position.[Week 6 Supply], Total_Supply_Position.[Week7 Supply], Total_Supply_Position.[Week 8 Supply], Total_Supply_Position.[Week 9 Supply], Total_Supply_Position.[Week10 Supply], Total_Supply_Position.[Week11 Supply], Total_Supply_Position.[Week12 Supply], Total_Supply_Position.[PD On hand], Total_Supply_Position.[Current On hand], Total_Supply_Position.[Week1 On hand], Total_Supply_Position.[Week2 On hand], Total_Supply_Position.[Week3 On hand], Total_Supply_Position.[Week4 on hand], Total_Supply_Position.[Week 5 on hand], Total_Supply_Position.[Week6 on hand], Total_Supply_Position.[Week 7 on hand], Total_Supply_Position.[Week 8 on hand], Total_Supply_Position.[Week 9 on hand], Total_Supply_Position.[Week 10 on hand], Total_Supply_Position.[Week 11 on hand], Total_Supply_Position.[Week 12 on hand], Total_Supply_Position.[% of On Time Schedules], Total_Supply_Position.[Order Issue Time], Total_Supply_Position.[Calculated Supplier Time], Total_Supply_Position.[Transit Time], Total_Supply_Position.[In Transit - ASN], Total_Supply_Position.[Open Firm], Total_Supply_Position.[Target Days On Hand Calc], Item_Notes_Collection.Notes, IIf([Critical]=0,0,-1)
    HAVING (((Total_Supply_Position.Product) Like "*" & [Forms]![Menu_MainMenu]![ProductLine] & "*" Or (Total_Supply_Position.Product)="IsNull") AND ((Total_Supply_Position.Item_Number) Like "*" & [Forms]![Menu_MainMenu]![Item] & "*" Or (Total_Supply_Position.Item_Number)="IsNull") AND ((Total_Supply_Position.LPA) Like "*" & [Forms]![Menu_MainMenu]![LPA] & "*" Or (Total_Supply_Position.LPA)="IsNull") AND ((Total_Supply_Position.[Store Control]) Like "*" & [Forms]![Menu_MainMenu]![StorageControl] & "*" Or (Total_Supply_Position.[Store Control])="IsNull") AND ((Total_Supply_Position.Blanks) Like "*" & [Forms]![Menu_MainMenu]![Blank] & "*" Or (Total_Supply_Position.Blanks)="IsNull") AND ((Total_Supply_Position.supplier) Like "*" & [Forms]![Menu_MainMenu]![SupplierCode] & "*" Or (Total_Supply_Position.supplier)="IsNull") AND ((Total_Supply_Position.[Supplier name]) Like "*" & [Forms]![Menu_MainMenu]![SupplierName] & "*" Or (Total_Supply_Position.[Supplier name])="IsNull") AND ((Total_Supply_Position.origin) Like "*" & [Forms]![Menu_MainMenu]![Origin] & "*" Or (Total_Supply_Position.origin)="IsNull"));

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Wow, you got a lot there!

    In these type of situations, it can often be hard "to see the forest for the trees". So what I usually like to do is start from the beginning with a much simpler version, just to make sure that you have the JOINS working properly, and it is returning what you expect. Then once you have that working the way that you need, you can add all the other fields in.

    So try creating a new query using the same kind of join, but only return a few fields at first. See if you can get that working correctly.
    If you cannot, please post that simplified query with a small sampling of data, and what you would like your expected output to look like.
    (You can upload your database here, for user's to download and see directly. If you decide to do that, just be sure to remove any sensitive information from it first).

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

Similar Threads

  1. Listing Duplicates from mutiple Tables
    By nikana in forum Queries
    Replies: 3
    Last Post: 05-11-2018, 01:30 PM
  2. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  3. Query join issue?
    By ajkosby in forum Queries
    Replies: 4
    Last Post: 07-16-2013, 05:03 AM
  4. Forms using mutiple tables
    By cantord in forum Forms
    Replies: 11
    Last Post: 10-26-2012, 08:58 AM
  5. Searching mutiple tables
    By mbolster in forum Access
    Replies: 8
    Last Post: 07-06-2010, 10:16 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