[RESOLVED] Access 2002: adding multiple text boxes values

[jmhfloor]

I am trying to create a survey input database. Guests fill out a survey. An employee inputs the data in a form. The information is numerical. I can make the information write to the table but I do not know how to make that information sum. Such as:

Reservation Services has 3 questions. I want to be able to take the sum of all the survey's entered and give me the average.


Can anyone help?

[salvelinus]

If users enter the data in a textbox, the data will be text, not numeric, even if the text is a number. There are several functions you can use to convert text to numeric, such as Val, CInt, CDbl, CLng, depending on your needs. Probably better to avoid CInt, since the result of your average may not be an integer. Something like:

Set rs = db.OpenRecordset("SELECT fldFoo FROM [Reservation Services]")
rs.MoveFirst
Do While Not rs.EOF
   total = total + CDbl(rs!fldFoo)
Loop
avg = total\rs.RecordCount

[DKenny]

Or use a SQL statement

SELECT 	AVG(YourField)
FROM	YourTable

[jmhfloor]

Dkenny,

SELECT AVG(YourField) FROM YourTable

The AVG(Yourfield) would that be the row names? Such as if the table name was tbltest and the field names was res1, res2, res3

would it be
Select AVG(res1!res2!res3)
From tbltest

[jmhfloor]

Salvelinus,

You lost me on your first 2 lines of code.

Set rs = db.OpenRecordset("SELECT fldFoo FROM [Reservation Services]")
rs.MoveFirst

[DKenny]

Sorry, I though you wanted the average down a column (field) rather than across a row (record).

To get the average across a row you would add the following calc to a SQL statement
((res1 + res2 + res3)/3)

e.g.

SELECT 	SurveyID (or some other field)
	,more fields....
	,(res1 + res2 + res3)/3 as 3QuestionAverage
FROM	YourTable

[salvelinus]

The SQL will work if the field is numeric, but it sounded like it was text.
jmh, that was just a code sequence. db = a declared instance aof the database, rs is a recordset.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim total as Double, avg as Double
Set db = CurrentDB
'...then the rest of the code

although I did make the same assumption as DKenny, my example averages averages a field, not a record.

[DKenny]

If they are text fields, you will just need to do a type conversion with the SQL statement.

[jmhfloor]

Actually you are both right.

What I have to do is average the 3 questions on the first section. Then if there are 150 surveys take the value for the average add them together and devide that by the 150 so I get the total average of the section over all the survey data.


example.
If questions 1,2,3 answers are 3,4,5. 3+4+5=12/3=4 4 is the number I need. Then if there are 150 survets I need to take the final number on all of them to tell me over all what I am looking for.

[DKenny]

You don't need to worry about how mant surveys there are if you use a SQL statement to calculate the Grand Average.

SELECT 	AVG((res1 + res2 + res3)/3) as GrandAvg
FROM	YourTable

[jmhfloor]

I can do this which ever way makes it easier. I havnt gotten to far into the process that I can not start all over.

If you can give me some recomendations as to the best way to input this data (ie type of field. ATM I am using text boxes for entery) I can start anew.

[salvelinus]

I'd use DKenny's SQL, using CDbl on the res1, res2 and res3 values if the value is text.
You'll still have to open a recordset to get the value, but you won't have to do the calculation separately. My example uses DAO, which is slightly faster as long as everything's in Access. You could use ADO, which seems more common.

[DKenny]

If the fields are storing numeric values that don't contain decimals, that I would suggest that you make the fields
INT or BIGINT in SQL Server
or
Number in Access with a Fields Size of Integer or Long Integer

[salvelinus]

Would there be a problem using integers for the value if the average ends up a double?

[jmhfloor]

Thanks again guys,

I am going to start playing with this again. Will post again if I run into any troubles.


Have a great day!