Something other than AND or OR

[NSNewey]

Hi
Using northwind as an example, how would I build a query to return Customers with a related contact named Jane, AND and a related contact named John? ie, I only want to return customers that have both a Jane and a John contact related to them.

I tried using AND but it returns no records because a contact cannot be both Jane AND John. OR is wrong because I want to see both, not one or the other.

My query isn't using customer and contact, but this makes it easier to explain what I'm trying to do.

I'm hoping the answer isn't to build a sub query for Jane and another for John because I have around 40 different search criteria, ie Jane, John, Susan, Billy, Adam...

Thanks for any help.
Neil

 

[combo]

OR is wrong because I want to see both, not one or the other.

???
It is clean OR. If you don't like it, use IN it is shorter for more names. Finally, a table with names joined in query is a third approach.

combo

 

[NSNewey]

It can't be OR because I want to return customers (parent table) having a related contacts record (child table) called Jane and John. Using OR would show customers with either Jane or John. I want to see customers with both. Thanks

 

[strongm]

Using your example, something like:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers_1.ContactName
FROM Customers INNER JOIN Customers AS Customers_1 ON Customers.CompanyName = Customers_1.CompanyName
WHERE (((Customers.ContactName) Like "Jane*") AND ((Customers_1.ContactName) Like "John*"));

 

[Andrzejek]

Or could it be simple:[tt]
SELECT *
FROM Customers
WHERE ContactName Like "Jane*" OR ContactName Like "John*"[/tt]

and, since you ask for 2 names, accept only the outcome of 2 records?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Not quite sure that is particularly effective.

 

[Andrzejek]

Not ‘particularly effective’. Agree. Kind of lazy approach to the issue. But if OP wants to ask for variable number of Names, that may be one way to do it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>Kind of lazy approach to the issue

It's not so much that but the fact that I rather think you need to do expand your query to achieve what you are suggesting, and which I suspect will actually end up being more complex than my query ...

erm, something like:

[tt]SELECT Customers.CompanyName, Count(Customers.CustomerID) AS CountID
FROM Customers
GROUP BY Customers.CompanyName
HAVING (((Count(Customers.CustomerID))=2) AND ((First(Customers.ContactName)) Like "Jane*")) OR (((First(Customers.ContactName)) Like "John*"));[/tt]

 

[NSNewey]

Thanks guys. If it looked like it was getting complex, my backup was to loop through a recordset, do the conditional bits then and add items to the listbox rather than using a query. I've been working on that and it works fine. Just wondered if there was an SQL function that easily achieved what I needed. There is but I need to build the query in code so may as well have used a recordset.
Thanks for the advice.
Neil