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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|