By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Simple Problem with queries

P: 1
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
May 12 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Main.f_1, Main.f_2, mux.d_1
  2. FROM mux RIGHT JOIN Main ON mux.mux_ID = Main.Main_ID;
or like this if you use a LEFT JOIN:

Expand|Select|Wrap|Line Numbers
  1. SELECT Main.f_1, Main.f_2, mux.d_1
  2. FROM Main LEFT JOIN mux ON Main.Main_ID = mux.mux_ID;

Share this Question
Share on Google+
1 Reply


bard777
P: 23
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Main.f_1, Main.f_2, mux.d_1
  2. FROM mux RIGHT JOIN Main ON mux.mux_ID = Main.Main_ID;
or like this if you use a LEFT JOIN:

Expand|Select|Wrap|Line Numbers
  1. SELECT Main.f_1, Main.f_2, mux.d_1
  2. FROM Main LEFT JOIN mux ON Main.Main_ID = mux.mux_ID;
May 12 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.