Access Unmatched or similar query where a column does not contain or is not like another column

I want to design a query that basically does a mass amount of "Not Like "*x*", except all of the things I would not like the query to contain are in another column.

I know I can do this one at a time by just using the criteria and specifying "Not like "*x*", but I have no idea how to do a not like for a whole column of data.

So, the long version is that I have a bunch of cameras hosted on several different severs on a corporate network. Each of these cameras are on the same subnet and everything but the last octet of the IP address matches the server. Now, I have already created a field in a query that trims off the last octet of my IP, so I now basically have a pre-made IP range of where the cameras could possibly be. However, I do not have an exact inventory of each of the cameras – and there’s not really a fast way to do this.

I have a list of issues that I’m working on and I’ve noticed some of the cameras coming up in the list of issues (basically a table that includes a bunch of IP addresses). I’d like to remove all possible instances of the cameras from appearing in the report.

I’ve seen designs where people have been able to compare like columns, but I want to do the opposite. I want to generate a query where it does not contain anything like what’s in the camera column.

For the sake of this, I’ll call the query where I have the camera ranges Camera Ranges and the field Camera Range.

Is there a way I can accomplish this?

I’m open to designing a query or even changing up my table to make it easier to do the query.

Answer

Similar to the answer I provided here, rather than using a negative selection in which you are testing whether the value held by a record is not like any record in another dataset, the easier approach is to match those which are like the dataset and return those records with no match.

To accomplish this, you can use a left join coupled with an is null condition in the where clause, for example:

select 
    MainData.*
from 
    MainData left join ExclusionData on 
    MainData.TargetField like ExclusionData.Pattern
where 
    ExclusionData.Pattern is null

Or, if the pattern field does not already contain wildcard operators:

select 
    MainData.*
from 
    MainData left join ExclusionData on 
    MainData.TargetField like '*' & ExclusionData.Pattern & '*'
where 
    ExclusionData.Pattern is null

Note that MS Access will not be able to represent such calculated joins in the Query Designer, but the JET database engine used by MS Access will still be able to interpret and execute the valid SQL.