none
Show all the records in a set of related tables!

    Question

  • Hi,

    I am new in Access. I have created a database and tried to  follow the most important rule in databases that says "DO NOT REPEAT DATA IN SEVERAL PLACES EVER!". Thus, I have related a set of the tables as shown in the attached image. I want to get a report from tbl_allPins including ones that are sold or not, ones that have a comment assigned to or ones have not. The output always gives me the records that are sold and have a comment because of the structure of the relational tables. BTW, I want to see all available data about a PIN in the tbl_allPins such as comments or vendors or usages in tbl_cards_CDR. Is there anyway that I can see all the records even IFDatabase structurethey have no comment ID or invoice ID? Please look at the image to see all my database  structure.

    Thank you in advance.

    Thursday, December 19, 2013 7:31 PM

Answers

  • You'll also have to double-click the joins from tbl_comments to tbl_tbl_callReason and tbl_satisfaction, and for both select the option to include ALL records from tbl_Comments.

    Next, double-click the join line from tbl_allPints to tbl_invoixes and select the option to include ALL records from tbl_allPins.

    Finally, double-0click the join line from tbl_invoices to tbl_vendors and select the option to include ALL records from tbl_invoices.

    All join lines will then point away from tbl_allPins.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Zarinsun Friday, December 20, 2013 5:44 PM
    Friday, December 20, 2013 10:47 AM

All replies

  • When you create a query and add tbl_allPins and tbl_comments, Access will automatically join them on comment_ID.

    Double-click the join line. Select the option to include ALL records from tbl_allPins and only those records from tbl_Comments where the joined fields are equal.

    The query will now return all records from tbl_allPins even if their comment_ID is empty.

    You can do the same for other joins.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 19, 2013 9:04 PM
  • Thanks for the reply.

    I did what you explained and the error message was shown as follows:

    "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

    I could not understand why it is ambiguous and what is the solution. Can you please help me to understand this.

    Thank you.

    Friday, December 20, 2013 1:54 AM
  • You'll also have to double-click the joins from tbl_comments to tbl_tbl_callReason and tbl_satisfaction, and for both select the option to include ALL records from tbl_Comments.

    Next, double-click the join line from tbl_allPints to tbl_invoixes and select the option to include ALL records from tbl_allPins.

    Finally, double-0click the join line from tbl_invoices to tbl_vendors and select the option to include ALL records from tbl_invoices.

    All join lines will then point away from tbl_allPins.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Zarinsun Friday, December 20, 2013 5:44 PM
    Friday, December 20, 2013 10:47 AM
  • Thank you so much Hans,

    You are awesome. It works now. 

    I wish you the best and happy new year.

    Friday, December 20, 2013 5:43 PM