Data Recurrance Issue

[Guest]

I work for a therapist.

He would like an Access Form that patients can fill out on a touchpad that
is essentially a series of about 100 organized check boxes. The patient
would be checking the items that they have a problem with (i.e. hurt knee,
shoulder, asthma, etc.)

I could easily store this a number of ways. However, the boss would like
to track injuries and illness based on occurances and the number of visits it
took to alleviate. So each time a patient visits, they would check the
boxes. Then later we could run reports to see how many visits it takes for
certain injuries to heal, and if there is a difference between the results of
several different therapists.

The challenge I am having is that the patient could have a re-injury of the
same type and that would need to show up on the reporting as a separate
injury. So, a patient may have hurt their shoulder, comes in for 6 visits
and each time checks the box that the shoulder hurts. Then they visit for
something else without the shoulder being hurt, so it would not be checked on
those visits.

Later the same patient returns for a new shoulder injury (frequent with our
sports patients). Then he checks the box for shoulder problems again. This
is a new injury and needs to be tracked as such, but the checkbox does not
know the difference between an old and new injury. In addition, he may have
other issues that have not yet been taken care of, so I cannot create a field
for 'new issues', because he is checking boxes for 'new' AND 'old' issues.

I am trying to figure out the most efficient way to do this. I am
redesigning the database completely, so I have no pre-design to fix, I am
starting from scratch. If we were only working on 5 types of injuries I
could use several methods, but there are over 100 different 'basic' health
issues to deal with, and many more specific.

I am, of course, working withing the requirements of my employers, or I
would do it differently. Any help would be appreciated.

 

[Vincent Johns]

I work for a therapist.

He would like an Access Form that patients can fill out on a touchpad that
is essentially a series of about 100 organized check boxes. The patient
would be checking the items that they have a problem with (i.e. hurt knee,
shoulder, asthma, etc.)

I could easily store this a number of ways. However, the boss would like
to track injuries and illness based on occurances and the number of visits it
took to alleviate. So each time a patient visits, they would check the
boxes. Then later we could run reports to see how many visits it takes for
certain injuries to heal, and if there is a difference between the results of
several different therapists.

The challenge I am having is that the patient could have a re-injury of the
same type and that would need to show up on the reporting as a separate
injury. So, a patient may have hurt their shoulder, comes in for 6 visits
and each time checks the box that the shoulder hurts. Then they visit for
something else without the shoulder being hurt, so it would not be checked on
those visits.

Later the same patient returns for a new shoulder injury (frequent with our
sports patients). Then he checks the box for shoulder problems again. This
is a new injury and needs to be tracked as such, but the checkbox does not
know the difference between an old and new injury. In addition, he may have
other issues that have not yet been taken care of, so I cannot create a field
for 'new issues', because he is checking boxes for 'new' AND 'old' issues.

I don't see why you can't create such a field. What I envision here is
a [Complaints] Table that contains fields such as

[ComplaintsID] <-- primary key, identifying 1 complaint on 1 visit
[DateOfVisit]
[BodyPartID] <-- link to Table listing standard complaints
[IsNewIssue?] <-- yes/no field
[PatientID] <-- link to [Patient] Table containing client info

I may be wrong about the [DateOfVisit] field; perhaps you'd want an
intervening Table, [Visits], linked to [Patients], that would contain
the date, and instead of [PatientID] linking directly to [Patients]
you'd have [VisitsID] linking several complaints to one visit on a
specified date. Either design would probably work. How many check
boxed is one person likely to check on one visit?

I am trying to figure out the most efficient way to do this. I am
redesigning the database completely, so I have no pre-design to fix, I am
starting from scratch. If we were only working on 5 types of injuries I
could use several methods, but there are over 100 different 'basic' health
issues to deal with, and many more specific.

If you follow my suggestion (of course you may want to re-name the
fields; these are just to give you an idea), the [BodyPart] Table you
will probably want to have list the complaints in the greatest detail
that you expect to need to use. You can always aggregate them via other
Tables, for example with a [BodyPart].[System] field to distinguish
among skeletal fractures, muscular cramps, pulmonary problems, &c.

