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
patient_id
drug_id
quantity
etc
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.
Unsolved 
thanks!! 
Offline 
