asked on

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 "-"
                  else
                        response.write rstemp.Fields.Item("q1").Value
                  end if
            rstemp.close
            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!

WMIF

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.

ASKER CERTIFIED SOLUTION

Leo Eikelman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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)

Leo

smetterd

ASKER

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

;-)

Leo Eikelman

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

Thanks :)

Leo