Hi! I am creating a database for a class. It is supposed manage the hospitals Drug inventory as well as the patient's information and what patient is on what drug. I created a drug table (with drug_id, num_in_stock, and num_prescribed) and a patient table (with patient_id, name.. blah blah blah) and a patient drugs table(linking drug_id and patient_id.. it also containg the columns quantity and times daily). the patient drugs table manages all the drugs each patient in the hospital is on as well as how many they are prescribed each day.
i want an error message to pop up when attempting to put a patient on a drug that they already are on. i am assuming i have to make a composite key (combining patient_id and drug_id within the patient drug table) .. i DO NOT KNOW HOW TO CREATE COMPOSITE KEYS and i DO NOT KNOW HOW TO SET RESTRICTIONs ON THEM... you can tell i'm frustrated with this.. LOL
also... i'm trying to keep an updated inventory of all the drugs. i'm having trouble decreasing the num_in_stock in my drugs table using an update query. does anyone have any information on how i can keep an updated inventory in access every time a patient is put on a drug?? am i making any sense?
-the lost and confused