C
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));
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));