Calculate the sum of several records from a table

[deniselaserna]

Hi,

Total newby when it comes to access so im hoping someone can put me in
the right direction. I am trying to calculate answers completed in a
survey. 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!!!

 

[Guest]

You already posted here and received answers before posting the exact same
thing.

 

[deniselaserna]

Hi, I just needed to change the subject title since I didn't get the
answer I needed. The previous post was a little misleading. It would be
nice if someone could help me with this though. Thanks so much!

You already posted here and received answers before posting the exact same
thing.

Hi,

Total newby when it comes to access so im hoping someone can put me in
the right direction. I am trying to calculate answers completed in a
survey. 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!!!