Auto-Generate table from data...

[MSherfey]

I hope this is the right description for my question.

I am trying to build a table which contains the permutations or combinations of different groups. It needs to show all the possible combinations available. I have this done in Excel pretty easily and have imported it into Access for now, but when things change/grow it will become an issue. Here's what it looks like:

1) Every group has a value which is double the preceding group's value
2) When the groups get added to each other they generate a unique value for that permutation or combination.

EX.

1 Group A
2 Group B
4 Group C
8 Group D
etc...

Output:
1 A
2 B
3 B+A
4 C
5 C+A
6 C+B
7 C+B+A
8 D
9 D+A
etc.....

As you can see, this is pretty easy to do in Excel, but when the groups change (not bad I guess) or when you exceed Excel's row limit (I've already done that with 20 groups) what do you do? This is why I thought Access would be better except I have NO IDEA how to generate the values.

Any thoughts?

 

[ajetrumpet]

obviously you'll need some code to do this, but it is very complex. you'll need one table with the values in it, split up into 2 fields, one for the number portion, and one for the value portion, and then a large function to iterate through the table and generate the number of rows for each combination.

 

[MSherfey]

Well, I have the first table already since I use it in a few other queries. It's the function I need help with. I think I can figure out the function itself, but I have no idea how to call the function to create the table.

Any suggestions?

 

[ajetrumpet]

creating the table initially would be the best idea I think. then write the values that the function gives to the recordset .

 

[MSherfey]

What do you mean? I already have a table with the default values of each of the groups. I just don't know how to use a function to fill-in another table.

 

[gemma-the-husky]

how many groups do you have. If its no more than 16, its quite easy.

this just sounds like a binary problem, so you may be able to do this simply with some bitwise arithmetic.

You can use a long number (16 bits) to manage up to 16 groups. If you have more then its slightly trickier, as there is no native integer data type.

 

[MSherfey]

Well, right now I have 20 groups which gives me 1048575 combinations. If I add another group it'll double each time. I'm afraid I may be reaching the limits of Access the same way I did for Excel.

I could limit the groups to 16 and use generic names for the groups (Group1, Group2, etc). Then when the reports are finished, just export to Excel and run a find/replace for their correct group names. This would allow me to keep a more manageable number of groups as well as remain flexible enough when the criteria changes.

How does that sound? If good, how would you recommend creating the combinations in the tables?

 

[gemma-the-husky]

sorry, I think i was wrong - a long is a 4byte word, so a long will let you manage up to 31 groups, or 2billion.

The thing is - why do you want to print out the combinations - its just too many rows to display them all , so what are you trying to do with the data.

 

[MSherfey]

The idea is to show different product combinations so the sales teams can decide where to focus their efforts. If a customer has product A and B, then product C is a natural fit for them. The problem is you can't get this data easily by basic queries unless you know the exact combinations you are looking for. When you start adding the different groups together, these combinations can get rather large. If I keep the groups to 2-3 then it is easy to manage. However, we have quite a few products and the sales teams are needing to know the adoption rate for all or products.....this is quite large

I have the values done already since this is a simple SUM function when the group queries are combined. However, the product grouping of 417 doesn't mean anything to them where Group9+Group8+Group6+Group1 (256+128+32+1=417) does. Now on the exported Excel file they look for the specific groupings they are interested in and can find the number of customers in this group as well as the revenue they represent (is it worth their time) and the specifics of those customers (Geo, size segment, industry, etc) for further contact.

It's actually quite interesting how you can look at the data when you start to combine customer purchases and not just their specific revenue potential. In short, it's a 'white field' report.

 

[gemma-the-husky]

ok - so what you need is a way of selecting product groups from a list (eg multi-select list box) and then finding customers who have bought all the selected products - somewthing like that?

I think you need to think about presentation/selection - because you just cant inspect a table with 1milion rows - even if you COULD generate one. This is the sort of thing that you need to achieve by exception etc.

 

[MSherfey]

I think I see what you're saying, but I may be using it in a different way. Once the 'summary' query is complete I am left with list of customers and their grouping value. The list is never greater than the number of customers. That list is joined with the grouping table (with all the different combinations) and it returns the customer information with the text value of the combinations and not the summed value of the combinations.

What I'm needing is a way to generate the grouping table. This way when the products change I have a simpler way to get the new information. I would only need to change the query for the product value to look for a different product and the grouping table so the correct name is applied to the value.