Questionnaires PK & Tables

[dee]

Hi,

I am planning a new database. It will be used to input and compile the
results of hard-copy questionnaires.

Each person who fills in the questionnaires will be assigned an identity
number code that is unique to them. Each participant will fill in multiple
questionnaires.

I am wondering, at this point, about 2 things:

1. Should I use an autonumber PK, or am I just a fine using the participant
number at the PK, as it will be a unique identifier. Same thing applies to
the question tables - again - should I use the question number, which is a
combination of letters and numbers that identify both the questionnaire and
the question number, as my PK? I was very kindly directed to a sample
database by this news group, and I see they have an autonumber PK, but it
seems as though they are dealing with only one questionnaire at a time and
the setup is a little different than mine.

2. I am thinking of creating a separate table for each questionnaire. The
questionnaires are further broken down into various "categories". I don't
think it's necessary to create even more tables because of this. Does this
sound correct?

Any assistance will be greatly appreciated.

 

[mray29]

As long as you make the particpant number a unique primary key, you shouldn't
need to create an autonumber field for that table.

Regarding the questionnaire tables, if you're going to use a separate table
for each questionnaire, you don't really need to include the questionnaire
name in the question number field. You will know which table you're selecting
from and can just use question number (which can also be the soleprimary key
in each questionnaire tables).

You could create a field in each questionnnaire table called "Category" and
fill it in for each question. Relationally, it would be a better solution to
create a Category table, with CategoryID and CategoryDescription in it, and
use the CategoryID as a foreign key in each questionnaire table.

 

[dee]

Thanks for your response.

The questionnaire numbers are already numbered - they are hard copy and are
being filled in during the next few months, after which they will be shipped
back to me for input into Access.

I like the idea of the category table. In fact, it may be a bit redundant
because the combination of letters and numbers that represent each question
refer to the actual questionnaire name and the question number. It might be
nice to have the category names, though. Does this sound right?

 

[John Bickley]

Hi Dee,

New to Access & trying to find out how best to go about constructing a
database. We're doing a promo at weekend and will be asking about 500 people
to answer a questinnaire, (i) some basic info about themselves (age, gender
etc) & (ii) to choose from a list of interests (cycling, cinema going,
eating out etc)

What's the best way to go about this?

Many thanks
John

 

[dee]

Hi John,

If you type the keyword Dee in the Search For: box, you will see some of
the questions I have posted and the answers.

Two things of note - I did get Database Design for Mere Mortals - it's
excellent and will get you into some good habits, including committing
everything to paper before even touching Access.

Additionally, there is a survey database you can download and take a look at
- it will give you some solid ideas - Duane Hookom designed it and is kind
enough to share.

Let me know how it goes.

Deborah

 

[dee]

Hi again,

Just realized that we're in General questions... take a look in the Database
Design postings as well.