Access should identify all possible product combination

[Stan Michel]

Hi, I've a table of clients an all the different products (P) they bought.

Account P1 P2 P3 P4 ...
Client1 2 0 1 0
Client2 0 0 1 1
Client3 0 3 0 0
Client4 0 1 1 1
.....

I'll analyse the cross selling within the products. Therefore access should
identify all possible combinations of products (P1 & P2, P1 & P3; P2 & P3 &
P4 etc.). Later I will count how many clients bought P1 and P2, P1 and P3 etc.

Has someone any idea how to do this automatically ?

Thanks
Stan

 

[pietlinden]

The easy way to do it is to create a deliberate cartesian product of
Account and Product.

SELECT A.AccountNo, P.ProductID
FROM Account As A, Product As P
ORDER BY A.AccountNo, P.ProductID;

Note the lack of joins. That's deliberate. The result is every
combination of {A} X {P}.

 

[Jerry Whittle]

The best way is to change your tables. As you have P1, P2, P3, etc., it's
obvious that your table is not properly normalized. You need at least three
tables. One for Accounts that hold information on the Clients. A second for
Products. And a third table of Sales that joins the primary key from Clients
and Products together. This is known as a bridging or linking table to fix a
many-to-many relationship. With such a design you could do what you wish plus
much, much more such as track how many individual products a Client bought
and when they bought them.