two tables multiple rows for same occurence

[tcbc]

I have on one table1

123 PA
124 MD

on another table2 I have

123 PA
123 MD and

124 PA
124 MD.

I want occurences where there is no match on table 2.
I am getting

123 PA MD
123 MD PA
124 PA MD
124 MD pa.

I should be getting none because there is a match

 

[IanWaterman]

Join the two tables with left outer joins using the two fields you have listed above.

Assuming column containing 123 and 124 is Field1. and table 2 is the right hand table

In select expert

isnull(table2.field1)

Ian

 

[tcbc]

Well ok

table1
123 pa
123 va

Table 2
123 pa.

i want it to come back

Where table 2 does not have the VA.
I should see
table1
123 VA

Because there is no VA in table 2

 

[Madawc]

Change the test to not IsNull.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10