Results 1 to 2 of 2
  1. #1
    Ingis is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    8

    Query trouble (left join).

    I have following tables and relations.





    SO I have a project, that has article groups, in those groups are articles.
    The articles can be ordered for a project.

    I want to have a querry that generates a table for a selected project and group that shows all the articles and if it is ordered also shows the orderID.



    This querry only shows the articles if they are already ordered (so an order line exist).

    Code:
    SELECT Project_AGroups.PID, Project_AGroups.AGID, ArticleGroup_Articles.AID, OrderLine.OrderIDFROM Projects 
    INNER JOIN ((Articles INNER JOIN ((ArticleGroups INNER JOIN ArticleGroup_Articles ON ArticleGroups.ID = ArticleGroup_Articles.AGID) 
    INNER JOIN Project_AGroups ON ArticleGroups.ID = Project_AGroups.AGID) ON Articles.ID = ArticleGroup_Articles.AID) 
    INNER JOIN OrderLine ON Articles.ID = OrderLine.ArtID) ON Projects.ID = OrderLine.ProjectID;
    I thought switching the last inner join to a left join would solve the problem. But Access doenst agree and tells me it is not supported.

    Anyone any ideas?

    thx,
    Wesley

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to save the first part (up to the orders table) as another query then use that with a left outer join to the orders table to get your final result. Otherwise try to change all joins to outer joins Project_AGroups-->ArticleGroups -->ArticleGroup_Articles -->Articles -->OrderLine

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 7
    Last Post: 07-10-2020, 11:44 AM
  2. Left Join with Crosstab query
    By FL0XN0X in forum Access
    Replies: 13
    Last Post: 03-05-2018, 04:22 PM
  3. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  4. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 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