A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

HELP.. me please



 
 
Thread Tools Display Modes
  #1  
Old December 6th, 2005, 02:05 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

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

  #2  
Old December 6th, 2005, 03:06 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

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


  #3  
Old December 6th, 2005, 03:42 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

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.

  #4  
Old December 7th, 2005, 01:59 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

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,MedicalPracti ceName,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

  #5  
Old December 7th, 2005, 02:09 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

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.

--
Regards

Jeff Boyce
Office/Access MVP

"ABZ123" wrote in message
ups.com...
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,MedicalPracti ceName,MedicalPracticeAddr
ess,
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


  #6  
Old December 7th, 2005, 05:18 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

"ABZ123" wrote in
ups.com:

tblPatient(PatientName,PateintAddress, PatientTel, DoctorID)


tblDoctorDetails(DoctorID, DoctorName, MedicalPracticeID),


tblMedicalPractice(MedicalPracticeID,MedicalPracti ceName,
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





  #7  
Old December 7th, 2005, 05:27 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

Hi,

Thanks that worked like a treat. ))

I have 2 more Q.

1. I have created an Order Form and I can now select a Patient from the
Combo Box (PatientID). I have also created extra Text boxes for the
relevant field (Address, Tel etc). How do I populate these text boxes
from the Combo Selection.

2. In the Northwind DB, I looked at the way the Order form runs. It has
a way of selecting multiple items and adds it to list. How can I do
this on my DB to add prescription items? I want to be able to select a
product from a drop down menu and enter a re-order date. This info
should update the OrderDetails table with each product.

Many thanks.

  #8  
Old December 8th, 2005, 09:32 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

Hi Tim,

You have made some very interesting points.

To give you a bit more of an insite, this DB is for about 400 - 600
patients who are on a long term treatment. The idea is to make their
life a little bit more comfortable by them not having to remember about
when to re-oder their medication. 95% of the time the treatment does
not change and 99.9% of the time the Practice that the patient goes to
does not change. Usually, the doctors too remain the same.

So the above design would have been suitable. However, I think your
suggestions are far better and will enable me to develope the DB
further in the future if there is a need.

So what do you think of the following:

Patients - Orders - OrderDetails - Products
v v
| |

Practices -------------- Doctors

  #9  
Old December 8th, 2005, 02:33 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

At this point, I'll recommend that you post new questions to the respective
newsgroups. This way, more readers will have the opportunity to respond,
and you'll get more focused responses.

For example, questions about filling textbox controls based on a selection
in a combo box are common in the forms and formscoding newsgroups. You may
even find an answer there without having to post.

--
Regards

Jeff Boyce
Office/Access MVP

"ABZ123" wrote in message
oups.com...
Hi,

Thanks that worked like a treat. ))

I have 2 more Q.

1. I have created an Order Form and I can now select a Patient from the
Combo Box (PatientID). I have also created extra Text boxes for the
relevant field (Address, Tel etc). How do I populate these text boxes
from the Combo Selection.

2. In the Northwind DB, I looked at the way the Order form runs. It has
a way of selecting multiple items and adds it to list. How can I do
this on my DB to add prescription items? I want to be able to select a
product from a drop down menu and enter a re-order date. This info
should update the OrderDetails table with each product.

Many thanks.


  #10  
Old December 8th, 2005, 09:58 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default HELP.. me please

"ABZ123" wrote in
oups.com:

95% of the time the treatment does
not change and 99.9% of the time the Practice that the patient goes to
does not change. Usually, the doctors too remain the same.


Only over the short term. Over the several years, practically all the
doctors will change; and over the course of decades most of the patients
will have changed practice. It all depends on your viewpoint and how long
you expect your app to run. Bear in mind that about a year ago I had to
upgrade an Access app that I created on version 1.1, and it had not been
changed in the meantime!


Patients - Orders - OrderDetails - Products
v v
| |
Practices -------------- Doctors


IMO, this seems a bit safer, and will give you a more reliable way of
finding the Dr responsible for a script. Thinking about it though, in an
Order corresponds to a prescription, and an OrderDetail corresponds to
items on a script, then presumably a whole Order will relate to one Dr,
so perhaps the FK belongs there.

For example, Brenda goes to Dr Allen for FP, Dr Bardsley runs the
practice asthma clinic, and Dr Charles does the diabetics. She therefore
has three Orders, consisting of (Microgynon), (Ventolin, Becotide), and
(Actrapid, Mixtard, Syringes and needles, BM Stix), with three different
doctors. Yes, I know that most patients will have one Order with one
Detail, but it's not the 99% that sinks databases, it's the 1%.

All the best


Tim F


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.