Still learning and need help

[Laurie]

Access 2000 - I am having trouble setting up a new database. I'm sure
there must be an easy solution, but I have not had enough experience
building databases to know. I feel like I am in over my head, but don't
want to give up; I will be grateful for any help you can offer.
The database deals with patients who receive medical treatment, and the
purpose is to allow the treatment provider to track progress during
treatment and provide that information to the insurance company.
Each patient can (but won't necessarily) have more than one treatment
series. Each treatment series will have one provider and several review
dates. Each review will cover six problem areas being addressed, with
each having a progress indicator from 1-4. In addition, each patient
can have multiple diagnoses associated with each treatment series.
So far I am able to identify that I need tables for Patients, Treatment
Episode Info, Diagnoses Codes, Reviews, Problem Areas, and Providers.
Some of the relationships are very straightforward, for instance,
patients and providers are each one-to-many with Treatment Episodes. I
have linked Treatment Episode and Problem Areas each to Reviews in a
one-to-many relationship, hoping that will function as a linking table
and allow me to do several reviews in each problem area - does that
sound like it should work? I'm having trouble with forms and queries, so
perhaps I don't have them set up correctly - but before I muck around
too much more, I'd like help identifying if this is a design problem
with the database or if it seems sound. Also, I cannot figure out how to
link diagnosis codes with treatment episode and then how to display
multiple diagnoses when needed.
I'm sorry this is so long - I wanted to be specific enough to make my
questions clear.
Thanks for your help.

 

[John Vinson]

Access 2000 - I am having trouble setting up a new database. I'm sure
there must be an easy solution, but I have not had enough experience
building databases to know. I feel like I am in over my head, but don't
want to give up; I will be grateful for any help you can offer.
The database deals with patients who receive medical treatment, and the
purpose is to allow the treatment provider to track progress during
treatment and provide that information to the insurance company.
Each patient can (but won't necessarily) have more than one treatment
series. Each treatment series will have one provider and several review
dates. Each review will cover six problem areas being addressed, with
each having a progress indicator from 1-4. In addition, each patient
can have multiple diagnoses associated with each treatment series.

Sounds like you've given some thought to the Entities (real life
people, things, or events) of interest, each of which should have its
own Table.

So far I am able to identify that I need tables for Patients, Treatment
Episode Info, Diagnoses Codes, Reviews, Problem Areas, and Providers.
Some of the relationships are very straightforward, for instance,
patients and providers are each one-to-many with Treatment Episodes. I
have linked Treatment Episode and Problem Areas each to Reviews in a
one-to-many relationship, hoping that will function as a linking table
and allow me to do several reviews in each problem area - does that
sound like it should work?

It should, yes.

I'm having trouble with forms and queries, so
perhaps I don't have them set up correctly - but before I muck around
too much more, I'd like help identifying if this is a design problem
with the database or if it seems sound. Also, I cannot figure out how to
link diagnosis codes with treatment episode and then how to display
multiple diagnoses when needed.

Sounds like you need some many-to-many "resolver tables". A given
Patient will potentially have several Diagnosis Codes; a given
treatment episode may be set up to deal with the Patient, or may more
specifically deal with one or more of that patient's conditions.
Whenever you have a many to many relationship you need a *new table* -
for example, a PatientDiagnoses table with fields for the PatientID
and the Diagnosis Code. If patient John Vinson has Hypertension,
Hypercholesterolemia, Meniere's Syndrome and Terminal Geekiness there
would be four rows for him in that table. If you want to track
diagnoses in conjunction with episodes, you need a EpisodeDiagnoses
table similarly structured.

I'm sorry this is so long - I wanted to be specific enough to make my
questions clear.
Thanks for your help.

If you wish, you might post the names, Primary Keys, and foreign keys
(and perhaps some illustrative relevant fields) of your tables, or a
link to a website displaying the Relationships window (please don't
post attachments to the newsgroup though!)

 

[Laurie]

Thank you for your help, John.
I think I have resolved the relationships - it was good to know I was at
least on the right track. However, I am having trouble setting up a form
for user input. Also, I cannot get the multiple diagnoses and the
multiple reviews to show in a form - only the first of each. I don't
know enough to know whether the problem is in the form setup, in the
joins, or something else altogether. The multiple items do show up in
subdatasheets (is that what they are called - clicking the plus sign
beside the record in the table ....)
I set up the multiple diagnoses by connecting with the treatment
episodes, since each episode only involves one patient, and a patient's
diagnoses may change with time. Here are the tables I am using:
tblPatients
PatientID (PK)
Other patient info

tblTreatment Episodes
TxEpisodeID (PK)
PatientID (FK)
ProviderID (FK)

tblEpisode Diagnosis
EpisodeDxID (PK)
DxCode (FK)
TxEpisodeID (FK)

tblProviders
ProviderID (PK)

tblDiagnoses
DxCode (PK)
DxName

tblReviews
ReviewID (CPK)
ReviewDate (CPK)
TxEpisodeID (FK)
Six other fields - one for each aspect of treatment to be reviewed

Also, I have made a table of progress codes for the reviews, but am
wondering if a value list would be a better choice, since there are only
five choices.

Making all these tables fit with a form is my biggest problem now, I
hope! Any thoughts or advice you can give would be very welcome.
Thanks.

 

[John Vinson]

Thank you for your help, John.
I think I have resolved the relationships - it was good to know I was at
least on the right track. However, I am having trouble setting up a form
for user input. Also, I cannot get the multiple diagnoses and the
multiple reviews to show in a form - only the first of each. I don't
know enough to know whether the problem is in the form setup, in the
joins, or something else altogether. The multiple items do show up in
subdatasheets (is that what they are called - clicking the plus sign
beside the record in the table ....)

I set up the multiple diagnoses by connecting with the treatment
episodes, since each episode only involves one patient, and a patient's
diagnoses may change with time. Here are the tables I am using:
tblPatients
PatientID (PK)
Other patient info

tblTreatment Episodes
TxEpisodeID (PK)
PatientID (FK)
ProviderID (FK)

tblEpisode Diagnosis
EpisodeDxID (PK)
DxCode (FK)
TxEpisodeID (FK)

tblProviders
ProviderID (PK)

Surely some other information about the provider?

tblDiagnoses
DxCode (PK)
DxName

tblReviews
ReviewID (CPK)
ReviewDate (CPK)
TxEpisodeID (FK)
Six other fields - one for each aspect of treatment to be reviewed

That makes me a bit queasy. Suppose six months from now you decide
that you need to review EIGHT aspects of treatment. What do you do
then? Add two fields to the table, revise all your queries, revise all
your forms, revise all your reports? I'd have a (six-row) table of
Aspects and yet another many-to-many resolver table, unless these six
aspects are cast in concrete and will NEVER be subject to change. And,
in my experience, the statement "we'll never need more than <x>
values" is a huge red flag that someday soon they *will*.

Also, I have made a table of progress codes for the reviews, but am
wondering if a value list would be a better choice, since there are only
five choices.

These are not at all incompatible. By all means use a table, and use a
Listbox to choose from the table.

Making all these tables fit with a form is my biggest problem now, I
hope! Any thoughts or advice you can give would be very welcome.
Thanks.

Subforms are the key here. Base your Mainform on (perhaps, the exact
solution will depend on your needs) on a Query joining tblPatient to
[tblTreatment Episodes]; use a continouous (not single!) Subform based
on [tblEpisode Diagnosis]. On the Subform you can have a combo box
based on tblDiagnoses to display the dxName. Don't try to include the
diagnoses in the main form's query!

 

[Laurie]

So far so good.
Actually, my original design had a table for Aspects and a table linking
that to reviews, but when that didn't work for me I went to the design I
listed. You are right, of course, if the aspects change we have a lot of
work to do. But actually, they are set by a prescribed set of national
treatment standards. If and when those change we'll be in for a complete
overhaul anyway.

At any rate, I've tried it both ways, and still cannot figure out how to
make sure all aspects show up for all reviews. I need a simple way to
get this piece of the puzzle to fit.

The list box based on the progress table works great.
The main form and the treatment episode subform work great also. As I
said, the reviews piece needs some work.

And I need a little more direction on the combo box based on the
diagnosis table. How do I do that and allow more than one choice?

(Oh, and yes, there is more info included on the providers table and
most of the others as well - I was condensing information to what seemed
pertinent!) <g>
Thank you so much for your valuable input!!!!!

Thank you for your help, John.
I think I have resolved the relationships - it was good to know I was at
least on the right track. However, I am having trouble setting up a form
for user input. Also, I cannot get the multiple diagnoses and the
multiple reviews to show in a form - only the first of each. I don't
know enough to know whether the problem is in the form setup, in the
joins, or something else altogether. The multiple items do show up in
subdatasheets (is that what they are called - clicking the plus sign
beside the record in the table ....)

<gnnn> I *hate* subdatasheets. Well, I suppose they have some uses...
but like datasheets in general, I would use them ONLY for debugging.

I set up the multiple diagnoses by connecting with the treatment
episodes, since each episode only involves one patient, and a patient's
diagnoses may change with time. Here are the tables I am using:
tblPatients
PatientID (PK)
Other patient info

tblTreatment Episodes
TxEpisodeID (PK)
PatientID (FK)
ProviderID (FK)

tblEpisode Diagnosis
EpisodeDxID (PK)
DxCode (FK)
TxEpisodeID (FK)

tblProviders
ProviderID (PK)

Surely some other information about the provider?

tblDiagnoses
DxCode (PK)
DxName

tblReviews
ReviewID (CPK)
ReviewDate (CPK)
TxEpisodeID (FK)
Six other fields - one for each aspect of treatment to be reviewed

That makes me a bit queasy. Suppose six months from now you decide
that you need to review EIGHT aspects of treatment. What do you do
then? Add two fields to the table, revise all your queries, revise all
your forms, revise all your reports? I'd have a (six-row) table of
Aspects and yet another many-to-many resolver table, unless these six
aspects are cast in concrete and will NEVER be subject to change. And,
in my experience, the statement "we'll never need more than <x>
values" is a huge red flag that someday soon they *will*.

Also, I have made a table of progress codes for the reviews, but am
wondering if a value list would be a better choice, since there are only
five choices.

These are not at all incompatible. By all means use a table, and use a
Listbox to choose from the table.

Making all these tables fit with a form is my biggest problem now, I
hope! Any thoughts or advice you can give would be very welcome.
Thanks.

Subforms are the key here. Base your Mainform on (perhaps, the exact
solution will depend on your needs) on a Query joining tblPatient to
[tblTreatment Episodes]; use a continouous (not single!) Subform based
on [tblEpisode Diagnosis]. On the Subform you can have a combo box
based on tblDiagnoses to display the dxName. Don't try to include the
diagnoses in the main form's query!

 

[John Vinson]

At any rate, I've tried it both ways, and still cannot figure out how to
make sure all aspects show up for all reviews. I need a simple way to
get this piece of the puzzle to fit.

What isn't showing? Could you post the SQL of the query that's failing
to show "all aspects for all reviews"? It may be that you need an
Outer Join, but I'm not visualizing how you're linking the tables.

And I need a little more direction on the combo box based on the
diagnosis table. How do I do that and allow more than one choice?

YOU CAN'T.

A Combo Box has ONLY ONE VALUE. You will need to use a Subform for the
visit-diagnosis table, and enter as many *rows* in the table as there
are diagnoses. Each row will have only one diagnosis, so you can use a
combo box - but you need to enter multiple rows.