Cross-selling finding query.

[Dawn]

Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
Want results as following:
PC TV Refrigeratory Microwave oven
PC
TV
Refrigeratory
Microwave oven
The four types are values for “goodstypeâ€, want to find how the person’s
buying-style in TV, Refrigeratory and Microwave oven while he buys pc. For
example the table shows how many person buy pc, and in such person how many
of them buy TV, how many of them buy Refrigeratory, how many of them buy
Microwave oven. The table are in counts.
How to write sql in access queries?
Thanks.
Dawn

 

[Jerry Whittle]

Find the built-in crosstab query wizard. It may take a couple of attempts,
but I think that it will produce the results that you want.

 

[John W. Vinson]

Hi Dawn,

I can set this up for you for a very reasonable fee. Contact me at
(e-mail address removed) if you want my help.

Steve, this is a FREE VOLUNTEER tech support group, as you well know. It is
not a place to hawk your dubious services.

 

[John W. Vinson]

Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
Want results as following:
PC TV Refrigeratory Microwave oven
PC
TV
Refrigeratory
Microwave oven
The four types are values for “goodstype”, want to find how the person’s
buying-style in TV, Refrigeratory and Microwave oven while he buys pc. For
example the table shows how many person buy pc, and in such person how many
of them buy TV, how many of them buy Refrigeratory, how many of them buy
Microwave oven. The table are in counts.
How to write sql in access queries?
Thanks.
Dawn

A "Self Join" query turned into a Crosstab query can do this. Create a query:

SELECT A.CustomerID, A.Goodstype AS GoodA, B.Goodstype As GoodB
FROM yourtable AS A INNER JOIN yourtable AS B
ON A.CustomerID = B.CustomerID
AND A.Goodstype <> B.Goodstype;

This should give you a tall-thin record of all the joint purchases for each
customer.

Then use the Crosstab Query Wizard on this query, using CustomerID and GoodA
as the "row header" and GoodB as the Column Header.

 

[xiaojun]

Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
Want results as following:
PC TV Refrigeratory Microwave oven
PC
TV
Refrigeratory
Microwave oven
The four types are values for ¡°goodstype¡±, want to find how the person¡¯s
buying-style in TV, Refrigeratory and Microwave oven while he buys pc. For
example the table shows how many person buy pc, and in such person how
many
of them buy TV, how many of them buy Refrigeratory, how many of them buy
Microwave oven. The table are in counts.
How to write sql in access queries?
Thanks.
Dawn