Huge survey data entry - not sure how to do it

[Celmst]

Hi


sorry is this is newbie question...
I am trying to setup an access database (latest version) that will be used for data entry.
The material to be entered is basically interviews/survey with about 400 questions. Each respondent has answered 400 questions and there will be a few hundred of these interviews done in total.

I have created 6 quite large tables containing these 400 questions. The tables have with 1-1 relationships between them.

I have also tried different ways to build a form that will allow for all these 400 data types to be entered for each respondent. But there seems to be limitations that make this impossible (maximum 256 fields in one form). I have also tried sub forms but I guess those are for 1-many relations and not really useful here?

i have tried tabbed layout etc.

i have also tried to create separate forms but testing that out I got it all scrambled and messy (guess I do not fully understand relationships!)

i guess i am doing something fundamentally wrong here? Or should approach it in some other way/structure my data differently?

I would be be very glad if someone could point me in better direction!
thanks!

[June7]

Subforms could be used for your 1-to-1 structure. However, this is not a normalized data structure.

The fundamental flaw appears to be a field for each question. Are these questions Yes/No, short answer, multiple choice? Consider:

tblQuestions
QuestionID
QuestionStatement
QuestionCategory

tblRespondents
RespondentID
RespLastName
RespFirstName
RespPhone

tblRespAnswers
RespID
QuestionID
Answer

I know you want to make data entry as easy as possible and the 1-to-1 structure with 400 answer fields has its appeal. It is a balancing act between normalization and ease of data entry/output.

[Celmst]

June7,
Thank you! You just made clear the fundamental flaw in my planning - I had a hard time wrapping my mind around normalization and how that would work (stuck in "excel thinking" I guess :-)).

I think I will give the 1-to-1 structure one more go. As you say it makes input and output easy and straight forward (especially for a beginner).

The problem i had with sub forms was more a layout problem. The sub forms would only give me spreadsheet-like data entry which is far from optimal. But I think can make it work.

Anyway - Thank you very much! Now I know how a more proper data structure would be.

[June7]

The subforms can give you single record layout, if you use a form as the source object and not table or query.

[Celmst]

Thanks once again!