criteria matching with table is null

[perryche]

I am not sure if I am asking the right question in the subject here.
But, here is my problem. I have 5tables/queries with various data
like below.

Table1: CustomerID1, Field1, Field2
Table2: CustomerID1, Field5, Field6
Table3: CustomerID2, Field7, Field8 (customerID1 does not exist in
the table)
....

So, how do I create query to "skip" over table3 and show me if
criterias are met in table1 & table2, then show me the customer?
When I do a IsNull([CustomerID] with the table, it does not skip over,
it displays other customerIDs.
Here are the actual SQL:
SELECT Trim([ELastName] & ", " & [EFirstName]) AS FullName,
TNameInfo.ELastName, TNameInfo.EFirstName, TNameInfo.CName,
TFellowship.Initial, TFellowship.FChinese, Ability.NameID,
Ability.QID, Ability.TQuestion.Description, Ability.CodeID,
Ability.TCode.Description, Experience.QID,
Experience.TQuestion.Description, Experience.CodeID,
Experience.TCode.Description, Heart.QID, Heart.TQuestion.Description,
Heart.CodeID, Heart.TCode.Description, Personality.QID,
Personality.TQuestion.Description, Personality.CodeID,
Personality.TCode.Description, SpiritualGift.QID,
SpiritualGift.TQuestion.Description, SpiritualGift.CodeID,
SpiritualGift.TCode.Description
FROM TFellowship INNER JOIN (((((SpiritualGift INNER JOIN Personality
ON SpiritualGift.NameID = Personality.NameID) INNER JOIN Heart ON
Personality.NameID = Heart.NameID) INNER JOIN Experience ON
Heart.NameID = Experience.NameID) INNER JOIN Ability ON
Experience.NameID = Ability.NameID) INNER JOIN TNameInfo ON
SpiritualGift.NameID = TNameInfo.NameID) ON TFellowship.FellowshipID =
TNameInfo.FellowshipID
WHERE (((Ability.QID) Like IIf(IsNull([Forms]![0F_All]![AB1])=True,"*",
[Forms]![0F_All]![AB1])) AND ((Ability.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![AB2])=True,"*",[Forms]![0F_All]![AB2])) AND
((Experience.QID) Like IIf(IsNull([Forms]![0F_All]![Ex1])=True,"*",
[Forms]![0F_All]![Ex1])) AND ((Experience.CodeID) Like
IIf(IsNull([Forms]![0F_All]![Ex2])=True,"*",[Forms]![0F_All]![Ex2]))
AND ((Heart.QID) Like IIf(IsNull([Forms]![0F_All]![Pa1])=True,"*",
[Forms]![0F_All]![Pa1])) AND ((Heart.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![Pa2])=True,"*",[Forms]![0F_All]![Pa2])) AND
((Personality.QID) Like IIf(IsNull([Forms]![0F_All]![Pe2])=True,"*",
[Forms]![0F_All]![Pe2])) AND ((Personality.CodeID) Like
IIf(IsNull([Forms]![0F_All]![Pe1])=True,"*",[Forms]![0F_All]![Pe1]))
AND ((SpiritualGift.QID) Like IIf(IsNull([Forms]![0F_All]!
[SG1])=True,"*",[Forms]![0F_All]![SG1])) AND ((SpiritualGift.CodeID)
Like IIf(IsNull([Forms]![0F_All]![SG2])=True,"*",[Forms]![0F_All]!
[SG2])));

Any hint will be appreciated.

Perry

[Pachydermitis]

On Jan 11, 3:18*pm, [e-mail] wrote:

I am not sure if I am asking the right question in the subject here.
But, here is my problem. *I have 5tables/queries with various data
like below.

Table1: CustomerID1, Field1, Field2
Table2: CustomerID1, Field5, Field6
Table3: CustomerID2, Field7, Field8 *(customerID1 does not exist in
the table)
...

So, how do I create query to "skip" over table3 and show me if
criterias are met in table1 & table2, then show me the customer?
When I do a IsNull([CustomerID] with the table, it does not skip over,
it displays other customerIDs.

Here are the actual SQL:
SELECT Trim([ELastName] & ", " & [EFirstName]) AS FullName,
TNameInfo.ELastName, TNameInfo.EFirstName, TNameInfo.CName,
TFellowship.Initial, TFellowship.FChinese, Ability.NameID,
Ability.QID, Ability.TQuestion.Description, Ability.CodeID,
Ability.TCode.Description, Experience.QID,
Experience.TQuestion.Description, Experience.CodeID,
Experience.TCode.Description, Heart.QID, Heart.TQuestion.Description,
Heart.CodeID, Heart.TCode.Description, Personality.QID,
Personality.TQuestion.Description, Personality.CodeID,
Personality.TCode.Description, SpiritualGift.QID,
SpiritualGift.TQuestion.Description, SpiritualGift.CodeID,
SpiritualGift.TCode.Description
FROM TFellowship INNER JOIN (((((SpiritualGift INNER JOIN Personality
ON SpiritualGift.NameID = Personality.NameID) INNER JOIN Heart ON
Personality.NameID = Heart.NameID) INNER JOIN Experience ON
Heart.NameID = Experience.NameID) INNER JOIN Ability ON
Experience.NameID = Ability.NameID) INNER JOIN TNameInfo ON
SpiritualGift.NameID = TNameInfo.NameID) ON TFellowship.FellowshipID =
TNameInfo.FellowshipID
WHERE (((Ability.QID) Like IIf(IsNull([Forms]![0F_All]![AB1])=True,"*",
[Forms]![0F_All]![AB1])) AND ((Ability.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![AB2])=True,"*",[Forms]![0F_All]![AB2])) AND
((Experience.QID) Like IIf(IsNull([Forms]![0F_All]![Ex1])=True,"*",
[Forms]![0F_All]![Ex1])) AND ((Experience.CodeID) Like
IIf(IsNull([Forms]![0F_All]![Ex2])=True,"*",[Forms]![0F_All]![Ex2]))
AND ((Heart.QID) Like IIf(IsNull([Forms]![0F_All]![Pa1])=True,"*",
[Forms]![0F_All]![Pa1])) AND ((Heart.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![Pa2])=True,"*",[Forms]![0F_All]![Pa2])) AND
((Personality.QID) Like IIf(IsNull([Forms]![0F_All]![Pe2])=True,"*",
[Forms]![0F_All]![Pe2])) AND ((Personality.CodeID) Like
IIf(IsNull([Forms]![0F_All]![Pe1])=True,"*",[Forms]![0F_All]![Pe1]))
AND ((SpiritualGift.QID) Like IIf(IsNull([Forms]![0F_All]!
[SG1])=True,"*",[Forms]![0F_All]![SG1])) AND ((SpiritualGift.CodeID)
Like IIf(IsNull([Forms]![0F_All]![SG2])=True,"*",[Forms]![0F_All]!
[SG2])));

