grouping data - possible crosstab

  • Thread starter Thread starter stuart
  • Start date Start date
S

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)
 
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
 
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?
 
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
 
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
 
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)
Click to expand...

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?
 
Back
Top