Averaging values in a query... MS Access/Classic asp/vbscript

Posted on 2006-04-12
Last Modified: 2008-02-01
The query in an asp page --

            sqltemp="select * from 00preSurvey where userHash=" + "'" + rsUserRpt.Fields.Item("md5Hash").Value + "'"
            set rstemp=Server.CreateObject("adodb.Recordset")
   sqltemp, "dsn=tsat_db", adopenstatic
                  if rstemp.EOF or rstemp.BOF then
                        response.write "-"
                        response.write rstemp.Fields.Item("q1").Value
                  end if
            set rstemp=nothing

Ok great, that writes the value of q1 very nicely. How can I rewrite the query so that it returns an average of the values in q1, q2, q3 etc? Not all tables have such nicely defined fieldnames, so programmatically writing the integer in front of the "q" is not an option... thanks!
Question by:smetterd
    LVL 22

    Expert Comment

    there are no functions that i can think of to automate this.  sql has an avg() function, but that averages whole columns.  you would need to modify either the written part or the select clause for each table because as you are explaining the column numbers arent consistent.
    LVL 8

    Accepted Solution

    so all you want is the average of values in all the fields?

    change this line:

    >>response.write rstemp.Fields.Item("q1").Value

    to this:

    numberOfFields = rstemp.Fields.length
    total = 0
    for i = 0 to i < numberOfFields
      total = total + rstemp.Fields.Item(i).value
    response.write total/numberOfFields


    LVL 8

    Expert Comment

    by:Leo Eikelman
    You might have to cast rstemp.Fields.Item(i).value to an integer or double or whatever types you are adding.

    CInt(rstemp.Fields.Item(i).value) (To cast to an integer)

    LVL 2

    Author Comment

    But I had to modify that one a bit... as you have it written it tries to average in their m5hash!

    LVL 8

    Expert Comment

    by:Leo Eikelman
    Yeah I hadn't tested it when I posted it, but it gave the general idea.

    Thanks  :)


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    730 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

    16 Experts available now in Live!

    Get 1:1 Help Now