Display blank(null/empty) values in ms access

Question

I have a database in MS Access, and I ran into a problem with empty values. I have 3 tables that are connected to eachother. Lets say Table1 contains people, Table 2 contains Phone numbers, and Table 3 connects table 1 and 2, having both their ID's so I could later see what person has what numbers by using the IDs.

What I want from access is that it would display a person even if he/she doesn't have a number assigned, and also a number when there are no people assigned to it.

Something like this:

Persons_name |Phone_number
--------------------------
Fred         |    123
             |    222
Anna         |

The tables look something like this:

People                People_phones         Phones
-------------         --------------        ------------
ID                    ID                    ID
Persons_name          People_ID             Phone_number
                      Phones_ID

So far I've managed to get access to show either table 1's null values or table 2's null values, but not both.

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

As E Mett indicated above, your looking for a full outer join which doesn't handle directly. Here is an example of what he's suggesting:

How do I write a full outer join query in access

JB

edited May 23 '17 at 11:49

Community

1
1

answered Jul 02 '14 at 18:50

Jim B

420
3
10

Thank you both, as soon as I can get back to Access I will try these, and confirm an answer to this question – Renet Jul 04 '14 at 17:51

score 1 · Answer 2 · answered Jul 02 '14 at 13:10

In sql jargon what you are looking for is an outer join.

This is unfortunately not available in Ms Access because it is rarely needed.

You should create two queries, one using a left join and the other with a right join.

Then use the UNION keyword to combine the results

Display blank(null/empty) values in ms access

2 Answers2