Excluding data from one table based on another table where the values contain wildcards

[scor1pion]

Hello,

I'm trying to have my query exclude data based on another table. I've seen various examples around here and can get them to partially work.

For example:

I have a table of over 7000 rows with 4 fields (columns) of data one of those fields is labeled Owner. There are over 2000 rows alone for one particular owner that I don't care to review and I have my exclude setup as follows and this one works as I need.

SELECT Resources.Resource, Resources.[Resource Owner], Resources.[User ID], Resources.Access, [Resource Owner Exclusions].[Resource Owner]
FROM [Resource Owner Exclusions] RIGHT JOIN Resources ON [Resource Owner Exclusions].[Resource Owner] = Resources.[Resource Owner]
WHERE ((([Resource Owner Exclusions].[Resource Owner]) Is Null));

It points to my second table where the owner I have listed as an exclude resides.

I know I could write that right in to the SQL statement, but I don't want too. The excludes will change through out the time and could be anywhere from 1 to 5 different owners that I would want to exclude and the people using this would not be able to edit/write SQL to make those changes. But they can add/delete a name from the owner exclude list.


My problem being with this second exclude. I don't know how to do it by pointing to another table. Writing it in to the SQL I can do it. But again, i don't want that as the people using this will not be able edit the SQL but they can change the entries in the table.



I have a third table 4 rows 2 fields and I want to exclude the following Not Like ????T.* ???T.* *test* *dev* from my resources table in the resource field the first 2 excludes are anything where the letter T is the 4th or 5th character from the left the second 2 excludes are items in the resoucre filed that contain the word test or dev in it.

These are examples of what I want to exclude in my query from my resource table based on the criteria defined above by the query pointing to a third table which contains this list. I don't want to exclude by the owner or by the ID as they have both production and test resources I only want to exclude the test/dev resources.

Resource	Resource Owner	User ID	Access
CAAPT.CCAAPC01.R810.*	D116ADM	FTPCAN	ALTER
SCLMWWAP.DEV.*	SCLMWWAP	BDWBHLT	READ
SCLMWWAP.TEST.*	SCLMWWAP	CAPSADM	READ
DB2T.DSNDBC.CAPDPROD*	D116ADM	DSNTDBM1	ALTER

This is my list of exclusions in a third table

Resource
*test*
*dev*
????t.*
???t.*

Is there a way to do what I want?

[John_G]

On way to do this would be to not use a JOIN but to use a sub-query to determine which owners to exclude.

The list of owners to exclude is in the table [Resource Owner Exclusion], so you want to retrieve all records from [Resources] where the owner is NOT in that list.

You would use SQL something like this:

SELECT Resources.Resource, Resources.[Resource Owner], Resources.[User ID], Resources.Access
FROM Resources
WHERE Resources.[Resource Owner] not in (Select [Resource Owner Exclusions].[Resource Owner] from [Resource Owner Exclusions] )

That would look after your first group of exclusions, and as you can see, it "points" to another table without creating a join to it.

The second exclusion group, based on only part of the resource name, is a lot harder. I don't see a way of writing the SQL directly as I did above. I think you will need to use a little VBA code to generate the rest of the SQL statement based on what is in that 3rd table. It would create a series of NOT LIKE ... clauses, one for each entry in the table.

As an aside, you would have to make sure your users know exactly how to formulate an exclusion string to use in those NOT LIKE clauses.

[scor1pion]

Yeah I already have the entire exclusion process through SQL.

This does what I want, just not how I want...

SELECT Resources.Resource, Resources.[Resource Owner], Resources.[User ID], Resources.Access, [Resource Owner Exclusions].[Resource Owner]
FROM [Resource Owner Exclusions] RIGHT JOIN Resources ON [Resource Owner Exclusions].[Resource Owner] = Resources.[Resource Owner]
WHERE (((Resources.Resource) Not Like "???T.*" And (Resources.Resource) Not Like "????T.*" And (Resources.Resource) Not Like "*test*" And (Resources.Resource) Not Like "*dev*") AND (([Resource Owner Exclusions].[Resource Owner]) Is Null));

I was just hoping that the second set of exclusions could be done by looking to another table so the users wouldn't have to edit the SQL.