Multiple Counting SQL

[wxkeep]

This is probably a very easy question. I have one database that contains several columns of data. In this instance I'm only concerned with two: Customers and Status.

Now, typically there are only about 10 customers - but there are 75-100 records (meaning that some customers have several records). Status is either pending, shipped, or received. What I would like to do is count the number of each staus for each customer so that I could have a final readout like this:

Customer A
pending - 10 shipped - 17 received - 5
Customer B
pending - 11 shipped - 4 received - 7
etc.

I'm using Access 2000 - and working with several things in the long haul. Essentially I want the count of each pending/shipped/received, the total amount of sales for each of those three categories, the average sales account for each of those three categories, etc for each customer. Currently I have designed 3 different queries, one for each category that pulls the numbers and I thought to have the report reference those three queries - but I thought I would check first to see if there is an easier way.

 

[PHV]

A starting point:
SELECT CustomerID
,Sum(IIf(Status='pending', 1, 0)) As pending
,Sum(IIf(Status='shipped', 1, 0)) As shipped
,Sum(IIf(Status='received', 1, 0)) As received
FROM yourTable
GROUP BY CustomerID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bkclaw113]

Have you tried using the Group By clause to group on STATUS? This should allow you to get the result for each distinct Status.

Also in the future you might find the Microsoft: Access Queries and JET SQL forum a little more helpful for these types of questions unless you are trying to find out how to use ADODB recordsets with your output or something like that.