Conditional "Is Null" Query Criteria

[veaux]

Question deals with linking tables in queries. I'm not a code writer
so use the GUI for all my queries.

Table 1 - Master
Table 2 - Sub1
Table 3 - Sub 2

All 3 tables have the same key field.

I'm trying to find all the records that are in "Master", that are not
in Sub1 or Sub2. Just doing that I have but doing show me all in
"Master" and using Join Property 2 and Is Null for both Sub1 and Sub2.

What I really want to do though is show me all records in "Master"
that are not in Sub1 or Sub2, but only when Sub1.Fieldname = "Jones".
If I put "Jones" in the criteria section of query, I get nothing. I
know I could create another query for Sub1.Fieldname = "Jones" and do
the above off of that, but I'd rather not create the add'l queries.

I haven't understood how you can do the "Is not in table X, when only
looking at Table X records where Fieldname = "Jones".

Hope the above makes sense?

[Rich P]

YOu can try the following if Master, Sub1, Sub2 contain the exact same
fields (where this example is using the * wildcard)

select tA.* From Master tA Left Join
(
SELECT t1.*
FROM Mater t1 INNER JOIN Sub1 t2 ON t1.keyfld = t2.keyfld
union all
SELECT t1.*
FROM Master t1 INNER JOIN Sub2 t2 ON t1.keyfld = t2.keyfld
) tB on tA.keyfld = tB.fld
where tb.keyfld is null

If the tables are not exactly the same then you have to list the fields
you want to display. The catch if the tables are not all the same is
that for the Union All part you have to list the same number of fields
for each select statement. Just change keyfld to the name of your
actual keyfield. The t1, t2, tA, tB are just alias's for each of the
tables. t1 refers to Master in the Subquery, t2 = Sub1 in the first
select inside the subquery, and t2 = Sub2 in the 2nd select in the
subquery, then tA again refers to Master in the outer query, and tB
refers to the Subquery (thus the alias).

This will return all the records in Master which are not contained in
either of Sub1 or Sub2.
So if the tables are basically all the same - just different data then
just copy and paste the sql above in the Sql window of the Query Builder
and replace the table names wiht the

Rich

*** Sent via Developersdex http://www.developersdex.com ***