MS Access Help

I've never really used access before, so I'm a bit of a noob with it, but I told my uncle I'd make a database for him. He's a doctor so he needs a database for his patients. One thing he wants to be able to keep track of is medicines they've been prescribed, dates they were prescribed on, and so forth. I don't really see any "list" object under access so I was a little unsure about how to go about doing that. My thought was to create another table for medicines and then set up a relationship between the medicine table and the patient table. Would that be the best way to go about doing that?

table patient
unsigned integer autoincrement patientid
varchar(40) namelast
varchar(40) namefirst
varchar(128) addressline1
varchar(128) addressline2
varchar(128) addressline3
unsigned integer guardianid

table medicine
unsigned integer autoincrement medicineid
varchar(128) medicinename

table prescription
unsigned integer autoincrement prescriptionid
unsigned integer patientid
unsigned integer medicineid
mediumtext doseage
date prescriptionstart
date prescriptionend

Just my suggestion.

Thanks. Yea, he sent me the requirements in a word document. Ahh, that's a genius idea -- splitting the medicine into a table for medicine and a table for prescription. Had not thought of that. Thanks.

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")