And Criteria in Query (1 Viewer)

[Alexander Willey]

Hello

This is probably a silly question.

I have a simple database with a one to many relationship between 'Consumer' and 'Products'. One consumer has many products.

Some consumers consume chocolate OR some consume cola, and some chocolate AND cola

I want to run a query that gives me only the consumers that consume chocolate AND cola from the Products field. So I think I am looking for an 'And' function on the one products field

I am unable to see how I do this. I seem restricted to using the Or function, which just gives me everyone who consumes chocolate, cola or both chocolate and cola

I hope that makes sense and welcome any help

Thank you

Kind regards

Alex

 

[jdraw]

Alex,

I think you may need an additional table.
Consider:

tblConsumers (ConsumeId,names, addresses, phone numbers...)
tblProducts (ProductId, Name, description,..other facts re product)

junctionTable:

jtblConsumerConsumesProduct (ConsumerId, ProductID..)

one record for each Consumer Product combination


The underlined fields indicate the PK of the table.

 

[plog]

jdraw is correct, but from the sounds of it your Products table maybe equivalent to jdraw'sjtblConsumerConsumesProduct table. IF that's the case and your data looks something like this:

Products
ConsumerID, ProductName
1, Chocolate
1, Cola
1, Chips
2, Cola
2, Cookies
3, Chocolate
3, Peanuts
4, Cola
4, Bread
4, Cola
4, Beer
4, Chocolate

Then you would need a sub query. First you would Group your data by consumers and products and limit it to just the Products you wanted to use, like so:

SELECT ConsumerID, ProductName
FROM Products
WHERE ProductName='Chocolate' OR ProductName='Cola'
GROUP BY ConsumerID, ProductName

Name that query 'subQ' and then run another one using that to and limit it to just consumerIDs that have 2 records in that table:

SELECT ConsumerID
FROM subQ
GROUP BY ConsumerID
HAVING COUNT(ConsumerID)=2;

That final query would produce these results based on my initial data set:

ConsumerID
1
4