Medical Assessment with more than 255 fields

[Jaz]

Greetings;
I have a database that I need to create that has many single checkboxes.
This is a medical assessment and has many different sections to rate. For
instance there is a presenting problem checklist with 50 checkboxes alone and
people can present with multiple problems. From what I count, there are
about 20 different sections all with multiple check boxes. Would you suggest
that I have 20 different tables consisting of each check box? If so, how do
I link this all together so I can print one assessment report with info from
all these tables. And is there a way that I can program the report to only
print items checked off and not every field? Thanks so much for your help.
I am also not that very savvy in Access just yet. Running Access 2003.

 

[Klatuu]

You are trying to create a spreadsheet in a relational database.
Using check boxes for a list like that is not the correct way to design such
a system.
What you should have is two tables.
The first table should have the patient's demographic information.
The second should be an assessment table. It should have on record for each
presenting problem. That record should contain whatever information to a
presentation. If a record for a specifc problem is not in the assessment
table for the patient, that means the patient did not present with the
problem.

Now, what you do need is table of all the possible problems that could be
presented. Here is where you control checking off the list. Depending on
whether the assessment is done using the database in real time or whether a
hard copy worksheet is used, would determine how you contruct your form.

If you can describe the business rules and the assessment procedure, perhaps
I can help with some design ideas.

 

[Mark]

Consider using the following table structure ---

TblPatient
PatientID
<Other patient fields>

TblSection
SectionID
SectionName

TblSectionCheckList
SectionCheckListID
SectionID
SectionCheckboxFieldName

TblAssessment
AssesasmentID
PatientID
SectionCheckListID

Base your report on a query that includes all the above tables. In the query
you can set criteria that determines what sections to include and what
SectionCheckListID (checkboxes) to include.

Steve

 

[Klatuu]

Sorry, Mark, but that is not correct.
It is not necessary to have a bunch of check boxes in a table.
Again, Access is not a spreadsheet. It is a relational database. You only
store data that provides information about what has occurred. It makes no
sense to store data about what did not occur.

 

[Mark]

Dave,

You did not interpret the design of the tables correctly.

TblSections is necessary for the database to record the names of all
sections. TblSectionCheckList is necessary for the database to record all
the "categories" in each section. TblAssessment identifies the Patient
assessed, ONLY the sections that are included in that patient's assessment
and for each sectrion ONLY the "categories" that are included in each
section included in the patient's assessment. TblAssessment only stores data
about what did occur. There are NO checkboxes in any table; only fields. The
suggested design is normalized.

Steve

 

[Klatuu]

You are correct. I misread your closing statement.
What you propose would be the way I would do it.

 

[Michael Gramelspacher]

You are correct. I misread your closing statement.
What you propose would be the way I would do it.

Well, I don't know. I see these tables:

Patients
Assessments
Sections
Checklists
Questions

And these relationships:

Patient Assessments
AssessmentSections
SectionChecklists
ChecklistQuestions
PatientAssessmentResponses

The OP seems to want the responses to always be Yes or No.

It seems to be a bit more than trival. Well, to me at least. We need more
information. Is a section the same as a checklist? Can a section have more
than 1 checklist? Can responses ever be anything other than yes/no?

 

[Jaz]

Thanks Michael for your help. To answer your question, the majority is only
yes/no check boxes. They want it that way because of risk management
purposes. All the necessary assessment indicators that they need to
address/rate are in front of them, so it is either yes/no. There may be the
odd text box here an there for annotation purposes. The clinicians will be
doing this in real time, so what they are wanting is ONE FORM, that they can
just scroll down and simply check off all the pertinant information using
yes/no check.

There are about 10 different sections (i.e. presenting problems, Diagnosis;
Orientation to time & place, social supports) and each of these sections has
at least 15 options (in the form of yes/no checkboxes). I can't do a
combo-box because there can be multiple answers under each section.

How can I create a report from a query when all you are allowed is 255
fields in the query? I have counted and all together there are 475
check-boxes and fields in this form. I will also have to pull up some form
so they can search previous entries for the patient.

 

[Mark]

If you follow the table design I suggested, at most you will have ten fields
in your query!!

Steve

 

[Jaz]

ok please forgive me if this sounds like an stupid question, I am a newbie
and really appreciate your patience.....

I think I understuand your structure, however when it comes to the
SectionCheckboxFieldName, so are you saying for each yes/no field that I
have, it will not actually be a field, it will be a record within the
TblSectionCheckList table?

Do you know of any sample database out there with a similar set up?

 

[Jaz]

Sorry...and more question from my last question, if the yes/no fields
actually appear as records within that table, how would this appear on the
form for the clinicians to check off? Will they be able to see all their
"options" if I list them in table?

 

[Michael Gramelspacher]

Here is a diagram I did, not that it is necessarily correct. It seems that
Section and Checklist are really the same thing.
http://www.psci.net/gramelsp/temp/PatientAssessments.jpg

Take a look at this. It is indeed crude, but I think it is close to what you
need, of course, based on my understing of the problem.

http://www.psci.net/gramelsp/temp/Crude_Example.zip

It was a little tricky for me.

 

[Duane Hookom]

Take a look at "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This demo
was originally created for a medical survey.

 

[Jaz]

Wow....thank yo so much for the sample Very kind of you. It does indeed
look tricky. I am going to examine it closer and see if I can figure out
your design. Thanks again so much.

 

[Michael Gramelspacher]

The form and the two subforms are straight forward, if you understand subforms.
If you select a section in the first subform, nothing will show in the second
subform initially. You need to click the Create List command button to fill it
with questions with default responses. I could have made a checkbox for YesNo
or an option group, but then there could never be anything but yes or no, which
is probably not the case. Of course, you could insert another column in the
table for a free-text response in addition to a YesNo response:

Question: Have you ever had an operation? if Yes, explain. Yes No
(explanation)

The tricky part was the query record source for the second subform. It had to
be updateable, and it had to show the sequence number of the question.

 

[Mark]

I'll answer both your questions here ....

Before entering any patient assessment data in your database, you would need
to fill TblSection and TblSectionChecklist first. TblSection would look
like:

1 Presenting Problems
2 Diagnosis
3 Orientation To Time And Place
4 Social Supports
etc ....

In TblSectionCheckList, SectionCheckboxFieldName are your options. You would
have a record in this table for each option in each section.
SectionCheckboxFieldName are your option questions NOT Yes/No answers.
TblSectionCheckList would look like:

1 1 Option Question1 For Presenting Problems
2 1 Option Question2 For Presenting Problems
.....
15 1 Option Question15 For Presenting Problems
16 2 Option Question1 For Diagnosis
17 2 Option Question2 For Diagnosis
.....
30 2 Option Question15 For Diagnosis
31 2 Option Question1 For Orientation To Time And Place
32 2 Option Question2 For Orientation To Time And Place
.....
45 2 Option Question15 For Orientation To Time And Place
etc ....

For each patient assessment, the answers (Yes or No) to the option questions
would be recorded in TblAssessment. I forgot to include in my previous
response one field in my proposed tables for TblAssessment. You need to add
a field named YesNoResponse to TblAssessment. YesNoResponse would be where
you record Yes or No for each option question.

Steve