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!
LVL 2
smetterdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WMIFCommented:
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.
0
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
next
response.write total/numberOfFields


cheers,

Leo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)


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

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

Thanks  :)

Leo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.