Database Design - simple solution for replicating records monthly

[Guest]

Hello,

I am not sure if there is a standard way of doing this:
I have a database with patient records on it. Each patient is on certain
level of medication, there are 20 categories. Most patients stay on the same
level month after month. Is there a way of generating the medication level
that they were on last month? For example, if patient was on level A in Jan
2007, how can I generate that they will also be on level A in Feb 2007? This
would save the data entry clerks a lot of work and they could only change
those levels that had changed since the previous month.

I hope this makes sense and thanks for your help in advance.

 

[Jeff Boyce]

Johanna

You've asked about database design, but not really provided a description of
your current design.

You've asked a "how do I" question about using a specific approach you seem
to have decided is the way to handle your situation ("generate the
medication level that they were on last month").

For instance, why do you need a medication level X month record per patient
(I suspect you've already determined that you do need this, but I'm asking
how having this lets you do something you need to accomplish).

I'm not asking out of curiosity, but because better understanding your
underlying requirements will help the newsgroup readers better advise you.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thank you for pointing out that I need to explain myself in more detail.

I have a database stores information for payment purposes. I need to know
how many patients are on each medication level each month, split by provider.
This allows me to calculate how much each provider needs to be paid. The
monthly payments depend on the number of patients and what level they are on.

Patient details are stored in PatientDetails table and whether patient has
been supplied medication on a particular month is stored in TreatmentRecords
table. There is also a LevelDetails table which has the medication levels. I
have a query that combines information from these queries. Both LevelDetails
and TreatmentRecords are linked to PatientDetails through PatID field. There
is also a field called 'MonthOfSupply' in TreatmentRecords which tells me
that patient is still in the scheme.

The data entry is getting very laborious with hundreds of patients each
month. Most of them stay on the same level. Is there a way of automatically
generating that the patients has stayed on the same level and then amending
those that have changed levels?

I would be very grateful for help.

 

[Jeff Boyce]

Johanna

You are there, much closer to your situation. I can offer an observation,
but you'll need to consider whether it fits.

If the objective is to be able to tell which patient (and how many) is on
which medication during which month, one approach might be to have one
record per patient X medication assignment. On that one record, you'd have
something like:

trelPatientMedication
PatientMedicationID
PatientID (foreign key from tblPatient)
MedicationID (foreign key from tblMedication)
StartDate
EndDate

In a design like this, each patient would get a "new" PatientMedication
Record ONLY when the medication changed. This design would also allow a
patient to be signed up for more than one medication at a time (but I have
no way of knowing if that matches your situation).

Regards

Jeff Boyce
Microsoft Office/Access MVP