HELP.. me please

[ABZ123]

I am trying to create a DB for a pharmacy which is for repeat
prescriptions only. A patient may come with several items on a repeat
prescription. However, due to the quantity, the items may have
different repeat order dates. e.g. he may have Paracetamol to last for
3 months and Ventuline to last for 6 months and hence the pharmacy will
need to order them from the doctor on differnt dates.

Here are the tables that I think I need so far.

tblPatients: PaitentID,PatientName, Surname, Address, Tel,
[DoctorID]*slecetable from a list of Doctors
tblDoctors: DoctorID, DocName, Surname, SurgeryName, Address, Tel
tbleMedicine: MedicineID, MedName

Now the confusing bit:
I need to create a Form and a Table to select a patient (filling out
all his/ her details automatically) and to be able to add multiple
items selected from the medicine table with re-order dates for each
item.

I then need to be able to run a Query/ Report to give me a list of all
the patents that have items to be re-ordered for the current day. The
report should also include the items and the name of the surgery.

HELP ME... (

I hope I haven't confused you... cos I know I have confised myself! lol

 

[Jeff Boyce]

Before you proceed, are you satisfied that you have designed a system that
complies with any local/state/province/national healthcare regulations? In
some countries, maintaining the kind of database you are describing is
highly regulated.

I don't understand how [SurgeryName] is considered a characteristic of a
Doctor... Are you saying that in your situation, any given doctor performs
only one type of Surgery? (your table structure implies that a doctor has
only one name, surname, address, telephone number, ...)

Since each patient could have zero, one or many prescriptions, and each
medicine could be used by zero, one or many patients, you are quite right.
You need a junction/relation/resolver table to handle the many-to-many
relationship between these two.

It might include:

trelPrescription
PrescriptionID
DoctorID (the prescriber)
PrescriptionDate (the date prescribed)
PatientID (the patient)
MedicineID ...
Quantity (how many)
Instructions (e.g., twice daily, with food)
RenewCount (how many times can the prescription be renewed)

and then you'd also need another table that shows refills:

trelRefills
RefillID
PrescriptionID
DateRefilled

But if the quantity could change, or a generic equivalent were substituted,
you would probably need to go back to your prescription table and change its
design to handle both original prescription and refills, rather than using a
Refills table.

NOTE: I am neither a doctor nor a lawyer nor a healthcare regulator -- you
may need/want to check with these before proceeding.

--
Regards

Jeff Boyce
<Office/Access MVP>

I am trying to create a DB for a pharmacy which is for repeat
prescriptions only. A patient may come with several items on a repeat
prescription. However, due to the quantity, the items may have
different repeat order dates. e.g. he may have Paracetamol to last for
3 months and Ventuline to last for 6 months and hence the pharmacy will
need to order them from the doctor on differnt dates.

Here are the tables that I think I need so far.

tblPatients: PaitentID,PatientName, Surname, Address, Tel,
[DoctorID]*slecetable from a list of Doctors
tblDoctors: DoctorID, DocName, Surname, SurgeryName, Address, Tel
tbleMedicine: MedicineID, MedName

Now the confusing bit:
I need to create a Form and a Table to select a patient (filling out
all his/ her details automatically) and to be able to add multiple
items selected from the medicine table with re-order dates for each
item.

I then need to be able to run a Query/ Report to give me a list of all
the patents that have items to be re-ordered for the current day. The
report should also include the items and the name of the surgery.

HELP ME... (

I hope I haven't confused you... cos I know I have confised myself! lol

 

[ABZ123]

Hi Jeff,

Thanks for your prompt response... I was well amazed)

Let me clarify your questions.

Surgery Name is the name of the Medical Practice e.g. Hillsgrove
Medical Surgery (Like Compnay name). The Surgery may have many doctors
and hence it might be better to break down the table into 2: tblDoctor
: DoctorID, DoctorFirstName, DoctorSurname and tblPractice :
PracticeID, PracticeName, PracticeAddress etc (I used Surgery rather
than Practice above). But I did not see the need to break it down for
just the doctors name.

This DB is directed to only the pateints that have a long medical
history and have repeated prescriptions. As most of these patients will
have their medicine delivered to them, the pharmacy provide a service
to re-order the prescriptions as needed. The aim of the DB is to be
able to run a daily report to show which patients require medicine to
be re-order from the doctor on that day and from which surgery. The
pharmacy will then order the prescriptions as needed.

e.g

Person A has 5 items, each having a differnt re-order date. Item 1 has
to be re-ordered today and is requested by the pharmacist. When this
prescription arrives at the pharmacy, the record will be updated with
the new re-order date (depending on the quantity prescribed).This will
happen for each and in some cases there may be a need to add/ remove
items as the treatment may change. So in effect, the patients don;t
have to worry about going to the surgery each time and having to
remember when to re-order as all this is done for them.

So the obove tables are not needed as all that info is on the standard
system already

I hope this explains your questions.

Many thanks.

PS thanks for the advise about regulations. I will get this verified
from the pharmacy.

 

[ABZ123]

Hi,

It would seem that they already have the a DB and are within the Data
Protection Act and relevant regulations.

I have amended my tables as follows:

tblPatient(PAtientName,PateintAddress, PatientTel, DoctorID)
tblDoctorDetails(DoctorID, DoctorName, MedicalPracticeID),
tblMedicalPractice(MedicalPracticeID,MedicalPracticeName,MedicalPracticeAddress,
MedicalPracticeTel),
tblProduct(ProductID, ProductName)
tblOrderDetails(ProductID, ReorderDate,OrderID),
tblOrder(OrderID, PatientID)

I have created a Form for Adding Doctor records. I have got a Combo Box
to select the Surgery the doctor works at but it only shows the
PracticeID no and not the name. How can I set this to show the name but
store the unique PracticeID?

Your help is much appreciated. PS I am a beginier in DB.

Many Thanks

 

[Jeff Boyce]

A combo box on a form can store one column and display another. In the
properties of the combo box, setting the first column to zero-width hides it
from view, allowing the next (non-zero-width) column to be displayed. If
the first column in your combo box's source is the PracticeID and the second
is the PracticeName, you would get what you described by using something
like:

0; 1

for the widths. Access will adjust to reflect your local unit of measure.

 

[Tim Ferguson]

tblPatient(PatientName,PateintAddress, PatientTel, DoctorID)

tblDoctorDetails(DoctorID, DoctorName, MedicalPracticeID),

tblMedicalPractice(MedicalPracticeID,MedicalPracticeName,
MedicalPracticeAddress, MedicalPracticeTel),

tblProduct(ProductID, ProductName)

tblOrderDetails(ProductID, ReorderDate,OrderID),

tblOrder(OrderID, PatientID)

In other words, if I have understood this correctly:


Patients -< Orders -< OrderDetails >- Products
|
^
Doctors >- Practices



There are a couple of things I would worry about. The modern NHS does not
work nearly as simply as Bevan would have liked. The reality is that
patients see more than one Doctor at a time: Amanda may well come with
her contraception script signed by Dr Brenda, her insulin from Dr
Charles, and her Ventolin by Dr Devlin. I would suggest removing the
DoctorID attribute from Patients and moving it to OrderDetails, which is
the place where the link is made between Patients and their drugs.

An alternative would be to place a FK to the Practices table in the
Patient record rather than an individual GP.

Another problem is going to be that doctors are more mobile than their
patients. When you contact Edna's doctor Dr Finlay after a long period of
antihypertensives, you may find that the poor man died some years ago. Do
you have links to your local LMC to update the GP lists?

Just a couple of thoughts

Tim F