Average of numbers - count only records with complete data

Posted on 2009-12-18
Last Modified: 2012-05-08
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>
Question by:JLohman
    LVL 31

    Accepted 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.
    LVL 51

    Expert Comment

    How about adding ;)

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

    for all fields that you want to eliminate...

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now