Database design help

I work for a non-profit that assists low income people
get medications from the pharmaceutical companies.

I have been working weeks on a database, but keep getting
stopped.

A person comes in with a list of medications they need to
take. It can be from one, up to around 13 to 15,
depending on the person.

The same person can have one doctor, or multiple doctors,
but usually one doctor.

We fill out an application for each medicine
(prescription or script). Regardless if they have
multiple medicines from the same company.

We gather the paperwork, send it to the Dr for signature,
receive the paperwork back from the Dr. and send it to
the pharmaceutical company.

The problem come in here. After the initial order, the
person comes back in when their medication runs out.
Either in 30, 60, or 90 days. Then we do a re-order for
the person.

How do I keep a history of what we have done for the
person?

And for each medicine?

I would like to grow the database in the future to
include the pharmaceutical companies and their respective
medicines.

I would also like to have the system tell me when a
persons medication is about to run out, so that we can
reorder it and the person does not have to go without
medicine, which they have to do now.

We also have to track the following:

The date the person originally came in and joined
The date we originally received a prescription
The date we processed the prescription and did the paper
work
The date we sent the paper work to the Dr for signature
The date we received the paper work from the Dr with his
signature
The date we send the paper work to the pharmaceutical
company
The date the medicine comes in
The date we contact the person that their medicine is in
and available to be picked up.


Any help and or guidance would be very greatly
appreciated, and help us to help many other people that
would not normally get the medicines they need to live.

Thanks in advance,

Kirk Rotramel
P.A.L.S. - Prescription Assistance League Service
Patient Advocate

It would certainly be possible to do all that in Access. But you will have
to start by designing a properly normalized database structure. You have
started well, by clearly identifying & describing your data needs. If you do
not know the term "normalized", start here for a dry but instructive read:

http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC


"Kirk" wrote in message
...

I work for a non-profit that assists low income people
get medications from the pharmaceutical companies.

I have been working weeks on a database, but keep getting
stopped.

A person comes in with a list of medications they need to
take. It can be from one, up to around 13 to 15,
depending on the person.

The same person can have one doctor, or multiple doctors,
but usually one doctor.

We fill out an application for each medicine
(prescription or script). Regardless if they have
multiple medicines from the same company.

We gather the paperwork, send it to the Dr for signature,
receive the paperwork back from the Dr. and send it to
the pharmaceutical company.

The problem come in here. After the initial order, the
person comes back in when their medication runs out.
Either in 30, 60, or 90 days. Then we do a re-order for
the person.

How do I keep a history of what we have done for the
person?

And for each medicine?

I would like to grow the database in the future to
include the pharmaceutical companies and their respective
medicines.

I would also like to have the system tell me when a
persons medication is about to run out, so that we can
reorder it and the person does not have to go without
medicine, which they have to do now.

We also have to track the following:

The date the person originally came in and joined
The date we originally received a prescription
The date we processed the prescription and did the paper
work
The date we sent the paper work to the Dr for signature
The date we received the paper work from the Dr with his
signature
The date we send the paper work to the pharmaceutical
company
The date the medicine comes in
The date we contact the person that their medicine is in
and available to be picked up.


Any help and or guidance would be very greatly
appreciated, and help us to help many other people that
would not normally get the medicines they need to live.

Thanks in advance,

Kirk Rotramel
P.A.L.S. - Prescription Assistance League Service
Patient Advocate

On Sun, 2 May 2004 17:16:25 -0700, "Kirk"
wrote:


I work for a non-profit that assists low income people
get medications from the pharmaceutical companies.

I have been working weeks on a database, but keep getting
stopped.

This is a nontrivial but reasonable project... BUT!

If you're in the United States or the EU, be aware that such medical
records come under pretty strict privacy regulations. You might be
opening your organization to some pretty serious legal trouble unless
the database is designed in accordance with these (complex and
finicky) rules. You may want to consider hiring a consultant with
experience in developing medical information systems. For the actual
text of the regulation see http://www.hhs.gov/ocr/hipaa/finalreg.html.

If you are comfortable with going ahead, you still may want to take
this offline with someone willing to donate some time to interactively
determining the requirements and sketching out a solid table design.
I'm not free to help right now but if your timeline will allow it you
could contact me later this month (jvinson at wysardofinfo dot
com); I'd be willing to donate some time. Note that I am *NOT* a HIPAA
expert of any description, though I'm aware of that particular large
shark in these murky waters!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

"John Vinson" wrote in message
news
On Sun, 2 May 2004 17:16:25 -0700, "Kirk"
wrote:


I work for a non-profit that assists low income people
get medications from the pharmaceutical companies.

I have been working weeks on a database, but keep getting
stopped.

This is a nontrivial but reasonable project... BUT!

If you're in the United States or the EU, be aware that such medical
records come under pretty strict privacy regulations. You might be
opening your organization to some pretty serious legal trouble unless
the database is designed in accordance with these (complex and
finicky) rules. You may want to consider hiring a consultant with
experience in developing medical information systems. For the actual
text of the regulation see http://www.hhs.gov/ocr/hipaa/finalreg.html.

If you are comfortable with going ahead, you still may want to take
this offline with someone willing to donate some time to interactively
determining the requirements and sketching out a solid table design.
I'm not free to help right now but if your timeline will allow it you
could contact me later this month (jvinson at wysardofinfo dot
com); I'd be willing to donate some time. Note that I am *NOT* a HIPAA
expert of any description, though I'm aware of that particular large
shark in these murky waters!


Good point about the privacy issue. Now I remember this thread:



Kirk, go to groups.google.com, select Advanced Groups Search, then enter the
above ID ( *including the s* ) into the Message ID box for more
information on HIPAA & Access.

HTH,
TC