AnsweredAssumed Answered

Carrying a field value across tables

Question asked by bryanade on Feb 1, 2017
Latest reply on Feb 16, 2017 by JackRodges

I'm creating a research database for a healthcare setting. We identify patients by their ID numbers. These patients have measurements taken before surgery and then several times after surgery. We also record things about their surgery.


Currently, I have a main table, which contains the patient's ID number, their year of birth and a few other things. Since they have measurements done several times, measurements are stored in a separate table (called Measurements), and their surgical things are stored in a third table called Surgery.


When a patient's data is entered, the first piece of entered data is the ID number, followed by the rest of the fields in the main table. There are two things I would like to do:


1) When Measurement data is entered, I would like the patient ID number to be automatically entered into the Measurement table to avoid having to re-type it. This is the primary linkage field. I have already established a one-to-many relationship between my main table and the Measurement table using the ID number as the linking field.


2) When surgical information is entered, I would like the patient ID number to be automatically entered, but I would also like a different layout depending on the operation that was performed (since the information items are slightly different).


I'm quite new at this, so apologies if these are really simple questions.