Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: report that joins 3 tables

    I have a report that joins 3 tables, I would like to have all informant from table 1 and 2 even there is no information in table 3.
    If I set the join to Left Join, a blank record from table 2 and 3 will also be printed.

    Any solution?

    Thanks

    Tom

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    When you defined the query how did you define the join or link between table 2 & 3?

    If you cretaed a query for the report, open the query designer (or data source of the report), select the link (the line) between table 2 & 3 and you should see a dialog pop up showing 3 options. Change the link to all from table 2 and table 3 where matched. precise wording will vary (Its too early to start work here yet to check Access). From memory this will be the second radio button.
    HTH

    In essence you can modify the way the joins are performed int hequery designer very easily, as opposed to writing the SQL directly.

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    Let’s say I have tables’ customer, order, and order detail.

    The 3 tables are joint in the query using the LEFT Join

    I would like to have all the customer printed, even there is no order.

    In addition, every customer also has a few other sub reports. Like all their employees and all of their working days.

    However, if the customer doesn’t have an order that has some order details, none of the other customer sub reports will print.

    Thanks
    Tom

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where are the sub reports located? If they're in a detail section, they will not print, as there are no detail records.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need to alter the join criteria - the easiest wya to talk you through it is through the query designer. Alter the link between table2 & table 3 so that it includes all rows from table2 that match the criteria AND all rows from table 2 where they match table 3 (like I said radio button 2 on the pop up dialog)

  6. #6
    Join Date
    Jan 2004
    Posts
    115
    Where are the sub reports located? If they're in a detail section, they will not print, as there are no detail records.
    I created 2 customer group headers
    and one Order header
    and one Order detail header

    I added the customer sub reports into the customer group headers

    Also, I would like to have all customer sub reports even there is no order at all
    I also would like to see all orders even there is no order details for that order.

    The join between customer and order is set to include all information from table customer.
    The join between order and order detail is set to include all information from table order.

    What else can I configure so it will include all above records?

    Thanks
    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •