grouping data - possible crosstab

[stuart]

hi

i have a data set where a customer can have product A, B, C, D, E or F with
a specific value
what i want to produce is a table showing where a customer has a combination
of these for all possible combinations eg

A B C D E F custs value
Y N N N N N 400 £100
Y Y N N N N 200 £2000
N Y Y N N N 50 £50

i can easily use a cross tab wizard to say for each customer which do they
have but how do i do the above
your thoughts are welcomed

regards

me
(a confused access beginner)

 

[John Spencer MVP]

What is Value? Is it the total sum of costs associated with the number of
customers? Is it the sum of costs for one customer that gets the marked items?

You know your table structure and what is stored in it. We don't.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

What does the original data look like?

Let me get this straight, you want to identify for each of the possible
combinations of products (A,B,C,D,E and F) the number of customers you have
for that combination? Is that correct?

Do you only want to count the customer once (so if they purchase A, B, and C)
they would only show up on the line that shows A, B, and C as "Y", or would
they also show up on each of the lines for "A", "B", "C", "AB", "AC", and
"BC"?

Dale

 

[KARL DEWEY]

You said what you want as a result but the problem is you did not say how
your data is stored now.
What is your table structure? Field names and data type?

 

[stuart]

apologies

table is stored as

customer number productId value
123456 A 100
123456 B 50
987654 C 80
654789 A 50
852369 B 50
852369 A 50

therefore my output would i hope be
A B C Custs Value
Y Y N 2
N N Y 1
N Y N 1

i have reduced it down to 3 for purposes of example

thanks in advance

 

[stuart]

im sorry for not being very clear - im a bit confused myself (been told that
alot)

table structure
ClientID Text
ProductID Text
Value Number


there are only X number of products therefore the client can only appear in
the
table up to X times (in this case 6 but ive previously reposted as 3)

a client can have any combination of the products from 1 to X so they could
have
client Product
1 A
1 B
1 C
2 A
3 B
3 C
4 A
4 B
4 C

ive been asked to summarise this by product combinations
therefore my output would be
A B C number clients
Y Y Y 2
N Y Y 1
Y N N 2




ive been asked to summarise for all possible combinations of the range of
products

 

[Dale_Fye via AccessMonster.com]

I think you mean:

A B C Custs Value
Y Y N 2
N N Y 1
Y N N 1

But you left off the "Value", and from your example, I see two different
values (50 and 100) for the ProductID "A", so, what would go in the Value
column of the first row?

Dale

apologies

table is stored as

customer number productId value
123456 A 100
123456 B 50
987654 C 80
654789 A 50
852369 B 50
852369 A 50

therefore my output would i hope be
A B C Custs Value
Y Y N 2
N N Y 1
N Y N 1

i have reduced it down to 3 for purposes of example

thanks in advance

You said what you want as a result but the problem is you did not say how
your data is stored now.

[quoted text clipped - 20 lines]

 

[John W. Vinson]

im sorry for not being very clear - im a bit confused myself (been told that
alot)

table structure
ClientID Text
ProductID Text
Value Number


there are only X number of products therefore the client can only appear in
the
table up to X times (in this case 6 but ive previously reposted as 3)

There will be 6! = 720 possible combinations; if you have ten products you'll
have 10! = 3628800 possible combinations. Are you QUITE CERTAIN that you want
this deluge of data? It's ok for three products but much beyond that you'll
just be getting a whole lot of data that may not be much use: why would you
calre if there were a few more customers who bought ABDEF than ABCEF?

 

[Dale_Fye via AccessMonster.com]

Actually, John, that would be 6! permutations, which would make sense if you
were trying to line the 6 products up in every possible sequence. But that
is not what he wants, he wants the number of possible combinations of the 6
items. If my math serves me right that would be

n! / ( (n-r)! * r! )

and if you take the sum of that over r ranging from 1 to 6, I think you come
up with 63 possible combinations of the 6 products (still not trivial).

Dale

im sorry for not being very clear - im a bit confused myself (been told that
alot)

[quoted text clipped - 7 lines]

the
table up to X times (in this case 6 but ive previously reposted as 3)

There will be 6! = 720 possible combinations; if you have ten products you'll
have 10! = 3628800 possible combinations. Are you QUITE CERTAIN that you want
this deluge of data? It's ok for three products but much beyond that you'll
just be getting a whole lot of data that may not be much use: why would you
calre if there were a few more customers who bought ABDEF than ABCEF?

 

[Dale_Fye via AccessMonster.com]

Stuart.

I think I would start with:

TRANSFORM Count(ProductID) AS CountOfProdID
SELECT tbl_Products.CustID, Sum(tbl_Products.ProdValue) AS SumOfProdValue
FROM tbl_Products
GROUP BY tbl_Products.CustID
PIVOT tbl_Products.ProdID In ("A","B","C","D","E","F");

Save this query. It will give you something that looks like:

CustID SumOfProdValue A B C D E F
123456 150 1 1
654789 50 1
852369 100 1 1
987654 80 1

From here, you can create a aggregate query that groups on the product and
SumOfProdValue columns and Counts the CustID. Something like:

SELECT A, B, C, D, E, F, SumOfProdValue, Count(qry_Product1.CustID) AS
CountOfCustID
FROM qry_Product1
GROUP BY A, B, C, D, E, F, SumOfProdValue;

Or, if you just want to sum the "SumOfProdValue" column,

SELECT A, B, C, D, E, F,
Sum(SumOfProdValue) as CumValue,
Count(qry_Product1.CustID) AS CountOfCustID
FROM qry_Product1
GROUP BY A, B, C, D, E, F, SumOfProdValue;

HTH
Dale

apologies

table is stored as

customer number productId value
123456 A 100
123456 B 50
987654 C 80
654789 A 50
852369 B 50
852369 A 50

therefore my output would i hope be
A B C Custs Value
Y Y N 2
N N Y 1
N Y N 1

i have reduced it down to 3 for purposes of example

thanks in advance

You said what you want as a result but the problem is you did not say how
your data is stored now.

[quoted text clipped - 20 lines]