Outer left join leaves out null values

[mschles4695]

I am joining two tables that have 3 fields in common and all 3 are needed to match the records uniquely; the three joins are Access left outer joins. However, if in table 1 one of the field is null, a record will not be returned eventhough there are matching values in the other two fields. I am joining an employee table with location information in a building field, floor field and room field (all text) to a Rooms table with the same three fields. In this case the employee has values in all fields but the Rooms table has a null in the floor field for the record with the same building and room IDs. So, since I am counting the number of records returned, in this case I am missing a record.



IS THERE A WAY TO GET AROUND THIS?????? HELP PLEASE!!!!

[ConneXionLost]

You need to properly normalize your Rooms table with unique primary key and use that instead of depending on a compound key with missing values.

In other words, instead of this:

Building	Floor	Room
Red	Main	101
Red	Main	102
Red	Second	201
Blue	Main	101
Blue	Second	201

use this:

RoomID	Building	Floor	Room
1	Red	Main	101
2	Red	Main	102
3	Red	Second	201
4	Blue	Main	101
5	Blue	Second	201

The employee table would only use the RoomID field.

Cheers,