Question Assign a value to reponse and calculate average

[manny_cb]

Hey all,

So I set up a database to record entries of a questionaire.

I use a form to input the data and its all being stored in table with each row being a single record.

the answers are a range from Poor - Excellent which are being looked up using a combo box a table called scale table.

Now i wana assign each response a numeric value.. e.g poor being 0 .. fair being 1.. till 5
and then i wana be able to generate a report where i can get a average of all the questions from all the records

so it would go
Question# Avg
Q1 3
Q2 2
etc....

i guess i would have to setup a query but my main problem is how do i assign each response a numeric value and store it in a table ?

if someone can point me in the right direction.. i hope i made sense... if not lemme know and i will try to explain better.. your help is greatly appreciated.. thanks!

 

[georgedwilkinson]

In your "table" that you reference, add a field for the "numeric value". Then you can pull it all straight out of the same table in your queries/forms/reports.

 

[manny_cb]

Thats what I was thinking.. so this is how i set it up

Table : Scale

Response Value
Veyr Poor 1
Poor 2
Neutral 3
Good 4
Excellent 5

Now how do i make the table to store the number value when the response is selected in the form ?? thanks!

 

[georgedwilkinson]

If that's all that's in your table, just make the "Value" the primary key (as long as it will always be unique and will never change), create a field in the table you want the value stored in (ValueID?), go to the relationships screen and drag Value from Scale to your other table's new field, go to/add the field in your form making sure it is bound to the new column, change it to a combo box, use "select Value, Response from Scale;" as your data source, set number of columns to 2, change the column widths to 1";1" (or experiment), and enjoy the fruits of your labor.

 

[manny_cb]

hey thanks for the help .. that worked .. can we reverse this when printing a report ?? so when the report is being pulled,, it would look up the number value and display the associated text with it ??

 

[georgedwilkinson]

Definitely. Just join the tables in the report's query (select it in the visual report builder) and don't include the Value field but include the Response field.

 

[manny_cb]

thanks for all the help.. its all working now

 

[georgedwilkinson]

Ehhhhggggsssuuhhlent!