Database structure

[Kaylen]

I am trying to make a template with a list of
questions. The answers are scores ranging from 0-4, with an option of N/A
(not applicable). The users of the template will be entering the [Date] that
they answer the questions and the answers to the questions (0-4, or N/A).
Here is when the calculations are involved: I want to average the answers at
a given date (Date=#) for questions 1-5 and sum the answers from questions
6-8, and then average the answers from questions 9-12, etc. But if, for
example the answers for questions 9-12 are all "N/A", then I want the average
or sum formulas to show "N/A" instead of error. At the end I want to have a
grand total score, the sum of all the averages and sums above. Because the
questions are so long to be columns headings, that's why I was thinking of
having them as numbers and relabeling them later in the form. Can you give me
any idea how I should create this template database so others can use to
answer questions and run report to see to the scores of a given date? Many
thanks..

 

[aaron.kempf]

Take an average; and inside the AVG call, set the cell to nothing or
null maybe if it's equal to N/A.

PS - I would make N/A equal to negative 2 or something; so this would
be easier to calculate

 

[Kaylen]

I would like to know how I would go about setting up the database template
for users to enter the scores for a list of questions. When the users answer
the questions, they would enter in the date and the scores for the
questions. Should I have the questions in the columns heading? I tried
different way to set up the tables in a way so that I can later calculate
the average scores of some of the questions. Does any one have a better idea
in how I should set up this database? Thank you so much....

 

[vbasean]

This is a complex question:

I would suggest for starters:

Build a table for questions:
QuestionID = the question number (i.e. 1-12)
Question = Text of the question

table for tests:
TestID = autonumber
TakersName = text
DateOfTest = date/time

table for answers:
TestID = child field related to the test
QuestionID = related to the question
Answer = number

set up your test form:
main form will be based on the tests info
subform based on the answers
use a frame for the answer with radio buttons
the values of the five buttons
1) = 1
2) = 2
3) = 3
4) = 4
N/A) = 0

this should get you started in the right direction

there will a lot of questions, like how do you get the question to match the
questions on the test.

also setting up the test like a wizard form that walks the person through.

these are questions that will pop up as you are building this db

I would like to know how I would go about setting up the database template
for users to enter the scores for a list of questions. When the users answer
the questions, they would enter in the date and the scores for the
questions. Should I have the questions in the columns heading? I tried
different way to set up the tables in a way so that I can later calculate
the average scores of some of the questions. Does any one have a better idea
in how I should set up this database? Thank you so much....

I am trying to make a template with a list of
questions. The answers are scores ranging from 0-4, with an option of N/A
(not applicable). The users of the template will be entering the [Date] that
they answer the questions and the answers to the questions (0-4, or N/A).
Here is when the calculations are involved: I want to average the answers at
a given date (Date=#) for questions 1-5 and sum the answers from questions
6-8, and then average the answers from questions 9-12, etc. But if, for
example the answers for questions 9-12 are all "N/A", then I want the average
or sum formulas to show "N/A" instead of error. At the end I want to have a
grand total score, the sum of all the averages and sums above. Because the
questions are so long to be columns headings, that's why I was thinking of
having them as numbers and relabeling them later in the form. Can you give me
any idea how I should create this template database so others can use to
answer questions and run report to see to the scores of a given date? Many
thanks..

 

[John W. Vinson]

I would like to know how I would go about setting up the database template
for users to enter the scores for a list of questions. When the users answer
the questions, they would enter in the date and the scores for the
questions. Should I have the questions in the columns heading? I tried
different way to set up the tables in a way so that I can later calculate
the average scores of some of the questions. Does any one have a better idea
in how I should set up this database? Thank you so much....

Take a look at Duane Hookum's questionnaire database - it should handle all of
this in a normalized and flexible manner:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[Kaylen]

Thank you so much for the suggestions. I am quite new to Access and I am very
excited to learn more about it. I will now try your suggestions. I am sure I
will bump into problems and I will try my best first before posting them.
Thank you again for your precious time.

This is a complex question:

I would suggest for starters:

Build a table for questions:
QuestionID = the question number (i.e. 1-12)
Question = Text of the question

table for tests:
TestID = autonumber
TakersName = text
DateOfTest = date/time

table for answers:
TestID = child field related to the test
QuestionID = related to the question
Answer = number

set up your test form:
main form will be based on the tests info
subform based on the answers
use a frame for the answer with radio buttons
the values of the five buttons
1) = 1
2) = 2
3) = 3
4) = 4
N/A) = 0

