If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average multiple colums
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 |
#2
|
|||
|
|||
Average multiple colums
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 '================================================= === esoller wrote: 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 |
#3
|
|||
|
|||
Average multiple colums
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...) He http://groups.google.com/group/comp....a79b7d3ada2107 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. |
#4
|
|||
|
|||
Average multiple colums
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" wrote: 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 |
#5
|
|||
|
|||
Average multiple colums
Thank you,
I will be reading your link. " wrote: 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...) He http://groups.google.com/group/comp....a79b7d3ada2107 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. |
#6
|
|||
|
|||
Average multiple colums
Duane Hookom has a sample survey database at
http://rogersaccesslibrary.com/Other...p#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 esoller wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|