query question

[andrew29]

HI everyone,

I have a table with account number and account type information in the format below:

0001 accountA
0001 accountB
0001 accountC
0001 accountD
0002 accountB
0003 accountD
0004 accountF
0005 accountA
0005 accountB
0005 accountF

What I want to determine is what percentage of accounts have only one account type, what percentage have 2 account types, what percentage have 3 account types etc.

I also need to know what percentage of our customers have different account type combinations (eg accountA and AccountC but nothing else).

Whats the best way to approach this task?

Cheers,

 

[SydneyGeek]

Maybe you could start with a Crosstab. Account Number as a Row Heading, Account Type as a Column Heading, and Account Type as a Value (Count).
That will give you a 2-D table with a table for each account type, and a many rows as you have accounts. Then base another query on this query, to generate calculations of the varying column combinations and / or column totals.

Denis

 

[andrew29]

Thanks Denis,

Thats what I've tried and it seems to have worked a treat!

Thanks for your help!!