Using OR and IIF

[elainenguyen]

Hi, I am doing a querry to calculate the customer satisfacation in my division and I need to know how many 5(always exceed) the employee received in one evaluation from a customer for all the questions. And there are 20 quetsions.
Here is my code that I wrote in the querry, but the count is not right, it gave me the total of 6, but the total suppose to be 20 because I have 5 of 5in performance_1, 5 of 5 in performance_2, 5 of 5 in performance_3, and 5 of 5 in performance_4.

Count 5:

Count(IIf([performance_1]=5 Or [Performance_2]=5 Or [Performance_3]=5 Or [Performance_4]=5 Or [Performance_5]=5 Or [Performance_6]=5 Or [Performance_7]=5 Or [Performance_8]=5 Or [Performance_9]=5 Or [Performance_10]=5 Or [Performance_11]=5 Or [Performance_12] Or [Performance_13]=5 Or [Performance_14]=5 Or [Professionalism]=5 Or [Confidentiality]=5 Or [Ownership]=5 Or [Accountability]=5 Or [Commitment]=5 Or [Communication]=5 Or [Telephone]=5 Or [Stewardship]=5 Or [Safety]=5 Or [Caring]=5 Or [Overall]=5,"yes"))

can somebody help please. thanks a lot and have a nice day.
Elaine

[nico5038]

Hi, I am doing a querry to calculate the customer satisfacation in my division and I need to know how many 5(always exceed) the employee received in one evaluation from a customer for all the questions. And there are 20 quetsions.
Here is my code that I wrote in the querry, but the count is not right, it gave me the total of 6, but the total suppose to be 20 because I have 5 of 5in performance_1, 5 of 5 in performance_2, 5 of 5 in performance_3, and 5 of 5 in performance_4.

Count 5: Count(IIf([performance_1]=5 Or [Performance_2]=5 Or [Performance_3]=5 Or [Performance_4]=5 Or [Performance_5]=5 Or [Performance_6]=5 Or [Performance_7]=5 Or [Performance_8]=5 Or [Performance_9]=5 Or [Performance_10]=5 Or [Performance_11]=5 Or [Performance_12] Or [Performance_13]=5 Or [Performance_14]=5 Or [Professionalism]=5 Or [Confidentiality]=5 Or [Ownership]=5 Or [Accountability]=5 Or [Commitment]=5 Or [Communication]=5 Or [Telephone]=5 Or [Stewardship]=5 Or [Safety]=5 Or [Caring]=5 Or [Overall]=5,"yes"))
can somebody help please. thanks a lot and have a nice day.
Elaine

Hi Elaine,

Looks like your table isn't really "normalized", but this problem I guess is Nulls related.
When there's one of the fields holding a Null (nothing/unknown) value, the comparison won't work.
The solution will be to use the NZ() function like:
Count 5: Count(IIf(NZ([performance_1])=5 Or NZ([Performance_2])=5 Or NZ([Performance_3])=5 .... etc..

Getting the idea ?

Nic;o)

[Scott Price]

In addition to what Nico suggests, I think you are also expecting something different from the Count() function than what it does!

Count() only 'counts' the occurrences of a certain field according to your criteria. You sound like you're trying to accomplish more of a Sum() function.

Regards,
Scott

[NeoPa]

Elaine,

As Scott says, I think we may need a bit of a rethink here.
Can you post your record layout for us (Table Meta-Data) and describe how it works (what it means). That way we can suggest a more appropriate way to process the data.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[elainenguyen]

HI,
Here is my table data structure:
tblAdmin (for table Administration)
fields:
performance_1, type is number, byte
performance_2, type is number, byte,
performance_3, type is number, byte, etc.

For all these fields, the criteria is
5 is Always Exceeds
4 is Often Exceeds
3 is Consistently meets
2 is Sometimes meets
1 is Does not Meet
0 is N/A

I want to have a querry that count how many 5 "this person" has for all the customer evaluation questions re: performance_1, performance_, etc.
Thanks!

[Scott Price]

Hi Elaine,

A little further clarification, please. In your first post you sound as if you want to add the 5's together, to result in 20 (four 5's), while in your last post you sound as if you want to count how many 5's there are 4 (four 5's).

Which is it?

Count() returns how many 5's there are.

Sum() adds the 5's together.

Regards,
Scott

[elainenguyen]

Scott,
Sorry for the confusion, I want to count how many 5's this employee received for his/her evaluation, not the sum.
for example. There are 20 questions in an evaluation form, this person completed 2 surveys for Jan, therefore, the total of question is 40. I want to coutn how many 5 she received. For example if she received 20. the rate for her "Always exceeds" will be 20/40.
Thanks!

[Scott Price]

Elaine,

Sorry to be the bearer of bad news, but I'm not sure that you can do it the way you are trying.

An alternative approach (although quite grotesque) is to use a number of queries, pulling the count of each category, which you will then sum in another query.

Here's an example of the first query:

SELECT tblAdmin.EmployeeID, Count(tblAdmin.Performance_1) AS CountOfPerformance_1
FROM tblAdmin
GROUP BY tblAdmin.EmployeeID, tblAdmin.Performance_1
HAVING (((tblAdmin.EmployeeID)=1) AND ((tblAdmin.Performance_1)="5"));
 

Regards,
Scott

[nico5038]

Hmm, as stated before, your table should have been designed to hold one row per question like:
EmployeeA, Q1, Answer1
EmployeeA, Q2, Answer2
EmployeeA, Q3, Answer3
...etc...
EmployeeB, Q1, Answer1
...etc...

Having such a table will allow a GroupBy query counting the number of "5" values per user easily.

We can "fake" such a table by using a UNION query that's generating such a table and then we can use the UNION for the needed GroupBy.

The UNION will look like:

Select EmployeeID, "performance_1" as Question, [performance_1] as Answer from tblAdmin
UNION
Select EmployeeID, "performance_2" as Question, [performance_2] as Answer from tblAdmin
UNION
Select EmployeeID, "performance_3" as Question, [performance_3] as Answer from tblAdmin
UNION
... etc ...
 

Getting the idea ?

Nic;o)

[NeoPa]

Elaine,

How many performance_ fields are there in the record?

[elainenguyen]

Thanks for everyone's help.
There are about 25 questions per survey. I've tried Scott's way, it works. But since I have to use one column per performance question per category, and there are 6 categories re: 5, 4, 3, 2, 1 and 0; I am afraid that I might run out of of columns, but I'll try.
Thanks!

Elaine

[nico5038]

Hmm, would still want to ask you to try the UNION I proposed and after defining it for e.g. 5 questions use it in a crosstable query.
It will allow then one line for each question and for each category a column.
Also the pivot will be possible showing the six categories and the questions as columns.

Nic;o)

[elainenguyen]

Thanks Nico for your help, I was able to complete the querry. It's working now.
Thanks!
Elaine