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



"ABZ123" wrote in message
oups.com...
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