Don't want a return if it exists in another table

[Bill]

Hope this comes over ok.

I have a query retuning a rows with a number of fields in it. Where the
return of a certain field is 'x' I don't want it returned. Easy enough.

However the requirement is growing to NOT return rows where this field
contains not only 'x' but x1, x2 x3 x4 etc. To do this within a query is
getting cumbersome so I would like to enter x, x1, x3 etc in another table
as exclusions but I don't know how to get the original table to look at that
exclusions table to get what I need.

Can anyone help please.

Ta.
Bill

 

[vanderghast]

If the other table, otherTable, has one field, Exclusion, then:

SELECT myTable.*
FROM myTable LEFT JOIN otherTable.Exclusion
ON myTable.myField LIKE "*" & otherTable.Exclusion & "*"
WHERE otherTable.Exclusion IS NULL


should do.




Vanderghast, Access MVP

 

[John Spencer]

Use the Unmatched query wizard to build a query based on your existing query
and the new table.

If the list was fairly short, you could use this as the criteria.

WHERE SomeField not In ("x1","x2","x3","x4")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bill]

Have created a test mdb file with the tables and fields as you suggest but
getting "syntax error in JOIN operation". Have had a fiddle but can't spot
the problem!

 

[Bill]

Slowly built it up line by line and it ended up like this - soooo close

SELECT myTable.*
FROM myTable LEFT JOIN otherTable
ON myTable.myfield Like "*" & otherTable.Exclusion & "*"
WHERE otherTable.Exclusion IS NULL;

Access can't look at in QBE view however but never mind.

Thanks for you help.
Bill

 

[vanderghast]

The QBE can only show equi join (joins implying operator = , here, we use
operator LIKE).


I aam right to ssume it works? or there is still a syntax error?

We could still use a subquery, with NOT EXISTS, but it would be slower...

SELECT *
FROM myTable
WHERE NOT EXISTS(
SELECT *
FROM otherTable
WHERE myTable.myfield Like "*" & otherTable.Exclusion & "*")



but the subquery still has to be written without much help from the QBE.

Vanderghast, Access MVP

 

[Bill]

Yes, all working.
Thanks for the help.
Bill.

The QBE can only show equi join (joins implying operator = , here, we use
operator LIKE).


I aam right to ssume it works? or there is still a syntax error?

We could still use a subquery, with NOT EXISTS, but it would be slower...

SELECT *
FROM myTable
WHERE NOT EXISTS(
SELECT *
FROM otherTable
WHERE myTable.myfield Like "*" & otherTable.Exclusion & "*")



but the subquery still has to be written without much help from the QBE.

Vanderghast, Access MVP