• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Average of numbers - count only records with complete data

I have a DB with a table that contains the core data (name, dept, RateJournal, RateBooks....). I have code written which will average the data for each field. The problem is some of the records do not have data for the "Rate.." fields. I want to count and average only those records for fields which have a number greater than 0. Not sure how to delete the incomplete records. Following is working code, but it is counting all records and then averaging. I am new to this type of calculations, please advise. Thanks.

'Number of records with priority rankings
sqlCount = "SELECT count(*) AS records FROM tblClinicians "
Set objCount = objConn.Execute(sqlCount)
sqlRank = "SELECT ROUND(AVG(tblClinicians.RateJournal), 2) AS RateJournal, " & _
                                                "ROUND(AVG(tblClinicians.RateBooks), 2) AS RateBooks, " & _
                                                "ROUND(AVG(tblClinicians.RateDatabase), 2) AS RateDatabase, " & _
                                                "ROUND(AVG(tblClinicians.RatePOC), 2) AS RatePOC, " & _
                                                "ROUND(AVG(tblClinicians.RateMultiple), 2) AS RateMultiple, " & _
                                                "ROUND(AVG(tblClinicians.RateOther), 2) AS RateOther " & _
            "FROM tblClinicians "
Set objRank = objConn.Execute(sqlRank)
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Subject" content="Library Orientation Evaluation">
<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>
<p><b><font size="2" face="Arial">Total number of evaluations completed (includes ONLY those responses where rankings were completed):
Ranking: importance of the type of online resources (#1 is first/top priority -
#2 is second/2nd priority)
  <li>Journals:  <%=objRank("RateJournal")%> </li>
  <li>Books:  <%=objRank("RateBooks")%> </li>
  <li>Database:  <%=objRank("RateDatabase")%> </li>
  <li>Point of Care Products:  <%=objRank("RatePOC")%> </li>
  <li>Multiple Resources in One:   <%=objRank("RateMultiple")%> </li>
  <li>Other:  <%=objRank("RateOther")%> </li>
1 Solution
re: "do not have data for the "Rate.." fields"
So, these fields probably allow NULL.
How about adding:
WHERE tblClinicians.RateJournal IS NOT NULL
  AND   tblClinicians.RateBooks  IS NOT NULL
  AND  etc.
HainKurtSr. System AnalystCommented:
How about adding ;)

WHERE isnull(tblClinicians.RateJournal, 0) > 0
  AND  etc.

for all fields that you want to eliminate...

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now