Help needed. Overall rating for 7 questions based on company ID

[Atenti]

Hi All,

I have been following the forums here for quite some time , but i finally managed to register today and i need fellow forum members advice on an issue i am having with one access query.

I am having the following database setup:

Client_id Respondent_Name Company_id Question 1 Question 2 Question 3
123 John Smith 111 Excellent Fair Good
123 Preedep Kumar 111 Fair Good Excellent
126 Dimitar Rushkov 111 Fair Fair Poor

The ratings for each question that i have in the survey are:
Poor;Fair;Average;Good;Excellent;N/A

Now what i have to do is enter in a new column at the end of the database the overall score for each question on a Company_id level where we use the following rule: N/A are not consider or if all ratings are N/A we should have N/A as overall score. The overall score is calculated as:

• 
  Number of answers "Excellent"/"All ratings" grater than 0.51 (51%) then the rating overall rating for the company is "Excellent"
  Number of answers "Excellent"+"Good"/"All ratings" grater than 0.51 (51%) then the rating overall rating for the company is "Good"
  Number of answers "Excellent"+"Good"+"Average"/"All ratings" grater than 0.51 (51%) then the rating overall rating for the company is "Average"
  Number of answers "Excellent"+"Good"+"Fair"/"All ratings" grater than 0.51 (51%) then the rating overall rating for the company is "Fair/Poor"

Any help will be appreciated here. Let me know in the topic if my instructions are unclear or you will need more details.

 

[pr2-eugin]

Hello Atenti, Welcome to AWF !

I am so sorry to be the bearer of horrible news, but your table is constructed quiet badly. Fields Question 1, Question 2, Question 3 indicates that the Table is not normalized properly. In Tables, you normally have information stored as Rows against columns. What your table should look like is.

tblSurveyRespondents
clientID - PK
clientName
companyID

tblSurveyResults
surveyID - PK
clientID - FK
questionID - (possible FK)
questionReply

Then your data will be something like..
tblSurveyRespondents

clientID    clientName        companyID
123         John Smith         111 
124         Preedep Kumar      111 
126         Dimitar Rushkov    111

tblSurveyResults

surveyID    clientID    questionID        questionReply
1            123         1                  Excellent
2            123         2                  Fair
3            123         3                  Good
4            124         1                  Fair
5            124         2                  Good
6            124         3                  Excellent
7            126         1                  Fair
8            126         2                  Fair
9            126         3                  Poor

Then your result can be obtained by simple GROUP BY Queries. Re structure your DB, then we can go into getting the result. Good Luck !