Any hint will be appreciated.

Perry

Perry,
I am not sure that I fully understand your question, but try this.
Create a query for table3 that limits it the way you want to - taking
out or leaving in nulls. Then use that query instead of the table in
the above query.
HTH
P

[perryche]

P,
I am sorry for being so confusing. I just don't know how to
ask.,.. let me give it one more try: (In general, this is a problem
because the table was inproperly designed in the first place, that is
why I am trying to "program" around it.)

I am simplifying the situation here:

Table1: CustomerID, Field1
Table2: CustomerID, Field2
Table3: CustomerID, Field3

Then I have a blank form with 3 fields: FormField1, FormField2 &
FormField3

If Table1Field1=FormField1 and Table2Field2=FormField2 and
Table3Field3=FormField3, then display the Common Customer ID (Who is
it?)

Assuming the first 2 criteria are met for CustomerID = 11, but the
third criteria, in table 3, since CutomerID #11 does not even exist, I
would like for the query result to include CustomerID #11. (Because
the info was missing does not mean it may not match, we just fail to
create a customerID #11 for table3.) In other words, how do I not
exclude results CustomerID #11 from the result even if Table3 does not
contain customerID #11's data?

FYI: The above tables are actual queries (everything is built into
one big table, not so good design in the beginning by someone else).
I am just trying to savage this. Hope this help a little.

Perry

[Pachydermitis]

On Jan 12, 7:01*pm, [e-mail] wrote:

P,
* * *I am sorry for being so confusing. *I just don't know how to
ask.,.. let me give it one more try: *(In general, this is a problem
because the table was inproperly designed in the first place, that is
why I am trying to "program" around it.)

I am simplifying the situation here:

Table1: CustomerID, Field1
Table2: CustomerID, Field2
Table3: CustomerID, Field3

Then I have a blank form with 3 fields: FormField1, FormField2 &
FormField3

If Table1Field1=FormField1 and Table2Field2=FormField2 and
Table3Field3=FormField3, then display the Common Customer ID (Who is
it?)

Assuming the first 2 criteria are met for CustomerID = 11, but the
third criteria, in table 3, since CutomerID #11 does not even exist, I
would like for the query result to include CustomerID #11. *(Because
the info was missing does not mean it may not match, we just fail to
create a customerID #11 for table3.) * In other words, how do I not
exclude results CustomerID #11 from the result even if Table3 does not
contain customerID #11's data?

FYI: *The above tables are actual queries (everything is built into
one big table, not so good design in the beginning by someone else).
I am just trying to savage this. *Hope this help a little.

Perry

if CustomerID is unique in each table (no duplictes) then you just
neet to do an outer join (left or right).
SELECT * FROM (table1 INNER JOIN table2 ON
table1.customerid=table2.customerid) LEFT JOIN table3 on
table1.customerid=table3.customerid
WHERE table1.customerID=11
When you create your joins, in the query editor, double click on the
line that joins the two tables and you can choose an outer join.

if CustomerID is not unique and you are using other criteria to limit
your query, you will have to do some other stuff.

[perryche]

This does not work too well for me, because like I described, the main
problem is some table do not have that particular customerID at all.
But I do not want to exclude those customerID that does not exist in a
particular table. What I am seeing is when I just search 1 query, it
yields 6 records; but when I search thru this inner/outer join as you
suggested, it yields only 4 records (because 1 of the tables do not
contain that particular customerID). Thanks for your help, any other
thoughts?

Perry

[Pachydermitis]

On Jan 15, 6:11*pm, [e-mail] wrote:

This does not work too well for me, because like I described, the main
problem is some table do not have that particular customerID at all.
But I do not want to exclude those customerID that does not exist in a
particular table. *What I am seeing is when I just search 1 query, it
yields 6 records; but when I search thru this inner/outer join as you
suggested, it yields only 4 records (because 1 of the tables do not
contain that particular customerID). *Thanks for your help, any other
thoughts?

Perry

You need to create two separate queries and put one within the other.
Create a query on table1 and 2 that has the criteria in it (query1)
and then create another query (query2) that does a left join between
query1 and 2
You can also do this in a derived table if you are daring enough.

Query1:
SELECT * FROM (table1 INNER JOIN table2 ON
table1.customerid=table2.customerid)
WHERE table1.customerID=11

Query2:
Select * FROM query1 LEFT JOIN table3 on
query1.whatever=table3.whatever

HTH