MS Access: Update data from one table to another based on a choice [closed]

Question

Help! I have a patient form where their referring doctor is chosen and that ID for the doctor is stored in a variable. When I click "Save record" I need it to copy the data form the ReferringDoctors table (name, address, etc) and copy it to the PatientDetails table in corresponding variables based on which doctor has been chosen. I can't figure it out. Help!

Using Access 2013

Re-edit: The whole idea here it that I have three separate tables - 1 for the DoctorInfo (just address data), 1 for the PatientInfo (just address data), and 1 for the PatientVisit info (why they came that day). The goal is to be able to click a button on the PatientVisit form to generate a letter in Word that includes the address from the doctor, the patient's address and the information from the patient visit.

So far, I have all the information I need stored in the three tables, but I have no clue how to grab the information to generate the letter. For example if Patient 1 is referred by Doctor A on the current visit, how do I generate a letter that has that information? I assumed I would need to copy the info from one table to another to eventually pass it to Word for it to use to generate the letter. Totally wrong?

Answer 1

Not sure what you want to achieve so let's start with saying that if you already have a table with the doctor's details, you shouldn't add them to the patientdetails table. If you need to display the data somewhere you can use joins in your query.

Thoughts:

1. Never duplicate data (unless you have to, these are only special cases)
2. You can always query data if they are linked (let's say you add the referring doctors id to this table)

example query

select * from PatientDetails join ReferringDoctors on PatientDetails.ReferringDoctorsID=ReferringDoctors.ID

Please provide more info, your current table structure, why you want to copy the info etc.