standard rel. rules between 2 main frms with 1-1 rel. and sub fo

[TravelingHT]

Dear All:

I have created a form and tables for taking in the info from a
questionnaire. For reasons of insanity and trying to keep to the rules of 4th
normal planning for my questionnaire, I have created a one to one
relationship between the questions always answered and some questions only
some times answered to save space.

To connect the two primary forms I created a primary key with auto number in
the Always Used Main Table and linked it to a primary key using long integer
(No Duplicates) in the Sometimes Used Main Table.

I set up an If statement to send the tab to a sub form in the Sometimes
Main Form which worked fine, but Ctrl+Tab out of the subform into another
subform did lots of weird things depending on if there was a value in the
first subform, if I added another entry then Ctrl+Tab to the point I could
not keep track of the variables that would create the changes.

I then looked at my relationships and added a one to many link from the
Sometimes Used Main Table to all the sub tables and now things are working
fine.

Originally there was only the One to One relationship between the two main
forms and a One to Many relationships between the Always Used Main Forms and
the Subforms.

This now means that both the Always Used Main Table and the Sometimes Used
Main Table both have One to Many relationships with all of the sub tables and
a One to One relationship between the two of the Main Tables!

Is this normal, is it an abomination, what are the rules, can I go to prison
for this?

Where can I find out about the conventions on this type of situation?

Thanks in advance.

TravelingHT

 

[Steve]

Consider these tables .....
TblQuestionaireAnswerer
QuestionaireAnswererID
FirstName
LastName
<Contact fields>

TblQuestion
QuestionID
Question

TblAnswersToQuestions
AnswersToQuestionsID
QuestionaireAnswererID
DateQuestionsAnswered

TblAnswer
AnswerID
AnswersToQuestionsID
QuestionID
Answer

Steve

 

[TravelingHT]

Dear Steve:

Thanks for the info.

I do realy want to know if in the situation I describe if there is a
convention and what it is if it exists?

Yours Truly,

Traveling HT

 

[Steve]

Your situation is no different than all other database applications. Two
rules apply:
1. Each table should record data of a single entity.
TblQuestionaireAnswerer - each person who answers one or more of
your questions
TblQuestion - The questions in your survey
TblAnswersToQuestions - Each responders to one or more of your
questions

2. The tables should be normalized. One-to-many relationships require at
least 2 tables. Any survey recorded in TblAnswersToQuestions could have
many answers recorded in TblAnswer.

Steve

 

[Larry Daugherty]

You might find that Duane Hookom has already done what you need. You
can download his working application and modify to your taste.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

The above link is all one line.

HTH

 

[TravelingHT]

Dear Steve:

Firstly thank you for taking the time to respond. I know I am lacking in
understanding because your post tells me I have a round peg and a round hole
but I just can not seem to understand how to put them together. So please
forgive me, I will mull this over untill I understand I know the concept is
core to access and relational databases. I speak specifically of your way of
having a Tbl of Questions and a Tbl of Answers.

I have problems becuase there are some questions that can have more than one
answer, so I need a sub form for the one to many relationship this can cause
to the main answer section.

Again there are another set of questions that only come into play if a
respondent answers yes to a previous question. Thus making records to those
questions redundant on a "No" answer.

When I look at what you are telling me I feel as though my previous two
paragraphs are completly missing the point. I can not conseptualize how a
table of questions can exist separately from a table of answers. In my mind
the Questions are the lables and the answers are the txt boxes. Again I will
think on this. Thanks for the insite.

Yours truly,

TravelingHT

 

[TravelingHT]

Dear Larry:

Thanks for your help, the link did not exactly work but I got the web site
and will go throught it to find the example of which you talk.

Yours truly,

TravelingHT

 

[Steve]

See Below ....

Steve

Dear Steve:

Firstly thank you for taking the time to respond. I know I am lacking in
understanding because your post tells me I have a round peg and a round
hole
but I just can not seem to understand how to put them together. So please
forgive me, I will mull this over untill I understand I know the concept
is
core to access and relational databases. I speak specifically of your way
of
having a Tbl of Questions and a Tbl of Answers.

In a relational database, a table is created for every entity. Then each
item (record) in each table is assigned a primary key (usually an
autonumber) that uniquely identifies that record anywhere it is referred to
in the database. For example, there is a table, TblQuestion, to record all
the questions in your questionaire. Each question in TblQuestion is assigned
a unique primary key QuestionID. Say Question12 on your questionaire is
assigned QuestionID = 17, each answer to Question12 you record in TblAnswer
you will associate the answer with QuestionID = 17.

I have problems becuase there are some questions that can have more than
one
answer, so I need a sub form for the one to many relationship this can
cause
to the main answer section.

Multiple answers to the same question is not a problem. Look at
TblAnswersToQuestions and TblAnswer. Each record in TblAnswersToQuestions
refers to a group of answers by someone on a specified date.
QuestionaireAnswererID identifies the someone and DateQuestionsAnswered
identifies the date someone answered your questionaire. Notice in TblAnswer
the field, AnswerToQuestionsID. That field is the foreign key that ties
together TblAnswersToQuestions and TblAnswer in a one-to-many relationship.
So you record who answered a group of questions on a specified date in
TblAnswersToQuestions and you record that someone's answers to each
questions he answered in TblAnswer. You can use a main form based on
TblAnswersToQuestions and a subform based on TblAnswer to record this
information.

Again there are another set of questions that only come into play if a
respondent answers yes to a previous question. Thus making records to
those
questions redundant on a "No" answer.

Refer back to the above. In your subform, you only record answers that the
someone provided. You record the QuestionID and his answer. If he provided
more than one answer to a question, you merely record the same QuestionID
and his other answer. In your subform, you don't record every question, you
only record the questions where there is/are answer.

When I look at what you are telling me I feel as though my previous two
paragraphs are completly missing the point. I can not conseptualize how a
table of questions can exist separately from a table of answers. In my
mind
the Questions are the lables and the answers are the txt boxes. Again I
will
think on this. Thanks for the insite.

The table of questions provides QuestionID to uniquely identify each
question. The table of answers records each person's answer to questions he
answered.