Multiple Combinations

[SASHA_D]

Hi Everyone,

I want to perform queries that count the number of customers using combinations of different products.
The data is organised like so:

CustomerID ProductType No of Items
1 A 5
1 B 1
1 C 2
2 D 2
3 B 2
3 B 3

and so on.

There are 5 or 6 product types. I need to be able to produce a table showing all the various combinations. For example, number of customers using JUST product type A and nothing else, number of customers using product type A and product type B, customers using product type A, B and C
and so on.

I think there are around 64 different combinations!
I was wondering if there is a quicker way to do this apart from writing 64 different queries!!

Hope this clear and hope someone can help!

thanks!

Sasha.

 

[Jon K]

A quicker way would be to build a crosstab query based on the data, using ProductType as the column headings:-

TRANSFORM Count([No of Items]) AS [CountOfNo of Items]
SELECT [CustomerID]
FROM [TableName]
GROUP BY [CustomerID]
PIVOT [ProductType] In (A,B,C,D,E,F);


Then create a criteria table to hold the criterion for each combination, one record for one criterion:-

[b]Products  Criteria[/b]
A	  [color=red][A]>0[/color] and IsNull([B]) and IsNull([C]) and IsNull([D]) and IsNull([E]) and IsNull([F])
B	  IsNull([A]) and [color=red][B]>0[/color] and IsNull([C]) and IsNull([D]) and IsNull([E]) and IsNull([F])
........................
AB	  [color=red][A]>0 and [B]>0[/color] and IsNull([C]) and IsNull([D]) and IsNull([E]) and IsNull([F])
BC	  IsNull([A]) and [color=red][B]>0 and [C]>0[/color] and IsNull([D]) and IsNull([E]) and IsNull([F])
........................

For 6 product types, you have 32 criteria including the unlikely one where all 6 product types are Null.


Finally build a query to display the NumberOfCustomers for each combination of product types:

SELECT [Products],
DCount("*","CrosstabQuery",[tblCriteria].[Criteria])+0 AS NumberOfCustomers
FROM tblCriteria;


If the table is large, instead of building the final query, you may add a NumberOfCustomers field in the criteria table and then, in VBA, loop through the criteria records in a recordset and update the NumberOfCustomers field with the numbers obtained by incorporating the criteria in a
"Select Count(*) from [CrosstabQuery]" statement.

Select Count(*) is more efficient than DCount("*")
.

 

[SASHA_D]

Hi Jon K, you posted this reply to my question a few weeks ago. Since then I have been on vacation and off work, so I've only statted to look at this again now! thanks again for your reply.

A colleague of mine who is a statistician assures me that for 6 product types there would be 64 possible combinations (incl null for all 6), not 32?
Anyway, there could be 8 or more product types now, which he says would be 256 possible combinations?

In any case I'm just wondering if you could explain this a little more?
For the first part you are saying produce a crosstab query that counts the number of items for each customerID and product type.
Then produce a criteria table that would I presume lists all the possible combinations? Would this have to be entered manually? So, if there are 64 or 255 possibilities I would have to enter all the different possibilities?

Then I would somehow run a query or write VBA that combines these two tables to give us the actual combinations?
I'm getting a bit lost now!

Any more help would be greatly appreciated! Thanks for your help so far..

Sasha

 

[SASHA_D]

Urgent help needed

Hi Everyone!,

Can anyone help with this post? It's now getting pretty urgent!

Any help would be greatly appreciated!

Sasha.