Adding across a row

[OxDavis]

Hey guys I'm developing a DB to input satisfaction surveys and compute satisfaction levels expressed as a percentage. I am setting up the DB with 1 record=1 satisfaction survey. The record would contain the fields:Survey Quarter and questions 1,2,3, etc. The responses to the questions are in pulldown menu form with Yes, No and N/A. I've already set Yes to be read as 1 and No as 0. Now I want to add across the row (Questions 1-11) so I can complete the math and get a percentage for each survey. So far I have had zero luck accomplishing this. Any suggestions?

 

[WayneRyan]

Ox,

Simple query should do it:

Select Employee, Sum(Questions)
From   YourTable
Group By Employee

Just joking. With your table structure you have to make a new
column in your Query:

NewClm: [Answer1] + [Answer2] + [Answer3] ...

You really need to redesign your tables so that they don't resemble a
spreadsheet. Access (and other databases) don't add horizontally. I'll
see if I can't find you a sample or two.

Wayne

 

[mresann]

You have a database that is not normalized. Although the query question you ask is actually simple to answer, your table has repeating fields (question numbers) that can actually be made eaier with a table normalization process.

In fact, let me give you the answer for your current table situation, then give me some time and I'll dig up an old quiz database I have that can more easily show you what I mean.

Also, you didnt' clarify the calculation you wanted performed on an "N/A". In fact, the "N/A" will actually create a situation where the query will be greately complicated with a lot of Iif statements, but it can be gotten around. For now, I'll assume you want that to be the same as "No" (0). Further assuming your question fields are named "Survey", "Quarter", "Q1", "Q2", ... "Q10", and your table name is called "tblSurvey", here is my solution:

SELECT 
tblSurvey.Survey, 
tblSurvey.Quarter, 

(IIf([Q1]="1",1,0))+
(IIf([Q2]="1",1,0))+
(IIf([Q3]="1",1,0))+
(IIf([Q4]="1",1,0))+
(IIf([Q5]="1",1,0))+
(IIf([Q6]="1",1,0))+
(IIf([Q7]="1",1,0))+
(IIf([Q8]="1",1,0))+
(IIf([Q9]="1",1,0))+
(IIf([Q10]="1",1,0)) 
AS YesSum, 

FormatPercent([YesSum]/10,0) AS Percentage

FROM tblSurvey;

 

[OxDavis]

Thanks for the imput, examples would be great. I was hoping to be able to search the DB by question number and a number of other factor including race, gender, etc. Perhaps I do not need a percentage output from each record, rather an aggregate total for them all that is searchable by the criteria I mentioned previously. However I do need percentage totals by question number. Suggestions?

 

[OxDavis]

BTW, for the N/As I was thinking to make each "true" response to each N/A a -1 and then adding this total to 11(the total number of Qs) and then dividing the "Yes" responses by this number. Plausible? Excuse my ignorance, I've made several DBs to track and search text info, but this is my first attempt at doing calculations in Access