Simple Problem with queries

[H0bbes]

Hello All,
I am fairly new to access, and have only a very cursory SQL query knowledge and I need some help.

I have two tables that have a single relationship. I have one table lets refer to it as "Main". I need to retain every record from main, but I am trying to "mux" in the information from the secondary table. If I run a simple query, combining the rows from main I need with the rows from secondary I need, all I get is the records from main that contain a value for the row that is used in the relationship. About 1/3 of main's records have no value, so the query generates a datasheet with about 2/3 of main's data.

I came up with a bad solution of just using a simple sql update statement to replace isNull isBlank with sometext and then just created a record in the secondary table that matches sometext. I realize this can't be the optimal way to do this, what would be?

My end goal is to condense all of the tables in this database into a flat file that I will eventually turn into a excel CSV file. So, hacky solutions that would not hold up for a long time in a real database don't bother me because I am really only doing this once to get the info out of the database.

Thank You,
H0bbes

[bard777]

You just need a RIGHT JOIN (or LEFT JOIN) instead of an INNER JOIN.

In the Query Design window for your query right click on the line between [Main] and [Mux] and choose properties. Then choose the option that say ALL RECORDS FROM [Main]. If you look at the SQL view it should look like the code below:

SELECT Main.f_1, Main.f_2, mux.d_1
FROM mux RIGHT JOIN Main ON mux.mux_ID = Main.Main_ID;

or like this if you use a LEFT JOIN:

SELECT Main.f_1, Main.f_2, mux.d_1
FROM Main LEFT JOIN mux ON Main.Main_ID = mux.mux_ID;