Table Relationships of Questionnaire Database

[Wissy]

Please help: New to Access database designing



i have 3 tables that translate to 10 forms which is 1 questionnaire for a study. the 1st table has a primary key which is an auto number (ID) and the other 2 have primary keys that are connected to the auto number (1 to 1 relationship). on designing the forms i put in the 3 primary key fields as well.
1. is it possible to have it such that when i enter in the 1st auto number field it translates to the other tables automatically???
2. i keep on getting the "index and primary key field can not contain null value" error when i try to enter data as i move from table to table while using forms??

is this programming issues or i am doing something wrong??

Please help

[jzwp11]

Welcome to the forum!

The key to any successful relational database is the table structure (the tables & the relationships between them), so let's forget about all your forms for now and just focus on the table structure.

The relationships between tables is dependent upon the data inherent to your application, so we need to understand your application so that we can analyze it to discern the relationships between the data. You mention a questionnaire for a study. Here are some questions that I would ask to start the analysis process:

Can you tell us about the study?
Can you provide more details about the questionnaire?
Are there sections to the questionnaire?
Does each section have multiple questions?
Who has to fill out the questionnaire? I assume that multiple people will complete the questionnaire.
Can the same person fill out a questionnaire more than once over certain time periods?
Is the person filling out the questionnaire part of an organization/business/group?
Is it important to track what group & people take the questionnaire?
What types of responses are you going to have for the questions: multiple choice, yes/no, short answer, number ranking etc.?
Can 1 question have multiple responses or just 1?
Once you have the results of the questionnaire then what?

[June7]

Why would 3 tables require 10 forms?

Even if these tables have 1-to-1 relationship, one must take role of 'parent' and have primary key (autonumber field?), the other tables will have this value as foreign key (NOT an autonumber field). A record can exist in 'parent' table without record in 'child', however, child record cannot exist without a parent.

1. Use form/subform arrangement

2. see number 1

[Wissy]

SAFFA DATABASE V4_working.zip

attached is the access database i have created

the idea behind 10 forms is that the end user will look at the questionnaire and just enter the data in the database.

i think by looking at it you may be able to help me and advise

thank you

[jzwp11]

First and foremost, your database is not normalize. Check out this link on normalization. Like data should be in 1 table. Each question should be a record in that table not a separate field for each question. This is what that table should look like:

tblQuestions
-pkQuesID primary key, autonumber
-txtQuestion (text field that holds the actual question)

If you have different groups of questions based on types of participants, you need a table to hold the types of participants

tblParticipantTypes
-pkPartTypeID primary key, autonumber
-txtParticipantType

Now to related certain question to certain types of participants you need a third table (this table is commonly called a junction table)

tblParticipantTypeQuestions
-pkPartQuesID primary key ,autonumber
-fkPartTypeID foreign key to tblParticipantTypes
-fkQuesID foreign key to tblQuestions