select query based on 2 tables

[vassa]

Hello!
I have a Select Query based on 2 tables with one-many relationship, let's call one table "One" and the other one "Many" based on the sides of the relationship.
How to make this query to return all records from the "One" table even if the "Many" table doesn't have related records? Right now, query shows only those records from "One" table that have related records in the "Many" table. I need it to show ALL records from "One" table, just like it would if I were to remove the "Many" table from the query. I appreciate any help with this.

 

[vbaInet]

Copy and paste the sql statement here and we'll show you how.

 

[vassa]

Copy and paste the sql statement here and we'll show you how.

SELECT tblProposalNew.*
FROM tblProposalNew INNER JOIN tblOffsetWell ON tblProposalNew.ProposalID = tblOffsetWell.ProposalID;

tblProposalNew is the "One" table and tblOffsetWell is the "Many" table. Thank you!

 

[vbaInet]

SELECT tblProposalNew.*
FROM tblProposalNew LEFT JOIN tblOffsetWell ON tblProposalNew.ProposalID = tblOffsetWell.ProposalID;

ONce you've tested it and seen the result, right-click the line that joins both tables and click Join Properties. You will notice that the second option is now selected. Play around with the options to get acquainted.

 

[vassa]

SELECT tblProposalNew.*
FROM tblProposalNew LEFT JOIN tblOffsetWell ON tblProposalNew.ProposalID = tblOffsetWell.ProposalID;

ONce you've tested it and seen the result, right-click the line that joins both tables and click Join Properties. You will notice that the second option is now selected. Play around with the options to get acquainted.

Thank you very much, vbaInet! I actually messed with Join Properties prior to posting here, it didn't work for me in the first plce because my actual query contains a lot of tables with one-many relationship and access was giving me an error "SQL statement can not be executed because it contains ambiguous joins..."

I created a separate Query which included the original Query (without that extra "Many" table) and I added the "Many" table with the LEFT JOIN like you recommended. Works like a charm!

I greatly appreciate your help.

 

[vbaInet]

Happy to help!

You can get away with the ambiguous joins problem by also making sure that all the arrows point outwards from the main table. So if you had this kind of join:

Table F ---- Table A ---- > Table B ---- Table C

where Table A is the main table you want to pull all the records from, the red chevron indicates a LEFT JOIN from A to B, and those without an arrow indicate an INNER JOIN. For it to work, the joins must point this way:

Table F <---- Table A ---- > Table B ----> Table C

You can see it's pointing outwards from the main table, A.