Pull Out Common Items

[Rustin788]

Hey,

I am trying to find a way to easily pull out items that customers purchased in common. For instance, in the example I made below I would like the results to show me that all 3 companies purchased Blue and Orange. In the actual report I am looking at 10-15 customers at a time and some customers purchase 30 different items.

I have tried to just do it manually by looking at a cross tab but the cross tab still keeps the items groups separately so I can't tell which item all customers have purchased.

Thanks for the help.

Customer	Item	Qty
A	Blue	1
A	Orange	3
A	Yellow	4
A	Green	2
B	Blue	2
B	Orange	1
B	Red	4
B	Purple	3
C	Blue	5
C	Orange	3
C	Black	4
C	Pink	4

[ranman256]

now the count query will show, by Product:

Row Labels	Count of Customer
Black	1
Blue	3
Green	1
Orange	3
Pink	1
Purple	1
Red	1
Yellow	1

and another will give names by product:

Item	Customer	Qty
Black	C	4
Blue	A	1
Blue	B	2
Blue	C	5
Green	A	2
Orange	A	3
Orange	B	1
Orange	C	3
Pink	C	4
Purple	B	3
Red	B	4
Yellow	A	4

[ArviLaanemets]

Having table name tblTest:

Create a saved query

qTestCustomers = SELECT DISTINCT t.Customer AS Customer FROM tblTest AS t;

Create a Query

SELECT t.Item, COUNT(t.Customer) AS ItemCustomers
FROM tblTest t
GROUP BY t.Item
HAVING COUNT(t.Customer) = (SELECT COUNT(Customer) FROM qTestCustomers);

[Rustin788]

Having table name tblTest:

Create a saved query

qTestCustomers = SELECT DISTINCT t.Customer AS Customer FROM tblTest AS t;

Create a Query

SELECT t.Item, COUNT(t.Customer) AS ItemCustomers
FROM tblTest t
GROUP BY t.Item
HAVING COUNT(t.Customer) = (SELECT COUNT(Customer) FROM qTestCustomers);

Awesome, that worked. Just to make sure I understand what it is doing...

- The qTestCustomers query is looking at my data and and giving me a list of the unique values.

- The second query is counting the number of customers that purchased each item and then only including those that match the total number of customers in the first query.

[ArviLaanemets]

Exactly!

Slightly changing the condition in HAVING clause, you also can get p.e. list of items which are bought at least by given percent of customers.