Table linking for Report

[AbeA]

We have a database that was being used with a psychiatrist office program, but we aren't using the program anymore so we've stripped the database. We want to create reports with it.

The database has three tables: Drugs, Prescriptions, and Patients.
Drugs has two columns: drug_id and drug_name
Patients has three columns: patient_id, first_name and last_name
finally, Prescriptions has several columns including patient_id and drug_id.

Basically, we want to have a complete report that shows which patient received which drug on which day.

I created a report using the Prescriptions table, in effect displaying the report I want, except it is only showing the drug/patient ID and NOT the actual name. (An annoying workaround is we can go to the Drugs/Patient tables and look up the ID to see which one it is, but it can become tedious)

Any way to have the actual patient name and drug name show up on the report instead of the ID #?

Thanks.

 

[boblarson]

Yes, you use a Query instead of the table for the recordsource of the report.

See here on how to change it from a table to a query:
http://www.btabdevelopment.com/main...dsourcefromtabletoquery/tabid/75/Default.aspx

 

[AbeA]

Thanks, bob. But, I'm still confused. I created the query, and now my report is showing the patient and drug name, however they do not match with the patient and drug ID fields. For instance, the first row shows a patient ID of 12 (lets say the correct name for patient ID #12 is John Smith - instead it shows Joe Blow.) Same thing with the Drug field - correct ID, incorrect drug name. Will I have to create a relationship or something for this to work?