SELECT Query with AND in criteria

[iXm]

I am new to MS Access!

I have a Table, with products and their categories. Each category (CAT) may have no product or multiple numbers of products. Now, I want to search for category which is having P1 and P2 products in it.

I am doing it in SELECT Query, but don't know how to do it..?

Need some help, please.

 

[arnelgp]

Field: Product----------Product
Show: (tick)------------(no tick)
Criteria: "P1"-----------"P2"

 

[iXm]

Field: Product----------Product
Show: (tick)------------(no tick)
Criteria: "P1"-----------"P2"

I didn't get you?

 

[CJ_London]

to clarify what you are asking you want to return all CAT's which have both P1 and P2 records

you also need to clarify whether this data needs to be editable or not

I would suggest something like this

SELECT A.CAT FROM myTable A INNER JOIN myTable B ON A.CAT=B.CAT WHERE A.Product="P1" AND B.Product="P2"

 

[iXm]

to clarify what you are asking you want to return all CAT's which have both P1 and P2 records

Absolutely "YES"

 

[arnelgp]

the one I showed you is the one you need to put in Query design (How to do it?, remember). Two Product field in separate column with different criteria.
you also need to use Total query (remove the ID from the query), so it will show only Cat: A

 

[bob fitz]

The query will return no records with your criteria because no record have P1 AND P2.
Perhaps you need to use OR instead of AND

 

[iXm]

to clarify what you are asking you want to return all CAT's which have both P1 and P2 records

you also need to clarify whether this data needs to be editable or not

I would suggest something like this

SELECT A.CAT FROM myTable A INNER JOIN myTable B ON A.CAT=B.CAT WHERE A.Product="P1" AND B.Product="P2"

No, I don't want data to be editable

 

[iXm]

the one I showed you is the one you need to put in Query design (How to do it?, remember). Two Product field in separate column with different criteria.
you also need to use Total query (remove the ID from the query), so it will show only Cat: A

I did it by pasting your code in SQL View, it works! Can you explain, how to do it in Query Design..?

 

[CJ_London]

in query design, drag your table onto the display area twice - one will have _1 added to the name which is an alias. You can then create your join and criteria and fields to be returned. To change alias names, click on the properties button and select one of the tables - you will see the alias property

 

[cheekybuddha]

Why the join?

More simply:

SELECT
  CAT
FROM YourTable
WHERE Product IN ('P1', 'P2')
GROUP BY CAT
HAVING COUNT(*) = 2;

 

[isladogs]

The query will return no records with your criteria because no record have P1 AND P2.
Perhaps you need to use OR instead of AND

@bob fitz
That won't work here as it will return A. B & C unless you also specify a count >1

 

[zeroaccess]

You could just right click your field area, change to Totals, and change the Group By to Where

 

[iXm]

to clarify what you are asking you want to return all CAT's which have both P1 and P2 records

you also need to clarify whether this data needs to be editable or not

I would suggest something like this

SELECT A.CAT FROM myTable A INNER JOIN myTable B ON A.CAT=B.CAT WHERE A.Product="P1" AND B.Product="P2"

What if I want to go for additional criteria such as "P1" And "P2" And "P3", do I need to have another table named "C"..?

 

[CJ_London]

do I need to have another table named "C".

yes - but it may be simpler to use something like cheeky's

SELECT CAT FROM YourTable WHERE Product IN ('P1', 'P2', 'P3') GROUP BY CAT HAVING COUNT(*) = 3;

 

[isladogs]

Why the join?

More simply:

SELECT
  CAT
FROM YourTable
WHERE Product IN ('P1', 'P2')
GROUP BY CAT
HAVING COUNT(*) = 2;

@cheekybuddha
This appeared to be the neatest solution at first sight. However, it doesn't actually work for the original question in post #1 on the supplied data as records 5 & 6 for CAT = C are also wrongly included. It should just be CAT =A.
However, CJL's variation on this idea will work for the modified request from Post #14

I think CJL's solution in post #4 is the most reliable approach overall.

 

[CJ_London]

think I'm seeing double

 

[arnelgp]

it's the Corona effect on the column headings.

 

[isladogs]

@CJ_London
There. I've fixed your double vision...

 

[cheekybuddha]

@cheekybuddha
This appeared to be the neatest solution at first sight. However, it doesn't actually work for the original question in post #1 on the supplied data as records 5 & 6 for CAT = C are also wrongly included. It should just be CAT =A.
However, CJL's variation on this idea will work for the modified request from Post #14

I think CJL's solution in post #4 is the most reliable approach overall.

Apologies all, unlike Colin, I didn't test.

The solution is to add Product to the GROUP BY clause.

SELECT
  CAT
FROM Test
WHERE Product IN ('P1', 'P2')
GROUP BY
  CAT,
  Product
HAVING COUNT(*) = 2;

Or:

SELECT
  CAT
FROM Test
WHERE Product IN ('P1', 'P2', 'P3')
GROUP BY
  CAT,
  Product
HAVING COUNT(*) = 3;