Compare tables

[PierpaoloScappatura]

Dear All,
I need to compare two tables with same fields.
Table1(A,B,C)
Table2(A,B,C)

Select * From Table1,Table2 
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

[nico5038]

Did you try:

Select * From Table1,Table2
Where NZ(Table1.A)=NZ(Table2.A) AND NZ(Table1.B)=NZ(Table2.B) AND NZ(Table1.C)=NZ(Table2.C)

Nic;o)

[NeoPa]

I think the following should do it for you. Let us know how you get on.

SELECT T1.*, T2.*
FROM Table1 AS T1 INNER JOIN Table2 AS T2
  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.

[FishVal]

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

[nico5038]

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)

[PierpaoloScappatura]

Did you try:

Select * From Table1,Table2
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

[PierpaoloScappatura]

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