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?

thanks!!

-the lost and confused

You have a drug table that describes the drugs, and patient table that describes the patients, but you do not have a table that contains drugs that has been prescribed to each patient. you will probably need a prescripton table
There would be multiple rows in this table for any given patient. Here is where you would use composite key -- patient_id and drug_id would be the unique key.
Then its a simple lookup to see if a patient has already been prescribed a given drug.

>>does anyone have any information on how i can keep an updated inventory in access every time a patient is put on a drug
Shouldn't the drug table be updated when a prescription is filled ? Doctor writes a prescription with three refills. The drug table should be updated each time a refill is made, not when the prescription is first made.

I don't know what programming language you are using, but it will first have to query to find out the current quantity, reduce it by some amout, then update the table with the new value.