Pulling data that is null (front and back end)

[marie]

I am unsuccessful in pulling data using my queries, so I am hoping that you
could help me.

This is the situation:
I've created 2 link tables. Data on these tables are coming from an
oracle_db (AS400).
This is a work-related project, so, for the purpose of privacy, I will
provide you with a 'dummy' example of these tables:

Table 1: Residential Info
Contains: Addresses
Names of Grandparents
Names of Parents
Names of Child(ren) - pk

Table 2: Tenant Info
Contains: Names of Grandparents
Names of Parents
Names of Child(ren) - pk

Keep in mind that not all child(ren) have parents nor grandparents on these
tables and visa versa.

What I want to pull are these:
(1) Which residential addresses are unoccupied and occupied?
(2) Which child(ren) from Table 2 are not occupying a residence? And if no
child(ren), which parent from Table 2 are not occupying a residence?

For some reason, when I pulled the data (using the link table) to create
Table 1, it only gave me info on the residential addresses that are occupied,
even though on the system, I was able to confirm that there is a long list of
addresses that are not tied to any resident.

My question is, why isn't my link table pulling the above data (i.e. why is
it excluding the addresses that are not tied to any occupant)? And how do I
go about pulling the info that I need?

I am using Access 2000.

I am also not sure why some of my records are showing "#deleted#" when the
other records in the same fields have values on them.

Your help is greatly appreciated.
Marie

 

[Michel Walsh]

Hi,


Make a first query:


SELECT a.GrandParents, a.Parents, a.Children FROM residential
UNION
SELECT b.GrandParents, b.Parents, b.Children FROM tenant


Have that query (or make an indexed table out of it), for this example, be
called Q1.


SELECT q1.*, a.*, b.*
FROM (q1 LEFT JOIN residential As a ON q1.grandParent=a.grandParent AND
q1.parents=a.parents AND q1.Children=a.Children)
LEFT JOIN tenant AS b ON q1.grandParent=b.grandParent AND
q1.parents=b.parents AND q1.Children=b.Children




a.Children and b.Children won't be null if they are present in both tables,
else, if not, one will be null (corresponding to the table where there was
no match in the other table).



Hoping it may help,
Vanderghast, Access MVP