Using "AND" in the query design pane 1

[looperboy]

If I have a database with a customers table, and a linked table that contains a field "Products", can I use the query design pane to find which customers have both an entry for both a "Book", And a "CD" in the products field?

I know it is simple to find customers who have an entry for a "Book" Or a "CD", but how would I use "And" in these circumstances?

 

[TonyJollans]

Hi looperboy,

Someone may put me right on this but I believe that's the case.


Enjoy,
Tony

-----------------------------------------------
A very Merry Christmas to all Tek-Tippers
----------------------------------------------- [santa2]

 

[Golom]

You can do it in one query like this

SELECT C.CustomerID, C.CustomerName

FROM (Customers AS C INNER JOIN CustProduct AS P1 ON C.CustomerID = P1.CustomerID) 
      INNER JOIN CustProduct AS P2 ON C.CustomerID = P2.CustomerID

WHERE P1.Product = "Book"
      AND P2.Product = "CD"

      AND NOT EXISTS 
          (Select * From CustProduct X 
           Where     X.CustomerID = C.CustomerID 
                 AND X.Product    = "DVD")

 

[looperboy]

Thankyou.

Both the secondary query, and the single query code seem to work on my tables. I just have a couple of questions:

1) Tonyjollans, when you say "You can type the second query in full into the Criteria", do you mean the Criteria field on the graphical view? What I've been doing so far is to run the first query fully graphically, and then run a query on the results of that using your code- typed stright into the SQL view. Does your sentence above suggest another way?

2) What would be the proceedure for adding a second or even a third negative statement to the query?

Thanks again.

 

[TonyJollans]

Hi looperboy,

I'm not really here - far too busy getting ready for tomorrow, so very quickly ..

1) It's not much different, but to avoid using the SQL window, in your Query Design, add CustomerID as a field in the Query and uncheck the "Show" checkbox, then in Criteria enter ..

Not In (SELECT Customers.CustomerID FROM Customers INNER JOIN Prodducts ON Customers.CustomerID = Prodducts.CustomerID WHERE Prodducts.Product = "CD&quot

This is what will show up if you do it in SQL and then return to Query Design.

You can make it slightly easier (shorter) to type if you set up a stand-alone query (graphically) which is a straightforward join of all CustomerID - Product combinations and call it, say, CustProd. Your criteria then becomes ..

Not In (SELECT CustomerID FROM CustProd WHERE Product = "CD&quot

2) To include two such criteria you need to change the WHERE clause in the sub-query so that you exclude all customers who have either a CD or a DVD, like this ..

Not In (SELECT Customers.CustomerID FROM Customers INNER JOIN Prodducts ON Customers.CustomerID = Prodducts.CustomerID WHERE Prodducts.Product In ("CD", "DVD&quot)

Hope this helps. Have a good holiday.

Enjoy,
Tony

-----------------------------------------------
A very Merry Christmas to all Tek-Tippers
----------------------------------------------- [santa2]