Table organization

[huv123]

Hi I have created a database that is meant to collect data about a study I am doing. It contains 2 main tables - 1 table regarding the staff that is working on the study and 2. Details about the participants and all their data (derived from a number of different forms/questionnaire).

Initially I had each questionnaire on a separate table but someone said it was not good database design and since there is a 1:1 relationship between the details about the participant and each of the questionaires, i.e. each participant cannot only fill out each questionnaire once) that I should have it all in one table.

However TheAceMan1 from the Forms forum said I should come here to discuss the table design etc since I am having issues with the design of one of my forms.

I am not certain about why he thinks there is an issue or why if there is an issue why its causing my problems but you can view the discussion here:

http://www.tek-tips.com/viewthread.cfm?qid=1443127&page=1

Here is a copy of the database also. The data is dummy/fake.


If anyone could help me as soon as possible I would really appreciate it. I am supposed to start using the database soon.

 

[fneily]

Bet you couldnt pull a clinically trial out of your pocket "
You win. Let me reach in my pocket to get the money to pay you. Right hand went into right pocket. Well I'll be - a clinical trial!

 

[ck1999]

Just a few questions regarding your error chart.
1. According to your 1st post they can have only 1 check
so therefore there are only 3 errors

0 1 1 1
1 1 1 0
1 1 1 1

2. Does it matter when pulling a form back up if they checked L1 or L2 as long as you say they checked an L

3 If 2 is does not matter then you only have 5 answers

1 no preference
No Checks
1 on each side

2 Slight Left

3 Slight Right

4 Strong Left

5. Strong Right.

These are the answers I would Store and then for calculations total lefts (slight left = 1, strong left =2 ) and total rights (slight right = 1, strong right = 2). The no preferences cancel each other out.


This code will require some setting up but works rather well IMHO.

Place a sub in your VBA

Sub checks(vques As String, vname As CheckBox)
    Dim vCount
    vCount = Abs(Me.Controls(vques & "L1")) + Abs(Me.Controls(vques & "L2")) + Abs(Me.Controls(vques & "R1")) + Abs(Me.Controls(vques & "R2"))
    If vCount >= 3 Then
        MsgBox "You can only have 2 options checked, Please uncheck a box if you would like to change your answer!"
        vname = False
    End If
End Sub

Then for EACH check box include this code in the onclick event.

Private Sub Q2L1_Click()
    If Q2L1.Value = True Then
        checks "q2", Me.Q2L1
    End If
End Sub
Private Sub Q2L2_Click()
    If Q2L2.Value = True Then
        checks "q2", Me.Q2L2
    End If
End Sub
Private Sub Q2R1_Click()
    If Q2R1.Value = True Then
        checks "q2", Me.Q2R1
    End If
End Sub
Private Sub Q2R2_Click()
    If Q2R2.Value = True Then
        checks "q2", Me.Q2R2
    End If
End Sub

This code if for the checkboxes of q2 only. You will place this code on each chechbox click event.

If you copy this code into your VBA and it does not function you may need to make sure that the property for the check box shows an event procedure in the on_click property.

This will help with the number of checks you still need to work on the database structure

ck1999

 

[huv123]

Hi CK - They [red] must have EITHER 2 or NO checks - they cant have 3 checks or one checks for a queation [/red]. - which means errors include 0100, 1000, 0010, 0001, 1110,1101,1011,0111 and so on...

I cannot control which squares they select on the form and i.e. if they dont have a preference for which hand, they may pick L1 and R2, L2 and R2, L1 and R1, L1 and R2, or L2 and R2 - there are all valid answers for "No preference"

From my table you can see there are 7 "valid" answers

0000 - Never do the task
1100 - Strong preference for left handed
0011 - Strong Preference for Right handed
0101 - Dont have a particular preference
1010 - Dont have a particular preference
1001 - Dont have a particular preference
0110 - Dont have a particular preference

This is a internet version of the questionnaire -

http://www.cse.yorku.ca/course_archive/2006-07/W/4441/EdinburghInventory.html

THeAceman1 does not think it is good database management to have one field for every checkbox. I am not certain how else I can store and retrieve information back to the form otherwise.

He has come up with a solution (of checking before update) but I could not get it working to my satisfaction.

Take a look?

Thanks

 

[ck1999]

1. Did you try my code?

2. If they have to have 2 or 0 checks then change the
check procedure to

Sub checks(vques As String, vname As CheckBox)
    Dim vCount,vcount2
    dim v
    v = left (vques,1) & (value(right(vques,1) +1) 
    vCount = Abs(Me.Controls(v & "L1")) + Abs(Me.Controls(v & "L2")) + Abs(Me.Controls(v & "R1")) + Abs(Me.Controls(v & "R2"))
    If vCount <> 0 or vcount <> 2 Then
           msgbox "Please complete the last question before  moving on"
     end if

    vCount = Abs(Me.Controls(vques & "L1")) + Abs(Me.Controls(vques & "L2")) + Abs(Me.Controls(vques & "R1")) + Abs(Me.Controls(vques & "R2"))
    If vCount >= 3 Then
        MsgBox "You can only have 2 options checked, Please uncheck a box if you would like to change your answer!"
        vname = False
    End If
End Sub

I have not tried the new additional code so you may have to try it.


Against Popular Ideas.

I would make 6 tables 1 for each questionnaire.
have 1 Field for each answer and a date field and patientID field.

This would simplify matters and get the ball rolling.

I like this idea because 1 field per check box or 1 field per question.
With a combined table 1 questionnaire may have 10 questions the other 5 the other 8, etc.

Please at least try the code to see if it works for you or what else you would like it to do.
ck1999

 

[huv123]

Thank you CK.

I am trying to decide how important normalization is to this process. I can understand when multiple users are using thousands of records but it is not something I could do by myself.

At the end of the day for statistical analysis all of the data would be dumped into one table anyway.

I will try the code today. Thanks.

 

[huv123]

v = left (vques,1) & (value(right(vques,1) +1)
his causes a syntax error