access variables from 2 tables on one form

[Guest]

I have a very simple flat database. I'm developing a form and want to access
variables from 2 tables (one to one relationship), but I'm having trouble
figuring out how to do this. Can you help?

 

[John W. Vinson]

I have a very simple flat database. I'm developing a form and want to access
variables from 2 tables (one to one relationship), but I'm having trouble
figuring out how to do this. Can you help?

A one to one relationship still has directionality - there's got to be a
"parent" and a "child" table (defined by the direction of the arrow on the
relationships window).

Create a Form based on the parent table, and a Subform based on the child
table.

Note that one-to-one relationships are QUITE uncommon; if you're building this
because you have more than 255 fields in your table... stop. You'll be *much*
better off normalizing the table. "Fields are expensive, records are cheap";
I'm all but certain that you have one or more one-to-many relationships
embedded within each record of this monster.

John W. Vinson [MVP]

 

[Guest]

A one to one relationship still has directionality - there's got to be a
"parent" and a "child" table (defined by the direction of the arrow on the
relationships window).

Create a Form based on the parent table, and a Subform based on the child
table.

Note that one-to-one relationships are QUITE uncommon; if you're building this
because you have more than 255 fields in your table... stop. You'll be *much*
better off normalizing the table. "Fields are expensive, records are cheap";
I'm all but certain that you have one or more one-to-many relationships
embedded within each record of this monster.

John W. Vinson [MVP]

John, than you for bothering with such a [stupid] questions. Believe it or
not, I am building this because I have more than 255 fields in my table.
Unforfunately this cannot be avoided because I am implementing a questionnare
for a research study and the form has a ton of variables, the primary key
being the participant ID in both tables. What does it mean to normalize the
table? I did read about using a subform but I couldn't figure out how to get
the subform on tab control pages 3 & 4. I don't want the subform to pop up
in the current page, but simply continue on to the next pages with the 2nd
table. Also, I can't really see any one to many relationships in the
questionnare.

 

[Guest]

A one to one relationship still has directionality - there's got to be a
"parent" and a "child" table (defined by the direction of the arrow on the
relationships window).

Create a Form based on the parent table, and a Subform based on the child
table.

Note that one-to-one relationships are QUITE uncommon; if you're building this
because you have more than 255 fields in your table... stop. You'll be *much*
better off normalizing the table. "Fields are expensive, records are cheap";
I'm all but certain that you have one or more one-to-many relationships
embedded within each record of this monster.

John W. Vinson [MVP]

John, than you for bothering with such a [stupid] questions. Believe it or
not, I am building this because I have more than 255 fields in my table.
Unforfunately this cannot be avoided because I am implementing a questionnare
for a research study and the form has a ton of variables, the primary key
being the participant ID in both tables. What does it mean to normalize the
table? I did read about using a subform but I couldn't figure out how to get
the subform on tab control pages 3 & 4. I don't want the subform to pop up
in the current page, but simply continue on to the next pages with the 2nd
table. Also, I can't really see any one to many relationships in the
questionnare.

 

[John W. Vinson]

John, than you for bothering with such a [stupid] questions. Believe it or
not, I am building this because I have more than 255 fields in my table.
Unforfunately this cannot be avoided because I am implementing a questionnare
for a research study and the form has a ton of variables, the primary key
being the participant ID in both tables. What does it mean to normalize the
table?

Just as I suspected - the dreaded "Questionnaire Trap". This is a very common
mistake for people just getting into Access!

A questionnaire application needs (at least) *FOUR* tables, not one:

Questions
QuestionNo <Primary Key>
Question <text>
<any other fields having to do with the question>

Participants
ParticipantID <primary key>
LastName
FirstName
<other biographical data>

Questionnaires
QuestionnaireID <primary key>
ParticipantID <link to Participants, who took it>
QuestDate <Date/Time, when they took it>
<other info about the questionnaire as a whole>

Answers
QuestionnaireID <which questionnaire is being filled out>
QuestionNo <which question is being answered>
Answer

Rather than one *FIELD* per answer, you'll add one *RECORD* per answer.

For a fully worked out, and excellent, example of how to do this, download
Duane Hookum's At Your Survey database:

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


John W. Vinson [MVP]

 

[Guest]

Hi John,

