Get the sum of several records from a table

[denisel]

Hi,

It's my first time to build a database and just learning Access as I
go . I am trying to design a simple database on survey tracking. Here
is my table structure:


tblPerson
PersonID (PK)
FirstName (text)
LastName (text)
Company(text)
Department(text)
Date (date/time)


tblPersonAnswer
PersonAnswerID (PK)
PersonID (text)
QuestionID (number)
AnswerChoiceActual (number)
AnswerChoiceImportance (number)


tblQuestions
QuestionID (PK)
QuestionNumber (text)
QuestionText (text)


I was asked to run a query to get a total sum of the AnswerChoiceActual
and the total sum of AnswerChoiceActual; then multiply those 2 fields
to come up a single answer per record; then add the total of
QuestionID1, QuestionID3 & QuestionID5. I was able to figure out the
following sql to run my first 2 queries:


qry1
SELECT Sum(tblPersonAnswer.AnswerChoiceActual) AS ActualChoice,
Sum(tblPersonAnswer.AnswerChoiceImportance) AS T_ImportanceChoice,
tblPersonAnswer.QuestionID, tblPerson.Department
FROM tblPerson INNER JOIN tblPersonAnswer ON tblPerson.PersonID =
tblPersonAnswer.PersonID
GROUP BY tblPersonAnswer.QuestionID, tblPerson.Department, (QuestionID)
HAVING (((tblPerson.Department)="Exec"));


qry2
SELECT qry1.QuestionID,
[qry1]![ActualChoice]*[qry1]![T_ImportanceChoice] AS TotalScore
FROM qry1;


I've been struggling for a week now on how I can pull only a few
records in a table and get the total sum.


I'm sorry if I gave too much info but I just wanted to be as clear as
possible since I'm also still learning the terminology of this program.
Thank you so much in advance!!!

[MMcCarthy]

Revise your queries as follows:


qry1
SELECT Sum(tblPersonAnswer.AnswerChoiceActual) AS ActualChoice,
Sum(tblPersonAnswer.AnswerChoiceImportance) AS T_ImportanceChoice,
Sum(tblPersonAnswer.AnswerChoiceActual)*Sum(tblPer sonAnswer.AnswerChoiceImportance) As TotalScore,
tblPersonAnswer.QuestionID
FROM (tblPerson INNER JOIN tblPersonAnswer ON tblPerson.PersonID =tblPersonAnswer.PersonID)
WHERE (((tblPerson.Department)="Exec"));
GROUP BY tblPersonAnswer.QuestionID;


qry2
SELECT Sum(IIf(QuestionID=1 Or QuestionID=3 Or QuestionID=5,TotalScore,0)) As SumQ1Q3Q5
FROM qry1;

[denisel]

Thank you so much! Your query is much more simpler than mine.

[MMcCarthy]

Thank you so much! Your query is much more simpler than mine.

You're welcome!

Please repost if you have any further problems.