Unmatched query to lookup part of the field

[DMetcalfe]

Hi,

I need an unmatched query to exclude items containing a value.
Such as:
in my "Exclude" table, I have Dan
in my normal table, I have Daniel, Danny, Dan, and other names

With an unmatched query, it would only exclude "Dan"
I would like it to exclude anything containing the value "Dan" so Daniel, Danny, Dan would be excluded too

I've tried using *Dan* and Dan* in my exclude table, but it doesnt work!

Please help?













Edit: After many attempts to help me, This is what i'm reduced to in my query:
Not Like "*Dan*" And Not Like "*blah*" And Not Like "*bleh*"
etc etc
I want something like:


Not Like "*" & [tblExclude.Excluded] & "*"
Which works, but only if I have ONE value in the Excluded list

I require a table to make Not Like "*Dan*" And Not Like "*blah*" And Not Like "*bleh*" easier to manage

[jpkeller55]

Try Like *Dan* to include or Not Like *Dan* to exclude

[DMetcalfe]

Try Like *Dan* to include or Not Like *Dan* to exclude

This would not work for me since I want it to lookup a list of values in the exclude table.

I don't want to manage a huge "Not Like *Dan* and Not Like *blah* and Not Like *meh*" string.
It would get quite complicated!

[jpkeller55]

Try something like this and ammend the table and field names to what you have.

SELECT tblNames.NameText
FROM tblNames, tblExclude
WHERE (((tblNames.NameText) Not Like "*" & [tblExclude]![ExlName] & "*"));

[shexe]

Do you have a table of known or acceptable values?

[jpkeller55]

Actually, this is probably better to be consistent

SELECT tblNames.NameID, tblNames.NameText
FROM tblNames, tblExclude
WHERE (((tblNames.NameText) Not Like "*" & tblExclude.ExlName & "*"));

[DMetcalfe]

Do you have a table of known or acceptable values?

No, were planning on adding values as we go along. So a table would be easiest

Actually, this is probably better to be consistent

SELECT tblNames.NameID, tblNames.NameText
FROM tblNames, tblExclude
WHERE (((tblNames.NameText) Not Like "*" & tblExclude.ExlName & "*"));

this sort of works, but when i have multiple values within the tblExclude, it doesnt exclude everything.
There is over 50000 entries in the field I wish to filter

[shexe]

I would use a LEFT JOIN ON the table you are attempting to filter names and exclude matching values in the WHERE clause. Think of this as a VLOOKUP in excel and filtering on matching values.

[DMetcalfe]

I would use a LEFT JOIN ON the table you are attempting to filter names and exclude matching values in the WHERE clause. Think of this as a VLOOKUP in excel and filtering on matching values.

I don't understand what you mean by this. Please could you post an example SQL View script?

[shexe]

Sure - This is a query I use to find records that do not match to an allowed table I manage.

SELECT qry_Budget_Workbook_Output_2010.Manager, qry_Budget_Workbook_Output_2010.Project, qry_Budget_Workbook_Output_2010.BudgetWorkbook, qry_Budget_Workbook_Output_2010.ResourceName

FROM qry_Budget_Workbook_Output_2010 LEFT JOIN Budget_Workbook_Allowed ON qry_Budget_Workbook_Output_2010.BudgetWorkbook=Bud get_Workbook_Allowed.BudgetWorkbook

WHERE [Budget_Workbook_Allowed].[BudgetWorkbook] Is Null;

You will want to include all needed fields in the select statement. The LEFT JOIN is matching the OutPut qry to the Allowed or in your case, not allowed table. The WHERE clause is filtering where the LEFT JOIN does not provide a match.

[DMetcalfe]

Sure - This is a query I use to find records that do not match to an allowed table I manage.

SELECT qry_Budget_Workbook_Output_2010.Manager, qry_Budget_Workbook_Output_2010.Project, qry_Budget_Workbook_Output_2010.BudgetWorkbook, qry_Budget_Workbook_Output_2010.ResourceName

FROM qry_Budget_Workbook_Output_2010 LEFT JOIN Budget_Workbook_Allowed ON qry_Budget_Workbook_Output_2010.BudgetWorkbook=Bud get_Workbook_Allowed.BudgetWorkbook

WHERE [Budget_Workbook_Allowed].[BudgetWorkbook] Is Null;

You will want to include all needed fields in the select statement. The LEFT JOIN is matching the OutPut qry to the Allowed or in your case, not allowed table. The WHERE clause is filtering where the LEFT JOIN does not provide a match.

This has brought me back to the beginning of the thread.
Your example will filter by exact values only.
So if i have Danny in my main table, and Dan in my filter table, it wont filter it out!

It's because a join is looking where feilds are Equal
A join will not fix what I require, because the fields will not always be Equal

[shexe]

Look into using a Left Function in your select statement. Left([tbl_NotAllowed].Name,3)="Dan"

[DMetcalfe]

Look into using a Left Function in your select statement. Left([tbl_NotAllowed].Name,3)="Dan"

I dont understand what you mean by this?

[jpkeller55]

Not sure if you found a solution for this or not. Here is something that should work for you. It may not be the most elegant of solutions, but it worked on my test table.

What I did is build two queries. The first query is used to find names that contain an excluded name in their string. Using the InStr() function, a name that contains an excluded name in the string will return a value of 1.

The second query is simply a matter of running an unmatched query on your table of names and the query built using the InStr() function.

So the first query (qryInString) would be:

SELECT DISTINCT tblNames.NameID, tblNames.NameTxt
FROM tblNames, tblExcludes
WHERE (((InStr(([tblNames]![NameTxt]),([tblExcludes]![exlName])))=1));

Then, run the unmatched query against this and your table.

SELECT tblNames.NameID, tblNames.NameTxt
FROM tblNames LEFT JOIN qryInString ON tblNames.NameTxt = qryInString.NameTxt
WHERE (((qryInString.NameTxt) Is Null));

[DMetcalfe]

Not sure if you found a solution for this or not. Here is something that should work for you. It may not be the most elegant of solutions, but it worked on my test table.

What I did is build two queries. The first query is used to find names that contain an excluded name in their string. Using the InStr() function, a name that contains an excluded name in the string will return a value of 1.

The second query is simply a matter of running an unmatched query on your table of names and the query built using the InStr() function.

So the first query (qryInString) would be:

SELECT DISTINCT tblNames.NameID, tblNames.NameTxt
FROM tblNames, tblExcludes
WHERE (((InStr(([tblNames]![NameTxt]),([tblExcludes]![exlName])))=1));

Then, run the unmatched query against this and your table.

SELECT tblNames.NameID, tblNames.NameTxt
FROM tblNames LEFT JOIN qryInString ON tblNames.NameTxt = qryInString.NameTxt
WHERE (((qryInString.NameTxt) Is Null));

Thankyou!
Messy as this may be, It works!
The qryinstring gets all names containing the excluded names. Then the unmatched query can filter on these

Thanks a lot dude!