MS Access Help

Well, that way if a medicine gets recalled or something, you can write a query something like

SELECT patient.* FROM patient LEFT JOIN prescription ON patient.patientid=prescription.patientid WHERE medicineid=15

to find all the patients that have used it. You could write a query based on text, too, but misspelling would be an issue, then. This way, you have them pick from a drop-down, so you don't have that issue. Also, searching for a number in a database is faster than searching for text. If a field will have a lot of repeated data and you may need to search on it, it's a good idea to make a table to associate it with a numerical index.

In case you hadn't figured it out, with the structure I proposed, each prescription entry has the ID of the person it was perscribed for, then you can use a query such as

SELECT prescription.* FROM prescription LEFT JOIN patient ON prescription.patientid=patient.patientid WHERE (patient.namelast="last")AND(patient.namefirst="first")