MS Query to exclude state abbreviations

I have a query that I would like to exlude US States from. I am trying to find all data related to countries other than the US. I have the abbreviations for all the US States and can either link the table to the query and exlude them somehow or write a statement in the query to exlude them. But, I don't know how to do either. I thought I could do Not "'AK', 'IA', 'KY', 'WA'" but that isn't w开发者_如何学Corking. Any suggestions?

Can you do something like this:

SELECT * from table1 as t1
LEFT JOIN StatesTable as st
ON t1.State = st.State
WHERE st.id is null

You can also use NOT IN but i think that is a performance no-no. Example:

SELECT * FROM table1 where State NOT IN
   (SELECT State from StatesTable)

Does your states table include non-US State values, like Canadian provinces?

If so, add another boolean field to your states table named isInUS and mark as true the 50 states in the US.

Then check for rows whose related state value is not in the US.

Otherwise, if your states table contains 50 rows, use the left join Aaron posted.

I can't recall exactly what SQL syntax MS-Access uses, but I think you can do something like

SELECT ... FROM ... WHERE StateAbbrev NOT IN ('AK', 'IA', 'KY', 'WA')

If you had the state data in a table, you could remove it a couple of ways. Here is one:

SELECT ... FROM ... WHERE StateAbbrev NOT IN (SELECT Abbrev FROM States)

There are lots of variations on this second method. This is one and it works, it trades readability for performance.