need to split a questionnaire with 400 fields

[Shusha]

Hi there,

I have a problem where i cld use some help.

Scenario: There is a questionnaire that has over 400 fields. a composite of a few fields , lets say from Q1 to Q10 may be used to get a total field. there is code attached to this calc.. and it goes on for all the questionaire. Many calculations are performed..

the user needs to have one form ( in the case of me splitting the table in 2) come from this questionnaire. How do i go about it? I see that a lot more of the tabls are going to be created with lots of fields in them.

Is there a simple way to have two tables, but one form. Do i make a query that joins these two, as all fields need be displayed. Also, if i do that, the query comes up with too many fields. so i am stumped right now. any help is welcome.

Thanks in advance.
usha

 

[lespaul]

You should design your tables so they meet the requirements for normalization (see JeremyNYC's website:

http://www.alphabetcitydataworks.com

in the developer's section for 'Fundamentals of Relational Database Design). It really doesn't matter what the form looks like or how many fields it has. What you need to look at is WHAT you are gathering information about.

So, let's say this is a standard survey database. I would have:

tblPeople (PeopleID, Name, Address, any other information about the PERSON)

tblQuestions (QuestionID, QuestionText)

tblAnswers(AnswerID, QuestionID, AnswerText)

tblSurvey (SurveyID, SurveyName)

tblSurveyQuestions (SurveyID, QuestionID)

tblPeopleSurvey (PeopleSurveyID, PeopleID, SurveyID)

tblPeopleAnswers (PeopleSurveyID, AnswerID)

This may be a little detailed for what you need. If you aren't going to have multiple surveys, then you don't really need tblSurvey. But this should give you some idea of how to structure your tables. Be sure to read that article, it will go a long way to helping you develop a properly designed relational database.

If you don't think it's worth the time, you should find some of the threads that deal with extracting information from a non-normalized database. The time you spend now designing it correctly will greatly improve your ability to quickly gather the information you need.

leslie

 

[BNPMike]

Access is very good at de-linking the data from its presentation. So if you've got 200 fields in a table you can spread them out over any number of screens. You can also collect data from many tables (max 32?) onto one screen.

So as Lespaul says, design you table(s) in a completly abstract way and then set about presenting them in the most friendly, attractive or easy way, depending on your priorities.

 

[Shusha]

Hi leslie,

I am sorry i took time replying as my hubby and i were involved in a bad accident and i had to attend to ins. etc. hence my delay in thanking u for ur invaluabe help.

lelsie, thks for pointing me to that site. it was informative and helpful. I did print it out and read the whole enchilada..

I have been developing access systems in a long while. So most of it at that site was like a re-read for me.
I do understand the concepts mentioned there. your example was very good.I am going to use part of ur example here to ask u for ur help if u can help me out.

Regards (standard survey database) [{in my case - a psychological questionairre given to clients that consist of 300 questions and some calculated fields which cld be a compostie of a few questions; a few calculated fields to keep track of number of 999's and so forth. so in all this table will have or shd have 450 fields.. Also, this 450 field table needs to be used to churn out the form which is the exact copy of the questionairre the client fills out. so all fields need be displayed.}]

I have split the table in 2, and kept the questions in one table and the answers in another. can u tell me how i can link the tables to hold the associated information for one questionairre.? also, there are modules attached to these questionnaires.. what is happening is the system tells us the modules are too large.. so how can i do the following?

1. create 2 tables to hold the questionnaire?
2. use these two tables to create one form ( a tab form) where the first 3 tabs are of the questionaiire and the last one is the answers one?
3. how does one break up a module that is too large as the system comes to a halt when it reaches a certain number of lines.. ?
4. have u encountered the same problem?

I am going to change some of the fields to make myself understood. the question is inserted in the field name like for ex: client uses aggresion : answer1
client comm. clearly : answer2
client comm. using sign: answer3
client comm. total: answer1+answer2+answer3
minimum communication: : mincom (calculated fld)
mean communication: meancomm (calculated fld)
maximum communication: : mincomm (calculated fld)
client dev. score : cdevscore (in the module - if client comm. total = 7 then dev.score = 8 else 9)
and so forth..

i think u get the drift..this is just a simple example.. the questionairre is rather large and cumbersome..

tblPeople (masterno, Name, birthdate, Address, etc.)
tblQuestions (Masterno, tstdate, testtype, answer1, answer2, answer3, answer4, answer5, answer6, answer7, min comm., meancomm., maxcomm., .cdevscore, ... q400,

so can u tell me how it is possible to link tables if i were to split them and also use a module that takes into acct both tables.

thanks for ur input.. we hav already done a lot of teh vineland, mullen, cars etc all psychological tests.. and i have working versions of the same.. with tables, forms, modules etc.. this particualr questionnaire + the newer ones are gettign bigger..

any help is appreciated..

thanks
usha

 

[lespaul]

So, here's some more detail:

Generally though, you need tblQuestion that has
QuestionID, QuestionText
1 Client Communication Skills


then you would have tblAnswer
AnswerID QuestionID AnswerText
1 1 Communicated Clearly
2 1 Communicated w/ Sign
3 1 Communicated w/ Aggression

So now we have question #1 and 3 Answers to that question.

Now you should have a tblPersonSurvey that stores the person who took the survey and a SURVEYID field (could be an autonumber). Then we need tblPersonSurveyAnswers that have the SURVEYID from the tblPersonSurvey the questionID and the answerID. Set this table up so that the combination of SURVEYID, QUESTIONID, ANSWERID is the primary key, which will allow multiple answers to each question.

Now, for your statistical information. Are these the standard min, max and mean or are they calculated from the person's score? Or do you need both the norms and the persons' score? If you need both then you need another table that stores the norms and you calculate the person's score on the fly in a query.

If you have a site that you can post the survey, I'll take a look at it.

Hope this helps!

Leslie