Record not showing if no record in many table

[cp2599]

I'm new to access and I think I'm missing a major piece of information
because I get different results based on whether the attribute in the
where clause is looking for a null value versus an actual value. I
want to display all families with the latest application number (if
one exists) and the house assignment status (if one exists).

The family record still appears even if no record exists in the
FamilyHouse table, but the family record does not appear if there is
no record in the FamilyApplication table. Why does a null value in
the where clause have a different affect than an actual value in the
where clause ... or do I have my tables not defined correctly.

Tables:
many-many relationship set up between Family and House (type 2) that
is resolved into the table FamilyHouse
1-many relationship set up between Family & FamilyApplication (type 2)

Family attributes:
key to Family is idnFamilyID
chrFamilyName

FamilyApplication attributes:
key to FamilyApplication is idnFamilyApplicationID
foreign key in FamilyApplication is lngzFamilyID
intApplicationNo
blnLatestApplication

FamilyHouse attributes:
key to FamilyHouse is lngzHouseID, lngzFamilyID and dtmStartDate
dtmEndDate
chrAssignmentStatus

Some records exist in Family with no records in FamilyApplication.
Some records exist in Family with no records in FamilyHouse
I want all records in family with data from selected records in the
FamilyApplication and FamilyHouse..

Query results only shows records that exist in both Family and Family
Application.

Query:
SELECT tblFamily.chrFamilyName, tblFamilyHouse.chrAssignmentStatus,
tblFamilyApplication.intApplicationNo
FROM (tblFamily LEFT JOIN tblFamilyHouse ON tblFamily.idnFamilyID =
tblFamilyHouse.lngzFamilyID) LEFT JOIN tblFamilyApplication ON
tblFamily.idnFamilyID = tblFamilyApplication.lngzFamilyID
WHERE (((tblFamilyHouse.dtmEndDate) Is Null) AND
((tblFamilyApplication.blnLatestApplication)=-1));

 

[Ken Snell [MVP]]

When you put a filter criterion on a table field that is in a table that is
on the right side of a LEFT JOIN, you negate the LEFT JOIN and turn it into
an INNER JOIN (note: this does not apply when you're testing for a NULL
value in a field in the table on the right side of a LEFT JOIN, as that is
how you find records in the left table that are not in the right table).
This particular criterion is the culprit:
tblFamilyApplication.blnLatestApplication=-1

If you want this criterion to be applied to the tblFamilyApplication table
before you join it in the query, you need to create a separate query that
does that filtering first:

qryFamilyApplicationEqualMinus1
------------------------------------
SELECT * FROM tblFamilyApplication
WHERE blnLatestApplication = -1;


Then change your original query to this:

SELECT tblFamily.chrFamilyName, tblFamilyHouse.chrAssignmentStatus,
tblFamilyApplication.intApplicationNo
FROM (tblFamily LEFT JOIN tblFamilyHouse ON tblFamily.idnFamilyID =
tblFamilyHouse.lngzFamilyID) LEFT JOIN qryFamilyApplicationEqualMinus1 ON
tblFamily.idnFamilyID = qryFamilyApplicationEqualMinus1.lngzFamilyID
WHERE tblFamilyHouse.dtmEndDate) Is Null;