this should get you started in the right direction

there will a lot of questions, like how do you get the question to match the
questions on the test.

also setting up the test like a wizard form that walks the person through.

these are questions that will pop up as you are building this db

I would like to know how I would go about setting up the database template
for users to enter the scores for a list of questions. When the users answer
the questions, they would enter in the date and the scores for the
questions. Should I have the questions in the columns heading? I tried
different way to set up the tables in a way so that I can later calculate
the average scores of some of the questions. Does any one have a better idea
in how I should set up this database? Thank you so much....

I am trying to make a template with a list of
questions. The answers are scores ranging from 0-4, with an option of N/A
(not applicable). The users of the template will be entering the [Date] that
they answer the questions and the answers to the questions (0-4, or N/A).
Here is when the calculations are involved: I want to average the answers at
a given date (Date=#) for questions 1-5 and sum the answers from questions
6-8, and then average the answers from questions 9-12, etc. But if, for
example the answers for questions 9-12 are all "N/A", then I want the average
or sum formulas to show "N/A" instead of error. At the end I want to have a
grand total score, the sum of all the averages and sums above. Because the
questions are so long to be columns headings, that's why I was thinking of
having them as numbers and relabeling them later in the form. Can you give me
any idea how I should create this template database so others can use to
answer questions and run report to see to the scores of a given date? Many
thanks..

 

[Kaylen]

I set the tables as you suggested. Now I can't figure out what to do next. I
want the main form to be about the test, which will open a subform from the
questions table, which allow me to enter the answers for each question. How
do I link the questions to the test table to match the questions on the
tests?

 

[vbasean]

You're up against a big learning curve and you'd have to start
learning about forms and how they interact.

try going online and finding a tutorial on MS Access, or a famous book
that helps a lot of people is "Database Design for Mere Mortals."

try some of the links above on questionair databases already designed
and try to follow some of their ideas.

the questionaire database is not a simple example for someone starting
out. Something like a phone list would be a beginner level database
project. It's the learning curve you have to consider.

 

[John W. Vinson]

I set the tables as you suggested. Now I can't figure out what to do next. I
want the main form to be about the test, which will open a subform from the
questions table, which allow me to enter the answers for each question. How
do I link the questions to the test table to match the questions on the
tests?

Did you look at Duane's sample database? It does exactly that.

And you don't "open" a Subform. It's in a window on the mainform, linked to it
by master/child link fields. You would have a Combo Box on the subform to
select the question, as one option.

 

[Kaylen]

I am so lost. I think I need one on one instructions with this...

 

[aaron.kempf]

I'd just find a class on SQL Server.

There is no reason to start with Access-- if youre new to this stuff.
And there's probably no way that you're going to find a class on
Access 2007.

So I'd start with something with a future-- and take a class on SQL
Server.

SQL Server is the worlds most popular database.

-Aaron

 

[Tony Toews [MVP]]

I am so lost. I think I need one on one instructions with this...

Be advised that Aaron's response to every question is SQL Server
and/or ADPs. No matter how inappropriate.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

Be aware that T O N Y T O E W S is about 20 years out of date.. and he
is scared that you'll go and take a class and put him out of a job.

That dork thinks that they should build houses out of ice cubes!~

-Aaron

 

[Kaylen]

I really hope someone can take a look at my excel template and see what
exactly I'm trying to do with access. And also what I have so far in Access
to give me a head start in the right direction.