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

B

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
 
V

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
 
J

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
 
B

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!
 
B

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
 
V

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
 
B

Bill

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

vanderghast said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top