AND Operator

[ozzroo]

Hi

I have a query and I am trying to filter out the clients who have bought products 1, 2 and 3. I am trying to put the criteria into the ProductID.

I entered "1" And "2" And "3"

But not working - data type mismatch

Any helps

 

[AlexCuse]

What is the data type in the table? If it is text you need quotes around them, if numeric you need no quotes.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ozzroo]

Datatype is a number. Its a drop down list with the products.

So Product A = 1. Product B = 2

I tried without quotes and it retrieved no records even though I know its go records

 

[PHV]

You may try this:
In (1,2,3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

That returns all results but I want to filter out only those clients who have bought all 3 products

 

[PHV]

Could you please post the actual SQL code (with the In operator)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

SELECT tblClients.ClientID, tblClients.TitleID, tblClients.Forename, tblClients.Surname, qryDealHistory.OfferID, qryDealHistory.ChequeAmount
FROM tblClients INNER JOIN qryDealHistory ON tblClients.ClientID = qryDealHistory.ClientID
WHERE (((qryDealHistory.OfferID) In (1,2,3)));

 

[lespaul]

maybe:

SELECT tblClients.ClientID, tblClients.TitleID, tblClients.Forename, tblClients.Surname, qryDealHistory.OfferID, qryDealHistory.ChequeAmount
FROM tblClients INNER JOIN qryDealHistory ON tblClients.ClientID = qryDealHistory.ClientID
WHERE (((qryDealHistory.OfferID) In (1,2,3)))
GROUP BY tblClients.ClientID, tblClients.TitleID, tblClients.Forename, tblClients.Surname, qryDealHistory.OfferID, qryDealHistory.ChequeAmount
HAVING COUNT(*) = 3;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ozzroo]

lespaul. thanks for the code but it brings back no results

any other ideas

 

[PHV]

I'd try this:
SELECT C.ClientID, C.TitleID, C.Forename, C.Surname, H.OfferID
FROM tblClients AS C INNER JOIN qryDealHistory AS H ON C.ClientID = H.ClientID
WHERE H.OfferID In (1,2,3)
GROUP BY C.ClientID, C.TitleID, C.Forename, C.Surname, H.OfferID
HAVING Count(*) = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

still didnt work. could it be my table structure?

 

[PHV]

And what about this ?
SELECT C.ClientID, C.TitleID, C.Forename, C.Surname
FROM tblClients AS C INNER JOIN (
SELECT DISTINCT ClientID, OfferID FROM qryDealHistory
) AS H ON C.ClientID = H.ClientID
WHERE H.OfferID In (1,2,3)
GROUP BY C.ClientID, C.TitleID, C.Forename, C.Surname
HAVING Count(*) = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

Thanks PHV.

That seems to work

Much appreciated!

 

[ozzroo]

PHV, Just an added question

Would there be a way I could change the query so it brings up clients who have purchased all products, rather than just 1,2,3?

 

[PHV]

Yes you could.
I think you've got enough help so far to try yourself.