Joining Tables to Exclude Records

[ddevil]

I have a table which contains records that I want to exclude from a query, but I don't see an option in the join criteria to give me all the records in one table except the records that equal from the table with the records I want to exclude.

I was thinking I could do a not in in the criteria for those records and reference the field that contains the records I want to exclude, but I couldn't figure that out.

If you could help, that would be great. Thanks!

 

[PWise]

Select *
from table1
inner join table2
on table1.idfield=table2.idfield
where table1.somefield<>somevalue

 

[ddevil]

How do you do that with the graphical tool in Access? I get three choices for joining. Where the fields equal. All from one and only those that equal form the other (either way). I'm sorry this tool confuses me. I get the SQL.

 

[PWise]

Where the fields equal

 

[ddevil]

OK, so I have one table called Event_Detail with a field called EventNo. The second table is Events_to_Exclude and a field called EventNo. If I join on the two fields where the EventNo = EventNo, then I only get the records I want to exclude.

What I want is to get all the records in Event_Detail, except the ones that equal in Events_to_Exclude.

I can do a not in (13433,13423) etc in the criteria field, but I have 1000 records to exclude. Can you reference a query or table in the criteria field for the in statement?

Still confused...sorry!

 

[PWise]

Select Event_Detail.*
from Event_Detail
left join Events_to_Exclude
on Event_Detail.EventNo = Events_to_Exclude.EventNo
where Events_to_Exclude.EventNo is null

 

[PHV]

Simply follow the unmatched query wizard.
SELECT D.*
FROM Event_Detail D LEFT JOIN Events_to_Exclude X ON D.EventNo = X.EventNo
WHERE X.EventNo Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ddevil]

Ok, light bulb is on now. That's what I needed. Thanks!