Since I believe that there are already standard catalogues of medical
complaints in existence, you might save a lot of trouble later by using
one of these standard systems of nomenclature. Try to use a
government-sponsored list (i.e., one that is in the public domain and
doesn't require copyright-permission paperwork for you to use it).
Failing that, a health-insurance company could direct you to an
industry-standard classification system.

I am, of course, working withing the requirements of my employers, or I
would do it differently. Any help would be appreciated.

Differently how? Are your employers dictating your internal Table
structure? If so, you're kind of stuck, and advice here won't help you
too much. If they're just specifying the user interface, report
contents, &c. (as I suspect is the case), that shouldn't be much of a
problem -- you have many ways to support a given user interface using
Access.

Each check box on your Form could then emit one record into the
[Complaints] Table. I would have it do this only when the record is
closed, however, as the client might change his mind and de-check a
checked box, and you probably wouldn't want to have to record actions
like that. If you wait for him to finish, you can just spit out one
record per checked box at that time. Your Form could include a command
button labeled "I'm finished."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Vincent,

Thanks for the response, you gave me a couple of great ideas. I still have
a challenge with how I am going to tell the difference between new and old
issues checked on the form by the patient. Unless I have 2 checkboxes, I
would have to run some code for each check box to see if that issue was also
on one of the last couple of recent visits. Yuck.

I also have to figure out how my reporting will distinguish which injuries
go together as one injury vs. a different injury in the same location. Is
there a way to store it, or am I going to have to find a way to write some
code that will differentiate between them?

William

 

[Amy Blankenship]

What about this:

InjuryTypes--defines injuries a patient could have
InjuryTypeID
InjuryTypeDesc

Patient--defines a patient
PatientID
FirstName
LastName
etc

Therapist--defines a therapist
TherapistID
FirstName
LastName
etc

Visit--defines a visit
PatientID
TherapistID
VisitDate

PatientInjury--defines a particular injury in a patient
PatientInjuryID
PatientID
InjuryID
IsResolved

VisitPatientInjury--defines visits during which a particular injury was
present
VisitID
PatientInjuryID

Then, at every visit, the FIRST thing you ask is whether existing injuries
have been resolved, then you ask if they have any of the other possible
problems. If a previously resolved injury type occurs again, it is inserted
as a new PatientInjury. Makes sense?

HTH;

Amy

 

[Guest]

Amy,

Thanks for your input. I'm kinda headed that way.

However, the boss would like the patient to choose items from a fresh list
each time they visit, so we can track what is still actually happening with
them. If we prompt the patient with the previous list, they will basically
say, 'oh yeah, that is wrong too', when in fact that issue was resolved a
number of days prior.

We currently do this on paper and then someone has to enter the data
manually. But the current database is really limited. I'm trying to
automate it and give us better reporting of injury recovery.

William

 

[Vincent Johns]

Vincent,

Thanks for the response, you gave me a couple of great ideas. I still have
a challenge with how I am going to tell the difference between new and old
issues checked on the form by the patient. Unless I have 2 checkboxes, I
would have to run some code for each check box to see if that issue was also
on one of the last couple of recent visits. Yuck.

Starting with Amy Blankenship's table design, you might add a field or
two to the [VisitPatientInjury] Table to indicate if the patient thinks
the injury is a new one. (Do you have an objective way to determine
this? Perhaps the physician could provide some input, via a separate Form.)

I also have to figure out how my reporting will distinguish which injuries
go together as one injury vs. a different injury in the same location. Is
there a way to store it, or am I going to have to find a way to write some
code that will differentiate between them?

William

You probably don't need any code to do this. Well, not VBA code in a
Module, but you will need to write Queries to display the contents of
updated Tables, to summarize the transactions taking place during some
defined time period, etc.

You might need to write some VBA code to populate the Tables based on
responses to the questionnaire. It depends on how fancy you want to
make everything, and since you're likely to have untrained users
entering data, that probably means you'll need a fair amount of
error-checking code.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Amy Blankenship]

Vincent, Thanks for the response, you gave me a couple of great ideas. I
still have a challenge with how I am going to tell the difference between
new and old issues checked on the form by the patient. Unless I have 2
checkboxes, I would have to run some code for each check box to see if
that issue was also on one of the last couple of recent visits. Yuck.

Starting with Amy Blankenship's table design, you might add a field or two
to the [VisitPatientInjury] Table to indicate if the patient thinks the
injury is a new one. (Do you have an objective way to determine this?
Perhaps the physician could provide some input, via a separate Form.)

That's what the IsResolved field is for.

HTH;

Amy

 

[Amy Blankenship]

Amy,

Thanks for your input. I'm kinda headed that way.

However, the boss would like the patient to choose items from a fresh list
each time they visit, so we can track what is still actually happening
with
them. If we prompt the patient with the previous list, they will
basically
say, 'oh yeah, that is wrong too', when in fact that issue was resolved a
number of days prior.

Once a problem was marked as resolved, it would not be presented again in
the first query.

The other option is, once a patient answers yes to something they've
previously answered yes to, you ask them "Is this the same problem you had
on (Date) or a new injury?" Then you either open a new injury or associate
it with the old one.

Bottom line is that the patient is going to have to tell you if it's a new
injury or not, unless you have the physicians going in and marking things as
resolved, which it sounds like you don't want. There is absolutely no way
around having some human person make that call. The data structure I've
given you will handle the data necessary to report once the call has been
made, but it can't make the call for you.

Good luck!

-Ay