data in one table no in other 1

[bn2hunt]

I am fairly new to the sql side of access I've always been able to get the data I wanted by using the design screens but I don't think I can get this data that easily. I am trying to write a query that will give me every record where the key does not exist in the other table.

table 1 table 2
11111 11111
22222 0tst7
33333 33333
44444 1tst8
55555
55556
66666

I want to return every record in table 2 that doesn't exist in table 1
0tst7 and 1tst8

This is what I have so far
SELECT [table1].field1
FROM [table1] INNER JOIN table2 ON [table1].field1 <> [table2].Field1

This is no where near where I want to be as it return 500,000 + records from a possible 161. So no I am just really confused.

Thanks for any help you might be able to provide

DDNWolff

 

[SuicidED]

Hi
SELECT DISTINCTROW Table2.field1
FROM Table2 LEFT JOIN Table1 ON Table2.field1 = Table1.field1
WHERE (((Table1.field1) Is Null));

 

[bn2hunt]

Thanks, I was just coming to post that I had figured it out.

Thanks

DDNWolff