Nulls in a query

  • Thread starter Thread starter Hal2604 via AccessMonster.com
  • Start date Start date
H

Hal2604 via AccessMonster.com

I have a query where I have 2 tables
Table 1
A
B
C

Table 2
A
B
C

I am comparing table1!A & table2!B and I want the query to kick out any
record where the two fields don't match. My problem comes in that A or B
could contain nulls and if it does these are not kicking out if the other
field contained data.

Any help would be appreciated. Thanks. Holly.
 
Conditional Expressions in ON or WHERE clauses:

A record will be returned
- only if conditional expression evaluates to -1 (TRUE)

A record will not be returned
- if conditional expression evaluates to 0 (FALSE)
- or if conditional expression evaluates to Null


======================================
Return Records WHERE 2 Fields Do Not Match
======================================

Null AND True = Null
Null AND False = False
Null AND Null = Null

t1.f1 t2.f2 t1.f1<>t2.f2
----- ------ ----------------------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null Null do not return {a, Null}
Null b Null do not return {Null, b}
Null Null Null do not return {Null, Null}



t1.f1 t2.f2 Nz(t1.f1<>t2.f2, -1)
----- ------ -----------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null -1 return {a, Null}
Null b -1 return {Null, b}
Null Null -1 return {Null, Null}


Nz(t1.f1<>t2.f2, -1)
AND
NOT (t1.f1 IS NULL
t1.f1 t2.f2 AND t2.f2 IS NULL)
----- ------ ---------------------------------------
a b -1 AND -1 = -1 return {a, b}
a a 0 AND -1 = 0 do not return {a, a}
a Null -1 AND -1 = -1 return {a, Null}
Null b -1 AND -1 = -1 return {Null, b}
Null Null -1 AND 0 = 0 do not return {Null, Null}


======================================
Return Records WHERE 2 Fields Do Match
======================================

Null OR True = True
Null OR False = Null
Null OR Null = Null

t1.f1 t2.f2 t1.f1=t2.f2 Nz(t1.f1=t2.f2, 0)
----- ------ ------------------------------ -----------------------
a b 0 do not return {a, b} 0
a a -1 return {a, a} -1
a Null Null do not return {a, Null} 0
Null b Null do not return {Null, b} 0
Null Null Null do not return {Null, Null} 0

t1.f1 t2.f2 (t1.f1=t2.f2) OR (t1.f1 IS NULL AND t2.f2 IS NULL)
----- ------ ----------------------------------------------------------
a b 0 OR 0 = 0 do not return {a, b}
a a -1 OR 0 = -1 return {a, a}
a Null Null OR 0 = Null do not return {a, Null}
Null b Null OR 0 = Null do not return {Null, b}
Null Null Null OR -1 = -1 return {Null, Null}

t1.f1 t2.f2 (Nz(t1.f1,'zzzz') = Nz(t2.f2,'zzzz'))
----- ------ ------------------------------------------
a b ('a' = 'b') = 0 do not return {a, b}
a a ('a' = 'a') = -1 return {a, a}
a Null ('a' = 'zzzz') = 0 do not return {a, Null}
Null b ('zzzz' = b) = 0 do not return {Null, b}
Null Null ('zzzz' = 'zzzz') = -1 return {Null, Null}



"Hal2604 wrote
 
It might seem somewhat backwards, but I believe you could do this as a
two-step process.

First, find all the records that DO match (where T1!A = T2!A and T1!B =
T2!B). Then take that query as a starting point and find the records in ???
(you don't say which one is your "master") that are not in the first query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
good!

when Jeff didn't mention anything
about my sageness, I wondered
if I misread. 8-)

"Hal2604 wrote
 
Back
Top