How to create table comparing the number of customers who purchased Product A as well as Product C, D, etc...

[trojans1975]

I'm not sure my title explained it well enough. What I am trying to do is create a crosstab that would have the same column headers as row headers and would show a total count of customers that purchased a product in on segment and also purchased in another segment. For example:

Prod Mrkt Category	Prod A	Prod B	Prod C
Prod A	100	?	?
Prod B	?	150	?
Prod C	?	?	200

<tbody>
</tbody>

so in the above example 100 people purchased prod a. What i need to also show is (looking down the prod a column) How many people purchased prod a AND prod b, how many people purchased prod A AND prod c, etc... Just by doing a basic cross tab i can get the numbers i show above, it's the "?" numbers i really am not sure how to even begin showing.

In my table there is a unique id number for each individual customer so if a customer purchased all 3 products that unique id would be the same for that one customer:

123456 - Joe Smith - Prod A
123456 - Joe Smith - Prod B
123456 - Joe Smith - Prod C

Any help you can provide would be great.

Thanks You

 

[sheetspread]

sumifs

 

[trojans1975]

sumifs

I understand what a sumif function does. But I'm fairly new to the access world so I am not sure where I would use this function or how to write it correctly.

 

[sheetspread]

Oh sorry, I thought this was an Excel question. In Access you might get this to work with a join query, which can be added via the wizard or sql window. I don't have access on this pc right now, I might have the cd rom somewhere. If I can find it I'll try to write something.

 

[sheetspread]

Ok in a table named access, I have:

Excel 2010
	A	B	C
1	number	name	product
2	123456	Joe Smith	Prod A
3	123456	Joe Smith	Prod B
4	789101	Kermit	Prod A
5	789101	Kermit	Prod C
6	987654	Alf	Prod A
7	987654	Alf	Prod B
8	987654	Alf	Prod C
Sheet10

Take a blank query and paste this in the sql view:

SELECT X.name, X.product, (select count(product) from access where name = x.name and product = "Prod A") AS [Prod A], (select count(product) from access where name = x.name and product = "Prod B") AS [Prod B], (select count(product) from access where name = x.name and product = "Prod C") AS [Prod C]
FROM access AS X;

then in another blank query sql view paste:

SELECT DISTINCTROW [access Query].product, Sum([access Query].[Prod A]) AS [Prod A], Sum([access Query].[Prod B]) AS [Prod B], Sum([access Query].[Prod C]) AS [Prod C]
FROM [access Query]
GROUP BY [access Query].product;

which should get you:

Excel 2010
	A	B	C	D
1	product	Prod A	Prod B	Prod C
2	Prod A	3	2	2
3	Prod B	2	2	1
4	Prod C	2	1	2
Sheet11

Is that what you're looking for? Btw, a simple sum or countifs in excel won't be enough for this contrary to what I believed earlier.

 

[sheetspread]

This one's better:

TRANSFORM Sum(1) AS Expr1
SELECT access.product
FROM access AS access_1 INNER JOIN access ON access_1.name = access.name
GROUP BY access.product
ORDER BY access.product, access_1.product
PIVOT access_1.product;

 

[trojans1975]

this provided the correct format that i needed but the actual data it output was grossly overstated. I obviously edited it to reflect actual table and/or query names i'm using. In one instance the output in a cell was over 5 times higher than it should have been. There doesn't seem to be a rhyme or reason as to how much each cell is off.

 

[sheetspread]

Can you post a longer sample on which the query is repeating totals?