Compare one field in a table with a 3 fields in another table

[geronimo_me]

Hi,

I have a small problem I am trying to check if field1 in Table1 matches
Field2, Field3 or Field4 in Table2: ie

Record 1
Table1
Field1 = ABC

Table2
Field1 = 44g
Field2 = ABC (this is matching field)
Field3 = rgrg

Record 2
Table1
Field1 = 123

Table2
Field1 = 123 (this is matching field)
Field2 = xx
Field3 = asd

Record 3
Table1
Field1 = xxx

Table2
Field1 = asd
Field2 = 123
Field3 = xxx (this is matching field)

Record 4
Table1
Field1 = 555

Table2
Field1 = ABC
Field2 = dsf
Field3 = ""

No matching field in Record4

As you can see the "matching" field could be in Field1, 2 or 3 in
Table2. I want to display all the records were there is no matching
field(s) in Table2 at all (whether in Field1, 2 or 3 in Table2)as in
record 4 (this is what i would like to be displayed)

Hope this maker sense

Thankyou very much

Flick. x

[David Lloyd]

One approach would be a query like the following:

SELECT Test1.*
FROM Test1 LEFT JOIN TEST2 ON (Test1.Field1 = TEST2.Field1) OR (Test1.Field1
= TEST2.Field2) OR (Test1.Field1 = TEST2.Field3)
WHERE Test2.Field1 is null;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ge*********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi,

I have a small problem I am trying to check if field1 in Table1 matches
Field2, Field3 or Field4 in Table2: ie

Record 1
Table1
Field1 = ABC

Table2
Field1 = 44g
Field2 = ABC (this is matching field)
Field3 = rgrg

Record 2
Table1
Field1 = 123

Table2
Field1 = 123 (this is matching field)
Field2 = xx
Field3 = asd

Record 3
Table1
Field1 = xxx

Table2
Field1 = asd
Field2 = 123
Field3 = xxx (this is matching field)

Record 4
Table1
Field1 = 555

Table2
Field1 = ABC
Field2 = dsf
Field3 = ""

No matching field in Record4

As you can see the "matching" field could be in Field1, 2 or 3 in
Table2. I want to display all the records were there is no matching
field(s) in Table2 at all (whether in Field1, 2 or 3 in Table2)as in
record 4 (this is what i would like to be displayed)

Hope this maker sense

Thankyou very much

Flick. x