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
+ 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 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 Jun 1 '07 #1
Share this Question
6 Replies

 Expert 2.5K+ P: 3,072 Did you try: Expand|Select|Wrap|Line Numbers 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) Jun 2 '07 #2

 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 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. Jun 2 '07 #3

 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

 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 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 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.