Newbie question - How to show all records in a query

[ryanjkirk]

Hi,

I'm sure this is a newbie question, I just can't find the answer anywhere. In table A I have a large number of records. Table B is much smaller but they are joined by primary keys. When I view the query, I can see the correct data from each table, but it only shows the common records. I would like it to show all records in column A, even though many columns will be empty. How could I do this?

Example:

Table A
1 Apple Red
2 Banana Yellow
3 Grape Purple
4 Lettuce Green
5 Orange Orange
6 Radish Red

Table B
1 Apple Red 1,000
4 Lettuce Green 250
6 Radish Red 400

Result of query would be:
1 Apple Red 1,000
2 Banana Yellow
3 Grape Purple
4 Lettuce Green 250
5 Orange Orange
6 Radish Red 400

Thank you.

 

[MSAccessRookie]

If you select the columns that you want from TableA LEFT JOIN TableB ON TableA.KeyName = TableB.KeyName, you will get all records in table A joined with any matches from Table B. Any missing records from TableB will have Null for a value in the resulting recordset

 

[ryanjkirk]

Is that something I type in somewhere? How do I do what you are saying?

 

[Alansidman]

In the upper left corner when you have your query open, you will see a triangle, and a drop down arrow next to it. Click on the drop down arrow, select SQL and amend the SQL code there. Replace the word Inner with Left.

Suggest you do a little reading on SQL to better understand how queries work. This is the guts of queries.

Look here-->http://www.fontstuff.com/access/index.htm

Alan

 

[ryanjkirk]

Thank you for your help. I did replace the word INNER with LEFT, but nothing seemed to change. I suppose I will need to learn more about SQL in order to achieve proficiency.

 

[Alansidman]

Post your SQL statement on the forum and what your expected results would be. We'll look at it and try to help you.

Alan

 

[MSAccessRookie]

I imagine that the SQL should look something like this:

SELECT TableA.FruitName, TableA.FruitColor, TableB.FruitCount
FROM TableA LEFT JOIN TableB ON TableA.FruitName = TableB.FruitName;

 

[ryanjkirk]

I deleted the query and started over. It works great! Thank you very much!