I have a client who is profiling a medical condition.  The profile 

involves over 80 different yes/no items on a form that patients fill in.  

A table with over 80 fields sounds somewhere between silly and a 

nightmare, and I presume that a table with a PatientID field and one 

field that looks up one of 80 + values from another table is going to be 

faster to query and design around once the data begins to pile up.

 

Any suggestions?

 

Thanks

 

Andrew Wrigley

Andrew,



I would go with your answer (PatientID and lookup), you could save a

little query time probably by only writing in the results to questions

answered YES and assuming if there is no entry the result was NO - you

will have to decide if this is suitable for your solution.



Regards



Paul



-----Original Message-----

From: Andrew Wrigley [mailto:awrigley@y...] 

Sent: 21 January 2002 22:09

To: Access

Subject: [access] Tables with large numbers of fields





I have a client who is profiling a medical condition.  The profile 

involves over 80 different yes/no items on a form that patients fill in.



A table with over 80 fields sounds somewhere between silly and a 

nightmare, and I presume that a table with a PatientID field and one 

field that looks up one of 80 + values from another table is going to be



faster to query and design around once the data begins to pile up.

 

Any suggestions?

 

Thanks

 

Andrew Wrigley

Thanks the quick response.  I agree with you re inputting yes only.  The 

problem I have is the interface that the patient/client will see - the 

more it resembles its paper ancestor the happier the client will be...



Thanks



Andrew

Andrew,



I have previous worked on some medical data capture products and don't

doctors just love paper....



Set up two scenarios to prove the point to your client:



Database 1:



A form containing a combo box, an Add button, a Delete button and a list

box.



The combo box would list all the conditions and use reference lookup so

only entering the first few letters would be required.



Hit tab and it moves over to the Add button, press Space to add the

Condition, use some DAO or ADO to add the appropraite details to the

table and requery the list box (which shows the current patients

entries) to show each condition as it is added.



Once the list box is requeried use SetFOcus to go back to the combo box.



Repeat the process till all the "yes" conditions are completed.



Database 2:



As the client wants duplicating the current paper format.





Run a side by side test and see who wins.



Hopefully sense will prevail.



Regards



Paul











-----Original Message-----

From: Andrew Wrigley [mailto:awrigley@y...] 

Sent: 21 January 2002 22:48

To: Access

Subject: [access] Re: Tables with large numbers of fields





Thanks the quick response.  I agree with you re inputting yes only.  The



problem I have is the interface that the patient/client will see - the 

more it resembles its paper ancestor the happier the client will be...



Thanks



Andrew

I did one of these once and it got to be a big mess.  Because of Access 

limitations, I had to break the data apart into multiple tables and 

reconstitute it on the fly.  Had similar problems with reaching the max 

number of controls allowed on forms and reports.



Another approach is to store multiple pieces of info in a single Number 

(Long) field.  A bit more awkward as it requires more coding, but its 

just an option.  See example code below (uses bit arithmetic).  I just 

did a small, contained example to keep from boring you, but you get the 

idea.

=========================================

Const IS_PATIENT_MALE As Long = 1

Const IS_PATIENT_OVER_60 As Long = 2

Const IS_PATIENT_SICK As Long = 4

Const IS_PATIENT_INSURED As Long = 8

Const IS_PATIENT_DIABETIC As Long = 16

Const IS_PATIENT_A_JERK As Long = 32



Public Function AnalyzeBits(FileInput As Long) As String



    If FileInput And IS_PATIENT_MALE Then

        AnalyzeBits = AnalyzeBits & "Patient is male." & vbCrLf

    Else

        AnalyzeBits = AnalyzeBits & "Patient is female." & vbCrLf

    End If

    If FileInput And IS_PATIENT_OVER_60 Then

        AnalyzeBits = AnalyzeBits & "Patient is over 60." & vbCrLf

    Else

        AnalyzeBits = AnalyzeBits & "Patient under 60." & vbCrLf

    End If

    If FileInput And IS_PATIENT_SICK Then

        AnalyzeBits = AnalyzeBits & "Patient is ill." & vbCrLf

    Else

        AnalyzeBits = AnalyzeBits & "Patient is healthy." & vbCrLf

    End If

    If FileInput And IS_PATIENT_A_JERK Then

        AnalyzeBits = AnalyzeBits & "Patient is a jerk." & vbCrLf

    Else

        AnalyzeBits = AnalyzeBits & "Patient is cooperative." & vbCrLf

    End If

    

End Function



Public Function setPatientSex(IsMale As Boolean, PatientInput As Long) As 

Long

    If IsMale Then

        setPatientSex = PatientInput Or IS_PATIENT_MALE

    Else

        If PatientInput And IS_PATIENT_MALE Then

            setPatientSex = (PatientInput - IS_PATIENT_MALE)

        Else

            setPatientSex = PatientInput    '* no change

        End If

    End If

End Function

==============================

R



> I have a client who is profiling a medical condition.  The profile 

> involves over 80 different yes/no items on a form that patients fill 

in.  

> A table with over 80 fields sounds somewhere between silly and a 

> nightmare, and I presume that a table with a PatientID field and one 

> field that looks up one of 80 + values from another table is going to 

be faster to query and design around once the data begins to pile up.