Counting Unque values when grouping by another column

[stevehowe]

Hi, this is probably rediculously easy but I have trawled through the forum and cannot find anything that helps me crack this problem.

I have an excel spreadsheet containing workflow information containing case number, process name and duration. I have dropped the data into Access and am trying to do some reporting on the raw data.

What I need to do is work out how many cases have been through each process, how much the total duration is per process and also the average time per case for each process. So I have this:

SELECT Count(NewCPSMI.[Case#]) AS CaseCount, NewCPSMI.TASK, Sum(NewCPSMI.DURATION) AS SumOfDURATION
FROM NewCPSMI
GROUP BY NewCPSMI.TASK;

So this gives me:

CaseCount TASK SumOfDURATION
18 Assess Credit Card 5499.59
3 Assess Home Loan Transfer 1329.07
11 Assess Personal Loan 5727.18
2 Assess Revolving Credit 684.09

Which is a start, but what I really want to know is whether that 18 cases in the "Assess Credit Card" process is 18 cases or 1 case which has been into and out of the queue 18 times.

What I really want to do is a select count(DISTINCT NewCPSMI.[Case#]) but for some reason MS Access 2003 doesn't like me if I put DISTINCT into the query, I have also tried UNIQUE and it was no more helpful. The MS developernet pages are totally unhelpful so please help me!!!!!

 

[PHV]

Create a query named, say, qryDistinctCase:

SELECT TASK, [Case#], Sum(DURATION) AS totDURATION
FROM NewCPSMI
GROUP BY TASK, [Case#];

And now your query:

SELECT Count(*) AS CaseCount, TASK, Sum(totDURATION) AS SumOfDURATION
FROM qryDistinctCase
GROUP BY TASK;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886