Find total "Competent" calls for each employee (1 Viewer)

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
Hi Guys

Query to give me total number of Competent calls for each Staff members. But the following query doesn't give me the right result..OutcomeID=1 means Competent call. I want to display the total competent calls for each staff.

Code:
 SELECT tblstaff.[Staff Name], IIf([OutcomeID]=1,1,0) AS Total
FROM tblCallType INNER JOIN (tblstaff INNER JOIN tblAudit ON tblstaff.[Staff Number] = tblAudit.[Staff Number]) ON tblCallType.[Call TypeID] = tblAudit.[Call TypeID]
GROUP BY tblstaff.[Staff Name], IIf([OutcomeID]=1,1,0);

Any help will be much appreciated.

Thanks
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
And then eventually I want to display the result in the multicolumn listbox giving the following information:
1. Staff members
2.Total Calls for each Staff member
3. Total Competent Calls for each Staff member
4. %age for each Staff member

ANy help will be much appreciated. Many Thanks
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
Your first query needs to sum the IIf([OutcomeID]=1,1,0) AS Total field.
To get all the results you want will probably require a number of queries brought together in one query linked back to your staff ID, unless you are adept at writing sub queries.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:35
Joined
Feb 28, 2001
Messages
27,001
Aman, what did you intend to do with the listbox once you had it? Because listboxes are for selecting things.

Anyway, here is how you would do this for a listbox. First, make a query that shows you what you want to see. Since this query will almost certainly involve SQL Aggregates, you will have to use the SELECT field-specification syntax that resembles, for example, "Total of Calls as CallTotal," for each of the aggregated fields.

Once you have the query working, you can use the form controls wizard to populate your listbox from the query. Since you used the "AS" syntax, the fields having totals will have simple names for this kind of reference. The only trick is to get the query working first.

Now, on to the query. This might be a case where you want a nested query; that is, use a query to first define whether each call is competent. This query will have the IIF functions to report 0 or 1 for each case (and probably should have the "AS" syntax at this level, too). Then just use the query wizard to do a summation query on the query that you built to quantify the results as 0 or 1. Then this summation query can feed back to your listbox if that is what you really wanted.

If you weren't actually planning to select anything from the list box, you might wish to instead consider using a sub-form to display the query results in a scrollable manner. But that's just a matter of preference if your point was merely to display results, not to select results.
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
Can you please help me to amend the query to solve the purpose? I want to display total number of competent calls for each staff.

Code:
 SELECT tblstaff.[Staff Name], IIf([OutcomeID]=1,1,0) AS Total
FROM tblstaff INNER JOIN tblAudit ON tblstaff.[Staff Number] = tblAudit.[Staff Number]
GROUP BY tblstaff.[Staff Name], IIf([OutcomeID]=1,1,0);
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
Yes - like I said - simply sum the IIf statement ;
Code:
SELECT tblstaff.[Staff Name], SUM(IIf([OutcomeID]=1,1,0)) AS Total
FROM tblstaff INNER JOIN tblAudit ON tblstaff.[Staff Number] = tblAudit.[Staff Number]
GROUP BY tblstaff.[Staff Name];
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
Thanks Minty, If I want to display total Calls,total competent calls and %age as well in the query. Is it possible?
I want to display in a listbox all that information.
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
Assuming every call record has a OutcomeID then add another calculated field :
Code:
Count(OutcomeID) as NoOfRecs.

To get your percentage then you need to use the following as you can't refer to the calculated fields in the same query;

Code:
(Count(OutcomeID)  / SUM(IIf([OutcomeID]=1,1,0))) * 100 as PercCompt
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
Thanks Minty, It worked. Just want to know that the last column which gives %age ..can we set it to 2 decimal points only?
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
Thanks Minty, It worked. Just want to know that the last column which gives %age ..can we set it to 2 decimal points only?

Yes - apply the Fixed format to the field, or remove the * 100 bit and format as percentage with 2 decimal places.
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
Thanks. Great Minty. If I want to display the result for rolling 12 months then I will need to write another condition on [DateOfObservation] field. Can you please help me in this?

Thanks
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
I will have a look at it. And also if I want to display RAG rating in the homepage for the Staff. What is the best way to do it based on their competency ?
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
I will have a look at it. And also if I want to display RAG rating in the homepage for the Staff. What is the best way to do it based on their competency ?
I have no idea what an RAG rating is sorry.
 

aman

Registered User.
Local time
Yesterday, 22:35
Joined
Oct 16, 2008
Messages
1,250
RAG rating meaning (Red,Amber and Green) based on the competency of calls.
 

Minty

AWF VIP
Local time
Today, 05:35
Joined
Jul 26, 2013
Messages
10,355
This is more sums based on what data you already have. So you have a percentage calculation already. Use a copy of that to determine your Red, Amber, Green output. Something like (Untested but you should get the idea);

Code:
IIf ((Count(OutcomeID)  / SUM(IIf([OutcomeID]=1,1,0))) < .5 , 'Red', IIf((Count(OutcomeID)  / SUM(IIf([OutcomeID]=1,1,0))) <.75, 'Amber', 'Green'))

Do you see how that works ?

There are other ways - you can use the Switch function if there are more arguments as the syntax for nested IIf statements gets messy.
 

Users who are viewing this thread

Top Bottom