over 400 fields in two forms coming from two tables

[Shusha]

Hi there,

I am in a real fix as to how-to solve this problem. If u cld point me in th eright direction or help me wld appreciate it very much.
I have this humungous form with over 400 questions. the previous developer has split this questionaire into 2 seperate tables with one table being the questions and the 2nd opne being the answers. now the calculations for mean are done throughout the forms.. where one form is passing info to another. i have the master table with cifno which identifies the client for who the assessment is being done. .I have added the cifno number fields to both of these tables.. i wld very much liek to be able to open the form for the client i am ebntering info for and also be able to pass this info between the two tables. is it possible.
i will give the example.

Table.PAT = client table with the cifno number, lets say this clients number is 1980
table questions = no record for 1980, would like to add the info.
table.answer - use the same record number as in table 1 that is 1980..

in other words.. i wld like to have one rec in table questions and one record in table answers.. but the fields from these 2 tables are containing one rec for 1980.. is this possible.. or if not.. can u tell me how i can solve this problem.. thanks
Usha

 

[kjv1611]

You might try providing more info, or more sample data or something... I don't know about anyone else, but I'm having trouble following the whole deal..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[LarryDeLaruelle]

Ushameister:

If each client only responds to the questionaire once, then you only need one table:

tblResults
ClientID
+ Client Identifiers
+ any other data items
Qst001
Qst002
to
Qst400

If the client may respond more than once (as in a pre/post situation) then you need two tables:

tblClients
ClientID
+ Client Identifiers

tblResults
ClientID
ResponseDate
+ any other data items
Qst001
to
Qst400

Link on ClientID

In either case, the question/response fields can be named as I have shown above and the question detail can be entered into the fields caption property.

It should not be too difficult to parse your existing data into a new format as above and, in either a 1 or 2 table scenario, eliminate the need to have two forms.

I have done a couple of db's similar to your situation (although my largest was just under 100 questions) and have used both the single table (for a once only response) and the two table for pre/post.

For the larger number of responses, I use a tab control on the input form to keep the user's view managable and uncluttered.

HTH.

Larry De Laruelle

 

[Shusha]

Hi Larry,

Thank you very much for spending time on my question. I still dont get it..I am going to give you an example here and maybe you can help me out.

The questionnaire is a genetic profiling of a client with 440 questions in it - done once for the client

there is a client table with client demographics which includes a master number - lets say master#984, John smith

Since Access does not allow more than 255 fields in a table the questionnaire has been broken down into two tables - Gene1 and Gene2.

Previously they used to manually enter the master numbers to link the two tables. it is s long story.. the master id was made up of family type +family number+client number+ whatever.. i tore down the numbering system and now each client has a CIFNO (client information file number) which connects all tables - there are about 15 more tables with different assessment etc.. so u see it is a long story indeed !!!!!

Well, this table uses reverse coding .. lets say questions
10, 13, 134, have 4 in it the data is reversed to 3 and so forth.. all of it works fine.. but what i want to do is simple..

Right now, I have

one client file with the master numbers.
two tables called gene1 and gene2

and one module that called gene12 which does all of the calculations for the two tables..
but the problem is that some of the information from the first table needs to be used in table 2. i will paste some of it and u can understand where my dilemna is.

Public Function calculategene()

Dim frm1 As Form
Dim frm2 As Form
'Set frm1 = Screen.ActiveForm
'Set frm2 = Screen.ActiveForm
Set frm1 = Forms!gene_Interview_Part_1
Set frm2 = Forms!gene_Interview_Part_2

If frm1!LV1q62 = 1 Then frm1!LV1q62r = 5
If frm1!LV1q62 = 2 Then frm1!LV1q62r = 4
If frm1!LV1q62 = 3 Then frm1!LV1q62r = 3
If frm1!LV1q62 = 4 Then frm1!LV1q62r = 2
If frm1!LV1q62 = 5 Then frm1!LV1q62r = 1
If frm1!LV1q62 = 999 Then frm1!LV1q62r = 999

If frm1!LV1q44 = 1 Then frm1!LV1q44r = 5
If frm1!LV1q44 = 2 Then frm1!LV1q44r = 4
If frm1!LV1q44 = 3 Then frm1!LV1q44r = 3
If frm1!LV1q44 = 4 Then frm1!LV1q44r = 2
If frm1!LV1q44 = 5 Then frm1!LV1q44r = 1
If frm1!LV1q44 = 999 Then frm1!LV1q44r = 999

.....
.
.
.
.
counter37 = 0
counter38 = 0
If frm1!LV1q33 = 999 Then counter37 = counter37 + 1
If frm1!LV1q63 = 999 Then counter37 = counter37 + 1
If frm1!LV1q75r = 999 Then counter37 = counter37 + 1
If frm1!LV1q98 = 999 Then counter37 = counter37 + 1
If frm1!LV1q119 = 999 Then counter37 = counter37 + 1
If frm1!LV1q127r = 999 Then counter37 = counter37 + 1
If frm2!LV2q203 = 999 Then counter38 = counter38 + 1
If frm2!LV2q263 = 999 Then counter38 = counter38 + 1

frm1!LV1999 = counter1 + counter3 + counter5 + counter7 + counter9 + counter11 + counter13 + counter15 + counter17 + counter19 + counter21 + counter23 + counter25 + counter27 + counter29 + counter31 + counter33 + counter35 + counter37
frm2!LV2999 = counter2 + counter4 + counter6 + counter8 + counter10 + counter12 + counter14 + counter16 + counter18 + counter20 + counter22 + counter24 + counter26 + counter28 + counter30 + counter32 + counter34 + counter36 + counter38
frm2!LV2ct999 = frm1!LV1999 + frm2!LV2999

End Function


this works.. only this is we have to open forms 2 and call form1 from there and manually enter it from there. is there some way i can open form1 and then pass the info to form2 ..
I am looking for something like open dataset and that way the module does not have to get changed. is there a quick way tofind an anser to this.. thanks for ur help

usha

 

[DanJR]

This is not an answer to your problem, but just an aside...

I have developed a number of surveys now, and I too have inherited databases that treat the questions as attributes to a relation ie, fields Q1....Qn. As pointed out, this can work fine when the number of questions are small, but becomes a arkward with a large number of questions.

In my situates, the quickest method (with respect to the life of the project) is to start from scratch.

why not have one table that stores the results of the genetic profile: e.g.
[tt]
QUESTION PROFILE
question_code cifno
question_text question_code
answer
[/tt]
if you have a survey thats repeated, then:
[tt]
QUESTION PROFILE
question_code cifno
question_text question_code
phase
answer
[/tt]
etc.....

cheers,
dan