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.