huge problems with database deisgn for survey

I am very inexperienced at database design and am saving huge problems.

I am trying to construct a Db in Access 2000. The data is based on
information from client files and questions, as well as dates of the
questions. The time scale from an initial event is important and will be the
subject of a query.

My original design was wrong in that it had a field for each question,
however this did make for a relatively easy build.

I am now trying to do the right thing, but I cannot understand how I get the
responses into into the database. I have

tblCase
CaseID autoPK

tblQuestionsAsked
QuestionsAskedID PK auto
Description

tblResults
ResultsID PK auto
QuestionsAskedID FK
Results

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have looked at AYS but found it too confusing, I don't have enough
experience to know what I need from this.

I'm sorry to be so basic, I hope that someone can provide me with a simple
solution.

I believe your tblResults needs a foreign key relating back to tblCase.
Otherwise, how do you track which case record corresponds with the results?

You might want to take a look at the survey results entry screen. There is a
command button that runs an append query with a source of the
tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the
main form.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

I am very inexperienced at database design and am saving huge problems.

I am trying to construct a Db in Access 2000. The data is based on
information from client files and questions, as well as dates of the
questions. The time scale from an initial event is important and will be the
subject of a query.

My original design was wrong in that it had a field for each question,
however this did make for a relatively easy build.

I am now trying to do the right thing, but I cannot understand how I get the
responses into into the database. I have

tblCase
CaseID autoPK

tblQuestionsAsked
QuestionsAskedID PK auto
Description

tblResults
ResultsID PK auto
QuestionsAskedID FK
Results

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have looked at AYS but found it too confusing, I don't have enough
experience to know what I need from this.

I'm sorry to be so basic, I hope that someone can provide me with a simple
solution.

Thanks for replying Duane,

I have added CaseID and I think I have made a command button using the
append data query. I cant try it as ai havent entered any data yet. I need a
form now.

How do I make a form to enter the results? Do i enter text boxes and then
options boxes to hold the answers?

I tried making a table of possible answers, but my yes.no.unknown answers
will need a number input for analysis.

Thank you for your time.


"Duane Hookom" wrote:

I believe your tblResults needs a foreign key relating back to tblCase.
Otherwise, how do you track which case record corresponds with the results?

You might want to take a look at the survey results entry screen. There is a
command button that runs an append query with a source of the
tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the
main form.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

I am very inexperienced at database design and am saving huge problems.

I am trying to construct a Db in Access 2000. The data is based on
information from client files and questions, as well as dates of the
questions. The time scale from an initial event is important and will be the
subject of a query.

My original design was wrong in that it had a field for each question,
however this did make for a relatively easy build.

I am now trying to do the right thing, but I cannot understand how I get the
responses into into the database. I have

tblCase
CaseID autoPK

tblQuestionsAsked
QuestionsAskedID PK auto
Description

tblResults
ResultsID PK auto
QuestionsAskedID FK
Results

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have looked at AYS but found it too confusing, I don't have enough
experience to know what I need from this.

I'm sorry to be so basic, I hope that someone can provide me with a simple
solution.

If your question are associated/related to a Case then you would have a main
form based on tblCase and a subform based on tblResults. The link
master/child would be set to the CaseID field. A command button on the main
form would run the append query.

Is your Results field numeric or text? What do you want to store? You can
always group by Results to count the number of like answers.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Thanks for replying Duane,

I have added CaseID and I think I have made a command button using the
append data query. I cant try it as ai havent entered any data yet. I need a
form now.

How do I make a form to enter the results? Do i enter text boxes and then
options boxes to hold the answers?

I tried making a table of possible answers, but my yes.no.unknown answers
will need a number input for analysis.

Thank you for your time.


"Duane Hookom" wrote:

I believe your tblResults needs a foreign key relating back to tblCase.
Otherwise, how do you track which case record corresponds with the results?

You might want to take a look at the survey results entry screen. There is a
command button that runs an append query with a source of the
tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the
main form.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

I am very inexperienced at database design and am saving huge problems.

I am trying to construct a Db in Access 2000. The data is based on
information from client files and questions, as well as dates of the
questions. The time scale from an initial event is important and will be the
subject of a query.

My original design was wrong in that it had a field for each question,
however this did make for a relatively easy build.

I am now trying to do the right thing, but I cannot understand how I get the
responses into into the database. I have

tblCase
CaseID autoPK

tblQuestionsAsked
QuestionsAskedID PK auto
Description

tblResults
ResultsID PK auto
QuestionsAskedID FK
Results

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have looked at AYS but found it too confusing, I don't have enough
experience to know what I need from this.

I'm sorry to be so basic, I hope that someone can provide me with a simple
solution.

I am at home now so will try that when I am back at work at the end of the
week.
My results will be a mixture of scores, yes/no/unknown, age ranges,
education ranges and dates.
I had problems grouping by results before but that may be because of my
table structure which was more spreadsheet than access tall and thin.

Thank you again


"Duane Hookom" wrote:

If your question are associated/related to a Case then you would have a main
form based on tblCase and a subform based on tblResults. The link
master/child would be set to the CaseID field. A command button on the main
form would run the append query.

Is your Results field numeric or text? What do you want to store? You can
always group by Results to count the number of like answers.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Thanks for replying Duane,

I have added CaseID and I think I have made a command button using the
append data query. I cant try it as ai havent entered any data yet. I need a
form now.

How do I make a form to enter the results? Do i enter text boxes and then
options boxes to hold the answers?

I tried making a table of possible answers, but my yes.no.unknown answers
will need a number input for analysis.

Thank you for your time.


"Duane Hookom" wrote:

I believe your tblResults needs a foreign key relating back to tblCase.
Otherwise, how do you track which case record corresponds with the results?

You might want to take a look at the survey results entry screen. There is a
command button that runs an append query with a source of the
tblQuestionsAsked and a target of tblResults. The CaseID is pulled from the
main form.

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

I am very inexperienced at database design and am saving huge problems.

I am trying to construct a Db in Access 2000. The data is based on
information from client files and questions, as well as dates of the
questions. The time scale from an initial event is important and will be the
subject of a query.

My original design was wrong in that it had a field for each question,
however this did make for a relatively easy build.

I am now trying to do the right thing, but I cannot understand how I get the
responses into into the database. I have

tblCase
CaseID autoPK

tblQuestionsAsked
QuestionsAskedID PK auto
Description

tblResults
ResultsID PK auto
QuestionsAskedID FK
Results

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have looked at AYS but found it too confusing, I don't have enough
experience to know what I need from this.

I'm sorry to be so basic, I hope that someone can provide me with a simple
solution.

Hi,
I have set up the forms and am trying to add the boxes for the results.
Should I be looking at list boxes or combo boxes?

My questions are listed in rows as text in tblQuestionsAsked, not as fields,
so how do I enter each question into the form and have a box for the results?

Should my results table have rows for each answer?

Would it be so terrible if I went back to having a field for each question??