Access 2000: Filter from Queried results

[Wes Dabney]

My name is Wes and I have been working with Access for a few years now,
though I know I'm not an expert, this forum has helped me many times without
me needing to post a question. Alas, I have found a problem where the answer
was not as obvious.

The database I'm working on is very simple. It pulls from multiple source
data files and reorganizes the data. The relationship is created by relating
the city name and or county name fields within each source file. My first
problem was that the formatting was different in some source files, but a
friend of mine showed me how to do the comparison with a LIKE statement.

FROM [Employment by City], [Population by City]
WHERE ((([Population by City].[Place Name]) Like ([Employment by
City]![Area] & "*")));

Where [Place Name] might be 'Tyler City' and [Area] might only be 'Tyler'
but the fields would still be related and the data combined.

The problem is, I now need a way to use this query as a filter against
another source file and remove any matching entries. Basically the exact
opposite of the previous sql code.

I made a table from the queried results and now I don't have the problem of
different formats because the query included the [Place Name] field which is
the field I need filtered (say that five times quickly). I assumed that I
could use a simple <> or Not statement to remove any entries from the large
[Population by City] table, but instead I either entered an infinite loop or
was eventually given the entire table. Sadly I don't see where I'm making a
mistake, so any advice is welcome, I'm at a loss. I have researched this
source as well as my three Access bibles and nowhere do I see what I want
done accomplished, is it a pipe dream or am I just making a mistake
somewhere? Thanks for any help

Wes Dabney

[Pieter Linden]

FROM [Employment by City], [Population by City]
WHERE ((([Population by City].[Place Name]) Like ([Employment by
City]![Area] & "*")));

Where [Place Name] might be 'Tyler City' and [Area] might only be
'Tyler'
but the fields would still be related and the data combined.

The problem is, I now need a way to use this query as a filter against
another source file and remove any matching entries. Basically the
exact
opposite of the previous sql code.
Sounds like you need something like:

FROM [Employment by City], [Population by City]
WHERE (((([Population by City].[Place Name]) Like ([Employment by
City]![Area] & "*")))
AND [Population by City].[Place Name] NOT IN (SELECT [PlaceName] FROM
[SomeTable] WHERE...));