Average multiple colums

[esoller]

Hello, This DB is my first project and my question might be basic but never
the less it drives me bananas. I need some help to put me strait again!

I have a survey db with one table that countain all the answers to my 80
questions.
Dept/Pay statu/Service Lenght/Year/Q1/Q2/Q3/Q4.....Q80

I have a query that does an average by department for each question. with
the following query.

Avg Of Q1: Avg(left([Q1],1)) as an expression and it does work properly.(I
only need the first car of the answer)

My problem is to have the same average by department but with all my 80
questions at once.

I tried many different options but none are working.

e.g. (with only 5 questions.
Avg Of AllQ:
Avg(Nz(Left([Q1],1))+Nz(Left([Q2],1))+Nz(Left([Q3],1))+Nz(Left([Q4],1))+Nz(Left([Q5],1)))/5

Does someone has an idea that could unlock my brain? Thank you

Esoll

 

[John Spencer]

Since you are retrieving a string, your statement is combining the
strings into a string. "1"+"1"+"2" is not 4, it is "112". Access will
try to change the string into a number (implicit conversion) and then
compute the average of that.

Try wrapping each NZ in CLng to force a conversion

Avg(Clng(Nz(Left([Q1],1))) +Clng(Nz(Left([Q2],1)))
+CLng(Nz(Left([Q3],1))) +Clng(Nz(Left([Q4],1))) +Clng(Nz(Left([Q5],1))))/5

That statement is probably going to get TOO long to work. You might
need to write a custom VBA function to get the average value of all
eighty questions.

It is too bad that your table design is wrong for a relational database.
You seem to be storing more than one piece of information in a field
(or you would not need to grab the first character from all those fields).

Also you should probably have a table of questionResponses with three
(or more) fields.
QuestionID: Identifies which question
Response: The answer to the question (and it seems that you may need
more than one field to hold the response)
SurveyID: a number identifying the respondent.

With that structure, your analysis would be fairly trivial.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[pietlinden]

As John said, most of your problem stems from the fact that your table
design is not right.... Keri Hardwick spelled this one out about 10
years ago... (let me see if I can find it...)

Here:
http://groups.google.com/group/comp...=st&q="Keri+Hardwick"+survey#bca79b7d3ada2107

There's also Duane Hookum's "At Your Survey 2000" which it on Roger
Carlson's website... All of them say basically the same thing --- if
the design is correct, the summaries are easy, and if not they're
somewhere between difficult and impossible.

 

[esoller]

Thank you John,
Yes I am using a flat structure for my tables. I am new to access so did not
know better. My answer table has one field for each question that includes
the answer from participant, the question's number and the question's
category. Plus some other tables that list dept. etc..
I will try my best to get out what I can with what I have and think of
redoing my database the proper way.
Wondering if I can tap on the experience of this group for help.

Thank you again for your insight.

Esoll

 

[esoller]

Thank you,
I will be reading your link.

 

[John Spencer]

Duane Hookom has a sample survey database at

http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

You can study this to get some ideas on the best setup.

This fully functional application uses a small collection of tables, queries,
forms, reports, and code to manage multiple surveys. Users can create a
survey, define questions, enter pre-defined answers, limit to list, report
results, create crosstabs, and other features without changing the design of
any objects.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you John,
Yes I am using a flat structure for my tables. I am new to access so did not
know better. My answer table has one field for each question that includes
the answer from participant, the question's number and the question's
category. Plus some other tables that list dept. etc..
I will try my best to get out what I can with what I have and think of
redoing my database the proper way.
Wondering if I can tap on the experience of this group for help.

Thank you again for your insight.

Esoll

Hello, This DB is my first project and my question might be basic but never
the less it drives me bananas. I need some help to put me strait again!

I have a survey db with one table that countain all the answers to my 80
questions.
Dept/Pay statu/Service Lenght/Year/Q1/Q2/Q3/Q4.....Q80

I have a query that does an average by department for each question. with
the following query.

Avg Of Q1: Avg(left([Q1],1)) as an expression and it does work properly.(I
only need the first car of the answer)

My problem is to have the same average by department but with all my 80
questions at once.

I tried many different options but none are working.

e.g. (with only 5 questions.
Avg Of AllQ:
Avg(Nz(Left([Q1],1))+Nz(Left([Q2],1))+Nz(Left([Q3],1))+Nz(Left([Q4],1))+Nz(Left([Q5],1)))/5

Does someone has an idea that could unlock my brain? Thank you

Esoll