# of Fields in a table?

[Kristin]

Hi,
I'm desigining a huge survey database in Access, and have
it one table. There are about 300 different fields (i.e
some questions have "check all that apply" so i have a
yes/no field for each possible answer). I'm worried I'm
going to run out of room...once already I've had to
restart Access to get it going again. I don't know how
else to do the design so that the survey id is consistent
(on the form when the results are to be inputed).
Any help greatly appreciated...
Thanks,
Kristin

 

[Graham Mandeno]

Hi Kristin

The limit on the number of fields in an Access table is 255. However, this
is ridiculously *high*. I think the most fields I ever used in a table was
about 60, and that was exceptional. Usually they don't go over 10.

The structure which is appropriate for a survey is to have a table with one
*record* (not field) for every answer. You need tables something like the
following:

1) Questions (one record for each question)
QuestionID (primary key)
Question number (whatever yoiu want the user to see)
QuestionText
AnswerType (yes/no, multi-choice, numeric, free text, etc)

2) Respondents (one record for each person)
RespondentID (primary key)
... other stuff such as first name, last name, address, demographic
details

3) Responses (one record for each survey completed)
ResponseID (primary key)
RespondentID (foreign key related to the Respondents table)
ResponseDate

4) Answers (one for each answer completed)
ResponseID (foreign key related to the Responses table)
QuestionID (foreign key related to the Questions table)
Answer (the answer actually given)

When you have the structure right, you can worry about the user interface to
enter and extract the data.

 

[Harlan]

Kristin,
go to the following address:

http://www.rogersaccesslibrary.com/OtherLibraries.asp

look for Duane Hookom
and download AtYourSurvey.....

This might give you some insight into setting up your survey....

HTH
Harlan