Survey Queries in Access

D

deniselaserna

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!!!
 
R

Roger Carlson

Denise,

Take a look a Duane Hookom's "At Your Survey" database application. You can
download it free here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

It is likely that you can use this without modification for your own
surveys. At the very least, it will give you an idea about table structure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
D

deniselaserna

Hi Roger,

Thank you for the suggestion. I've actually already downloaded the
Duane Hookom's database and it gave me a few ideas on how to structure
a survey. My problem isn't about table structure or how to structure a
database so that's not going to help me out much. Please read the
questions on my message. I hope anyone out there can answer my
question. Thank you in advance!!!!



Roger Carlson wrote:
Roger said:
Denise,

Take a look a Duane Hookom's "At Your Survey" database application. You can
download it free here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

It is likely that you can use this without modification for your own
surveys. At the very least, it will give you an idea about table structure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


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!!!
Click to expand...
Click to expand...
 
K

KARL DEWEY

then add the total of QuestionID1, QuestionID3 & QuestionID5. I was able to
figure out the following sql to run my first 2 queries:

Use a totals query with criteria for QuestionID field as 1 Or 3 Or 5

[email protected] said:
Hi Roger,

Thank you for the suggestion. I've actually already downloaded the
Duane Hookom's database and it gave me a few ideas on how to structure
a survey. My problem isn't about table structure or how to structure a
database so that's not going to help me out much. Please read the
questions on my message. I hope anyone out there can answer my
question. Thank you in advance!!!!



Roger Carlson wrote:
Roger said:
Denise,

Take a look a Duane Hookom's "At Your Survey" database application. You can
download it free here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

It is likely that you can use this without modification for your own
surveys. At the very least, it will give you an idea about table structure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


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!!!
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I can't get past "total sum of the AnswerChoiceActual and the total sum of
AnswerChoiceActual" since these seem to be the same to me. Perhaps it might
help if you posted some sample records and desired output.
 
D

deniselaserna

Thank you Carl! That acutally solved my problem. I kept using "And" and
other methods, using "or" did what I wanted it to do. Thanks so much!

Duane, I was trying to get the sum of AnswerChoiceActual and
AnswerChoice Importance from a whole department, then multiply the 2
answers I get. Thanks so much for looking at my question.


Duane said:
I can't get past "total sum of the AnswerChoiceActual and the total sum of
AnswerChoiceActual" since these seem to be the same to me. Perhaps it might
help if you posted some sample records and desired output.


--
Duane Hookom
MS Access MVP


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!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate the sum of several records from a table 2
Create Survey form in Access based on two linked forms 2

Top