Let me see if I can give you a little more information about what I am
trying to accomplish. We have a research study where a clinician asks
certain questions form the participant and fills out the paper questionnaire
apppropriately. There are 2 qxres: Initial Visit and Follow-up visit. This
initial visit has a ton of questions, more than 255, so I have created 2
tables to go with this form. I'm having trouble identifying how this would
be set up as a one to many relationship (I'm new to doing anything more
advanced than having one table to one form. Additionally, the follow up form
has almost the identical variables as the initial visit form. The follow-up
form is completed once a week for a year and beyond.

The initial qxre has these kind of demographic variables;
Participant ID
Participant DOB
Participant gender
Initial visit date
An a bunch of questions about illness symptoms, medications, blood
collection, and PCR swab collection. These questions are asked of the 1.
primary child (which is the participant ID), 2. the mother, and 3. up to 9
of her other children living in the household.

For example, there is a question, "In the past week, has there been fever?"
This is answered for the primary child, mother, and up to 9 secondary
children in the household. So essentially, the same question is asked of up
to 10 people. This is the same for child name, gender, age for all secondary
children, e.g same questions are asked for each of these children. Also,
there are question for each child and mother as to whether blood and oral
swabs were collected.

This is a non-changing database. Once the paper forms are filled out, they
are data entered in a database (the one I'm creating) and the data is
downloaded to analyze the data in a statistical program.

I am at a lost as to how to begin. In the past I have just created one
table for each questionnaire but since there are too many variables, this is
not possible.

Sorry so long winded. I have the Microsoft Inside out book but find it of
little help.

Thank you for anyone who can help.
Linda Drolette

 

[John W. Vinson]

Hi John,

Let me see if I can give you a little more information about what I am
trying to accomplish. We have a research study where a clinician asks
certain questions form the participant and fills out the paper questionnaire
apppropriately. There are 2 qxres: Initial Visit and Follow-up visit. This
initial visit has a ton of questions, more than 255, so I have created 2
tables to go with this form. I'm having trouble identifying how this would
be set up as a one to many relationship (I'm new to doing anything more
advanced than having one table to one form. Additionally, the follow up form
has almost the identical variables as the initial visit form. The follow-up
form is completed once a week for a year and beyond.

The initial qxre has these kind of demographic variables;
Participant ID
Participant DOB
Participant gender
Initial visit date

These should be RECORDS in a table, not FIELDS.

An a bunch of questions about illness symptoms, medications, blood
collection, and PCR swab collection. These questions are asked of the 1.
primary child (which is the participant ID), 2. the mother, and 3. up to 9
of her other children living in the household.

Even more so. If you have a set of fields for each child, you're "committing
spreadsheet". I'd suggest a structure like:

People <all participants and family members are people>
PersonID
LastName
FirstName
<other biographical information>

FamilyRelations
ParticipantID <link to People>
FamilyMemberID <another link to People>
Relationship <e.g. "Mother", "Sister", "Stepbrother">

Questions
QuestionNo
Question <text>, e.g. "In the past week, has there been fever?"

Answers
PersonID <who answered the question>
QuestionNo <link to Questions>
Answer <yes/no>

This structure will let you enter ANY DESIRED number of questions; add or
delete questions as your needs change (and they will, believe me!); assemble
your data in any way desired, say using a Crosstab query to reconstruct the
wide-flat view; and be much easier to maintain than one huge wide table.

John W. Vinson [MVP]

 

[Guest]

John,

I can't thank you enough. I'm not sure I can implement all of this before
my due date (11/1) while also trying to complete other projects at the same
time. But your response is just what I needed to get going. I may re-do my
database in this relational way when I have time later, and any new ones
created will start with this concept.

One other question though related to your response. Your first response had
a table for 'questionnaires'. Does this mean there would be only one form to
handle both questionnaires (initial and follow-up*52_) The follow-up form
has 80% of the same questions as the initial visit form plus a few more
questions. Would there be something similar to a menu (switchboard) where
you could choose to data enter the 'initial visit form' or the follow-up
form, where the 1st question would be to choose which form is being
data-entered? Would each questionnaire have it's own form?

Can you recommend the best reference books? I have the microsoft Inside Out
for 2007, but frankly, it doesn't go into enough detail and it's really hard
to find what your looking for from the index. I don't do any SQL programming
-- everything is based macros. The book tells a lot about what you can do
but doesn't tell you how.

Best Regards,

Linda Drolette