By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,710 Members | 1,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,710 IT Pros & Developers. It's quick & easy.

Compare tables

P: 4
Dear All,
I need to compare two tables with same fields.
Table1(A,B,C)
Table2(A,B,C)
Expand|Select|Wrap|Line Numbers
  1. Select * From Table1,Table2 
  2. Where Table1.A=Table2.A AND Table1.B=Table2.B AND Table1.C=Table2.C
Query don't return a record if a field is empty in both tables and other fields are equal.

Hope someone can help me out. Thanks a bunch! I truely appreciate it.

Regards
Jun 1 '07 #1
Share this Question
Share on Google+
6 Replies

nico5038
Expert 2.5K+
P: 3,072
Did you try:

Expand|Select|Wrap|Line Numbers
  1. Select * From Table1,Table2
  2. Where NZ(Table1.A)=NZ(Table2.A) AND NZ(Table1.B)=NZ(Table2.B) AND NZ(Table1.C)=NZ(Table2.C)
Nic;o)
Jun 2 '07 #2

NeoPa
Expert Mod 15k+
P: 31,770
I think the following should do it for you. Let us know how you get on.
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.*, T2.*
  2. FROM Table1 AS T1 INNER JOIN Table2 AS T2
  3.   ON T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
The SELECT clause may be more than you need but the illustration is there for you to work from at least.
Jun 2 '07 #3

FishVal
Expert 2.5K+
P: 2,653
This is basic principe of tristate logic (True, False, Null).
Comparison operations with Null does always return False. This means that Null <> Null in any case.
You may try the following:
Instead Table1.A=Table2.A
one of the following
1) Table1.A=Table2.A OR (Table1.A Is Null AND Table2.A Is Null)
2) IIf(IsNull(Table1.A), DummyValue, Table1.A) = IIf(IsNull(Table2.A), DummyValue, Table2.A)

DummyValue may be any value but the type have to correspond to the field type, e.g. any number for Number field, any string for Text field etc.

Good Luck
Jun 2 '07 #4

nico5038
Expert 2.5K+
P: 3,072
This is basic principe of tristate logic (True, False, Null).
Comparison operations with Null does always return False. This means that Null <> Null in any case.
You may try the following:
Instead Table1.A=Table2.A
one of the following
1) Table1.A=Table2.A OR (Table1.A Is Null AND Table2.A Is Null)
2) IIf(IsNull(Table1.A), DummyValue, Table1.A) = IIf(IsNull(Table2.A), DummyValue, Table2.A)

DummyValue may be any value but the type have to correspond to the field type, e.g. any number for Number field, any string for Text field etc.

Good Luck
Thanks for your elaboration about the Null value FishVal, I proposed the NZ() function so each Null will be changed into a "not null" value and can be compared directly.

Nic;o)
Jun 2 '07 #5

P: 4
Did you try:

Expand|Select|Wrap|Line Numbers
  1. Select * From Table1,Table2
  2. Where NZ(Table1.A)=NZ(Table2.A) AND NZ(Table1.B)=NZ(Table2.B) AND NZ(Table1.C)=NZ(Table2.C)
Nic;o)
Thanks this solution work with access but don't work if I work with VB (.Net 2003) and ADO Driver
Jun 4 '07 #6

P: 4
This is basic principe of tristate logic (True, False, Null).
Comparison operations with Null does always return False. This means that Null <> Null in any case.
You may try the following:
Instead Table1.A=Table2.A
one of the following
1) Table1.A=Table2.A OR (Table1.A Is Null AND Table2.A Is Null)
2) IIf(IsNull(Table1.A), DummyValue, Table1.A) = IIf(IsNull(Table2.A), DummyValue, Table2.A)

DummyValue may be any value but the type have to correspond to the field type, e.g. any number for Number field, any string for Text field etc.

Good Luck
Thanks this solution work
Jun 4 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.