Crosstab with multiple columns?

[Guest]

When doing a crosstab query based from a multi-table query, is there a way to
use a count summary for more than one column? EG: I have a table of assets
and related customers, and a table of customer support cases and related
customers. Is there an easy way to generate a cross tab which will summarize
the record count for assets AND the record count for cases which a particular
customer is related to? (Apologies for, well, being a noob to Access; I'm
predominantly an Excel or SQL guy.) Thanks for any assistance anyone can
throw my way.

Sincerely,

Jamie W.

 

[Guest]

Post a sample of your data with field names.

 

[Guest]

Karl,

Unfortunately, I'm unable to do so for a while due to legal issues--my
employer filed their S1 last month and as a result, even minutae like this is
restricted. Understandably, that makes any assistance much harder, and
generating dummy info to replicate the issue might be the path of least
resistance. If I'm unable to drum up a solution, you can bet I'll be back
here with some dummied data. Thanks

Sincerely,

Jamie W.

 

[Guest]

This might give you some ideas of how to get your results.
C-Table ---
Ord # Item Qty
100 B-Plan 2
100 CP-PO 1
100 BP-OP 2
145 DD-OP 4
133 AA-Plan 5

This query uses a table CountNumber with a field CountNUM containing numbers
from zero through maximum needed.
[C-TableCount] ---
SELECT [C-Table].[Ord #], CountNumber.CountNUM, Count([C-Table].Item) AS
CountOfItem
FROM [C-Table], CountNumber
GROUP BY [C-Table].[Ord #], CountNumber.CountNUM
HAVING (((CountNumber.CountNUM)<=Count([item])));
Query results ---
Ord # CountNUM CountOfItem
100 0 3
100 1 3
100 2 3
100 3 3
133 0 1
133 1 1
145 0 1
145 1 1

Crosstab query ---
TRANSFORM First([QTY] & " " & [Item]) AS Expr1
SELECT [C-Table].[Ord #]
FROM [C-Table] INNER JOIN [C-TableCount] ON [C-Table].[Ord #] =
[C-TableCount].[Ord #]
GROUP BY [C-Table].[Ord #]
PIVOT "Item " & [CountNUM];

Query results ---
Ord # Item 0 Item 1 Item 2 Item 3
100 2 BP-OP 2 BP-OP 2 BP-OP 2 BP-OP
133 5 AA-Plan 5 AA-Plan
145 4 DD-OP 4 DD-OP