Cross-selling finding query.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dawn - 23 May 2008 04:11 GMT

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

Reply to this Message

Steve - 23 May 2008 16:07 GMT

Hi Dawn,

I can set this up for you for a very reasonable fee. Contact me at
rlaird@penn.com if you want my help.

Steve

> Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
> Want results as following:
[quoted text clipped - 12 lines]
> Thanks.
> Dawn

Reply to this Message

John W. Vinson - 23 May 2008 19:43 GMT

>Hi Dawn,
>
>I can set this up for you for a very reasonable fee. Contact me at
>rlaird@penn.com 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.

Signature

John W. Vinson [MVP]

Reply to this Message

Steve - 23 May 2008 16:12 GMT

Hi Dawn,

I can set this up for you for a very reasonable fee. Contact me at
rlaird@penn.com if you want my help.

Steve

> Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
> Want results as following:
[quoted text clipped - 12 lines]
> Thanks.
> Dawn

Reply to this Message

Jerry Whittle - 23 May 2008 16:16 GMT

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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
> Want results as following:
[quoted text clipped - 11 lines]
> Thanks.
> Dawn

Reply to this Message

John W. Vinson - 23 May 2008 19:47 GMT

>Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
>Want results as following:
[quoted text clipped - 11 lines]
>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.

Signature

John W. Vinson [MVP]

Reply to this Message

xiaojun - 30 May 2008 03:26 GMT

"Dawn" <Dawn@discussions.microsoft.com> Ð´ÈëÏûÏ¢ÐÂÎÅ:9FC4FBA9-9784-4E31-91B6-A679980AF809@microsoft.com...

> Table1 has 3 fields: customerid, goodstype, salesongoods, no pk.
> Want results as following:
[quoted text clipped - 12 lines]
> Thanks.
> Dawn

Reply to this Message