You shouldn't need to duplicate any data. Instead, use two tables, Patients and Assessments, related by PatientID. Enter each assesment as an individual record, with the year being one of the fields in the Assessments table.
Thanks thats very helpful to get me on track.
Each condition will have a different set of assessments so I will need a table for assessment of each condition. Patients will only have 1 condition.
Please can you give me a bit more detail on how to construct the relationship PatientID , or point me at some advice.
Sorry, probably not being too helpful about what I want.
Take 100 patients, 5 conditions , equal distribution of a condition so 20 in each group.
I want a list of the 100 patients with address etc. I will then need a separate asessement table for each condition.Each table will have 20 records ( 1 for each patient per year).
I want to jump from the initial opening patient demographics table to the assessment table for the particular condition the patient has ( eg using a button) but when i do I need to have the patient demograhics on the asessment table , - not sure how to use the PatientID to do that and still separate them into 5 groups.
Each condition will have a different set of assessments so I will need a table for assessment of each condition.
I am not sure that's necessary - but I would need to know more details about the various assessment types. One way to handle such situation is to assign a "label" to each value e.g.:
Another option is to have dedicated fields for all types of assessments, but fill out only the relevant ones.
Please can you give me a bit more detail on how to construct the relationship PatientID .
In the Patients table, define a PatientID field as type Number, with auto-entered serial number.
In the Assessments table, define a PatientID field as type Number.
In the relationships graph, link the two tables so that:
Patients:: PatientID = Assessments:: PatientID
If you like, you can set the relationship to automatically create related records on the Assessments side - this will allow you to enter data directly into a portal to Assessments, placed on a layout of Patients.
I need to have the patient demograhics on the asessment table
Once you have established the relationship, you can place related fields from Patients on a layout of Assessments to display information from the parent record in Patients.
I am probaly more of a newbie than I think.
I have done all that and I can't get the patient demographic data to populate the assessments fields. I can get the patientID to create a new record in the asessments table ( by layout setup - show records from patient table ) but not to pull the lastname , firstname etc fields through too. How do I do that ?.
I have set the relationships for all these in manage database and edit relationship dialogues.
II can't get the patient demographic data to populate the assessments fields.
There shouldn't be any fields for patient data in the Assessments table, so it's not necessary to populate them. Simply place fields from the Patients table on the Assessments layout.
Sorry , I don't know what you mean by 'place' fields from the Patients table on the Assessments layout ?.
Go to the Assessments layout;
Enter Layout mode;
Select Insert > Field…
In the 'Specify Field' dialog, select Patients from the drop-down menu;
Choose a field and click OK;
Repeat for more fields.