Calculating a Total Score and Ignoring Null Values 1

[laina222]

I am in the process of creating a database for a call center to calculate scores for people's phone calls. They are required to meet a certain level of professionalism and obviously follow the script, and their scores reflect that.
On the table, most of the fields are criteria the people are being scored on, but not all the criteria are applicable in every situation. The scores are on a 0-2 range, 0 being "did not meet criteria" and 2 being "met criteria perfectly". If the criteria is not applicable to the call (for example, there are criteria based on taking a credit card but not all customers pay with a credit card), then the score is left as NULL.
What I need help with is calculating the overall score. If the field for the criteria is left NULL, then I don't want it counted.
For all fields not left NULL, I want the query to calculate how many of those fields are not NULL and multiply the count by 2, and then take the actual score and divide it by the (count of fields NOT NULL*2). Also keep in mind that not all the fields in the table are criteria, so I need to specify which fields are relevant to this calculation.
Any help is appreciated. Thanks in advance!

 

[r937]

you're in luck!! aggregate functions ignore nulls!!

select sum(criteria)/(count(criteria)*2) as score ...

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Take a look at the Count aggregate function for the diff between Count(*) and Count([field name]).
You may also consider the IIf and IsNull functions.

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

 

[dhookom]

I doubt laina222 is in luck since I think the application "commits spreadsheet" and values are to be averaged across fields rather than across records like they should be.

Maybe laina222 could provide some sample records with field and table names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[r937]

the application "commits spreadsheet"

huh?

rudy

http://r937.com/

SQL Consulting

 

[dhookom]

Committing spreadsheet is using a relational database like you would a spreadsheet with multiple fields rather than multiple records.

For instance, fields like:
CallID Score1 Score2 Score3 ...

Which might better be structured:
CallID ScoreType ScoreValue



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[r937]

okay, i get it

first time i've heard that terminology, but it makes sense



rudy

http://r937.com/

SQL Consulting

 

[laina222]

What I really need is to insert a column in the table, it seems, after going through all of these responses. The column should be based on adding the Criteria from each row.

Criteria1, Criteria2,....Criteria50 (yes, I have 50!)
Some of the criteria will definitely be NULL.
If the Criteria is not NULL, then it is a score from 0-2.

So let's say that there's really only 5 criteria, for example's sake:
Field Value
Criteria1 2
Criteria2 NULL
Criteria3 1
Criteria4 0
Criteria5 2

Then I want to add a field to my table called "TotalScore", which would be all Criteria that are not NULL added together, divided by the count of non-NULL Criteria * 2

Hope that helps.

 

[r937]

ouch

duane was right

any chance you can redesign the table to the way he suggested?

because the way it is now, the sql is going to get really, really ugly

rudy

http://r937.com/

SQL Consulting

 

[laina222]

I might try that. But then I'll have multiple records per call and I think that's also getting ugly in terms of table structure and entering data on a form. I'm okay with ugly SQL - it's ugly tables I hate!

 

[r937]

i think if you were to ask a large number of database professionals, the consensus would be overwhelming that your table is ugly and the one with 3 columns and multiple rows is a lot neater

no offence

rudy

http://r937.com/

SQL Consulting

 

[dhookom]

laina222,
You might want to look at my "At Your Survey" found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This is how I would set up your application. Depending on your requirements, different call types might filter the append of question records. This would require a table that stores a call type and question ID.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

And be sure to read 'The Fundamentals of Relational Database Design'.

But then I'll have multiple records per call and I think that's also getting ugly in terms of table structure and entering data on a form

multiple records per call is the "correct" way to design this structure according to established database theory.

If you need help with correcting the design, feel free to post some more information about your system and we can help you develop a database that will conform to the rules!

Leslie