Query to act like a pivot table???

[andrewf10]

Ive got a query (SearchCriteria) whose data looks like this:

Call ID....Problem Source............Problem Source2
15366.... Complaint....................
15224....Collateral Order............Complaint
15734....Delivery.......................
15733....Delivery......................Order Taken
15738....Delivery.......................
15137....Complaint.....................
14238....Other.........................Delivery
15072....Complaint....................Delivery



What Im trying to do is create a query on the back of the one above which will have Problem Source in column 1 and then 2 more columns with their counts in them. So:

Problem Source......CountOfProblem Source...........CountOfProblem Source2
Collateral Order................1..............................................1
Complaint.......................3...............................................1
Delivery..........................3..............................................2
Other.............................1..............................................0


Ive created a Query with this SQL:

SELECT SearchCriteria.[Problem Source], Count(SearchCriteria.[Problem Source]) AS [CountOfProblem Source], Count(SearchCriteria.[Problem Source2]) AS [CountOfProblem Source2]
FROM SearchCriteria
GROUP BY SearchCriteria.[Problem Source];

but this gives me:

Problem Source......CountOfProblem Source..........CountOfProblem Source2
Collateral Order.................1.........................................1
Complaint.........................3.........................................1
Delivery...........................3.........................................1
Other..............................1.........................................1


I guess this is because it's filtering on the first Problem Source and then looking for non-blanks in the second Problem Source which isnt what Im trying to do!

Any inspiration greatly appreciated as always...

 

[Rich]

Won't a crosstab give the results you want?

 

[andrewf10]

Good call Rich, worked well when hooked up with another query.

Thanks again