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)
AnswerChoiceAct ual (number)
AnswerChoiceImp ortance (number)


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


I was asked to run a query to get a total sum of the AnswerChoiceAct ual
and the total sum of AnswerChoiceAct ual; 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(tblPersonAn swer.AnswerChoi ceActual) AS ActualChoice,
Sum(tblPersonAn swer.AnswerChoi ceImportance) AS T_ImportanceCho ice,
tblPersonAnswer .QuestionID, tblPerson.Depar tment
FROM tblPerson INNER JOIN tblPersonAnswer ON tblPerson.Perso nID =
tblPersonAnswer .PersonID
GROUP BY tblPersonAnswer .QuestionID, tblPerson.Depar tment, (QuestionID)
HAVING (((tblPerson.De partment)="Exec "));


qry2
SELECT qry1.QuestionID ,
[qry1]![ActualChoice]*[qry1]![T_ImportanceCho ice] 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(tblPersonAn swer.AnswerChoi ceActual) AS ActualChoice,
Sum(tblPersonAn swer.AnswerChoi ceImportance) AS T_ImportanceCho ice,
Sum(tblPersonAn swer.AnswerChoi ceActual)*Sum(t blPersonAnswer. AnswerChoiceImp ortance) As TotalScore,
tblPersonAnswer .QuestionID
FROM (tblPerson INNER JOIN tblPersonAnswer ON tblPerson.Perso nID =tblPersonAnswe r.PersonID)
WHERE (((tblPerson.De partment)="Exec "));
GROUP BY tblPersonAnswer .QuestionID;


qry2
SELECT Sum(IIf(Questio nID=1 Or QuestionID=3 Or QuestionID=5,To talScore,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.