Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

The query in an asp page --

            sqltemp="select * from 00preSurvey where userHash=" + "'" + rsUserRpt.Fields.Item("md5Hash").Value + "'"
            set rstemp=Server.CreateObject("adodb.Recordset")
            rstemp.open 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!
  • 3
1 Solution
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.
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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


Leo EikelmanDirector, IT and Business DevelopmentCommented:
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)

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

Leo EikelmanDirector, IT and Business DevelopmentCommented:
Yeah I hadn't tested it when I posted it, but it gave the general idea.

Thanks  :)


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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