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?

 

[SteveR77]

The AND condition is implied.

In order to get what you are looking for you would have to set the desired customer as the criteria and then for the item(s) you would want you would set up a Like statement as the criteria something like this one.

Like "book" or Like "cd"

It is important that the criteria is all on the criteria line otherwise you will get more records than you are expecting.

 

[looperboy]

i don't think i quite understand. when you say i would have to set the desired customer as the criteria, do you mean i would set a specific customer as the criteria. What I am looking for is for the query to produce a list of cutomers who have all got a listing for "Book" AND "CD" in the products column.

 

[SteveR77]

Sorry I miss understood I thought you were looking of a unique customer.

The answer is to leave the customer criteria blank, and have your products column be an order condition for the book and cd.

The reason that it can not be an AND if I understand properly would be the product could not be a cd and a book at the same moment in time.

 

[looperboy]

thanks, steve. this might sounds stupid, but how do I go about making my products column an order condition for the book and cd?

 

[Golom]

Just to be more concrete in the discussion, if you have something like

CustomerID     Product

   1              Book
   2              Book
   1              CD
   2              DVD

Then

Select C1.CustomerID
From Customers C1 INNER JOIN Customers C2
     ON     C1.CustomerID = C2.CustomerID
        AND C1.Product = 'Book'
        AND C2.Product = 'CD'

Should return 1

Is that what you have?

 

[SteveR77]

Golom,

Thanks, for breaking out the SQL syntax, it should makes things a bit clearer to everyone.

 

[looperboy]

i was wondering if it's possible to do from the Query Design Pane without having to resort to the code. Would that be a "No"?

 

[SteveR77]

looperboy,

A bound combo box field that will allow you to select your options and then write them to the field in the table should do the trick.

 

[TonyJollans]

Hi looperboy,

I don't think Golom's SQL will work in Access, but anyway it doesn't really answer your question.

You say you have a Customers table and a linked table containing Products. Suppose your Customers Table is like this:
[tt]
CustomerID Name
1 Fred
2 Bill
3 Jack
4 Jill
5 Edward
6 Brenda
[/tt]
And your Products Table is like this:
[tt]
CustomerID Product
1 Book
2 Book
5 Book
1 Tape
2 Tape
2 CD
3 CD
5 CD
6 CD
4 DVD
5 DVD
[/tt]
I'm not sure how much you know so my apologies if this is over-simplified.

[ul][li]Create a new Query and add the Customers Table once and the Products Table twice (Access will name the second occurrence Products_1 by default)[/li]
[li]Set up a link by clicking and dragging the CustomerID field from the Customers Table to the CustomerId field on the Products Table.[/li]
[li]Set up a link by clicking and dragging the CustomerID field from the Customers Table to the CustomerId field on the Products_1 Table.[/li]
[li](Note that these links might appear automatically if you have relationships between your tables set up)[/li]
[li]Double click on Name in the Customers Table to add Customer Name to the Query[/li]
[li]Double click on Product on the Products Table to add it to the Query. Uncheck the Check Box in the Show Row, and in the Criteria Row, enter "Book"[/li]
[li]Double click on Product on the Products_1 Table to add it to the Query. Uncheck the Check Box in the Show Row, and in the Criteria Row, enter "CD"[/li][/ul]

With the sample data above this Query should give you two rows - Edward and Bill.

Enjoy,
Tony

 

[Golom]

TonyJollans is right ... the SQL I supplied doesn't work with Access ... but this does with his table structure.

SELECT C.CustomerID, C.[Name]

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

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

 

[looperboy]

thanks very much, tony. That's exactly what I was after. You're a star.

 

[looperboy]

Oops, spoke too soon.

I can't get either of these suggestions to work. Both Tony's and Golom's return the message "Type mismatch in expression" when I try to run the query. I have created tables exactly the same as the ones in Tony's example to try and run it, but the results are the same. Is there something I'm doing wrong?

 

[lespaul]

The type mismatch error can occur when you have string variables that are missing the quotes or number characters that have them. Can you go to the SQL view of your query and post it?

Leslie

 

[looperboy]

This is it:

SELECT Customers.Name
FROM (Customers INNER JOIN Products ON Customers.CustomerID = Products.CustomerID) INNER JOIN Products AS Products_1 ON Customers.CustomerID = Products_1.CustomerID
WHERE (((Products.Product)="Book&quot AND ((Products_1.Product)="CD&quot);

 

[TonyJollans]

Hi looperboy,

Check the datatypes of the CustomerID field in Customers and Products; are they both the same? Or is one of them Number and the other Text?


Enjoy,
Tony

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

 

[looperboy]

that was it, tony. thanks. your star status is restored.

my customer ID field was an autonumber field in the customer table, and a text field in the products table.

cheers

looperboy

 

[looperboy]

I'm not sure if this is a dead thread now or not, but I want to know if I can expand the method suggested by Tonyjollans to find which of my customers have not bought a CD. And if it can be expanded even further to find who has bought a CD AND a Book, but not a DVD.

 

[TonyJollans]

Hi looperboy,

Thread's still alive

To find Customers who have not bought a CD, for example, requires the use of a second query. You can type the second query in full into the Criteria but you can't do the whole thing graphically in a single query via Query design.

The SQL is along the lines of ..

SELECT Name FROM Customers
WHERE CustomerID NOT IN (SELECT Customers.CustomerID FROM Customers INNER JOIN Products ON Customers.CustomerID = Products.CustomerID WHERE Products.Product = "CD&quot

You can expand this to include extra (positive) criteria without too much difficulty - it's the negative criteria which require the NOT IN SubQuery. If you want a bit more help with this please post back.


Enjoy,
Tony

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

 

[looperboy]

thanks tony,

i think i'm going to be able to use that.

i'll get back if there's a problem.

does that mean negative criteria can only be entered as SQL? you couldn't first run one query graphically, and then perform another graphical query on the results?