I have a database with 1 primary table containing records for hundreds of individuals, all of whom are identified by 3 unique IDs (they are complete for all records - no blanks or repeats so far):
1) ScreenID is a unique ID I made up for each person, and will remain this way
2) StudyID is currently set up as a unique value for each person (it started out as the identical value to ScreenID, but some were later updated as people were added to the study and re-assigned a new ID). I need to re-code StudyID to give some individuals the same N/A value for that variable (since some of them technically are not in the study)
3) AutonumberID is automatically generated.
There are 3 other tables with repeated records for some of the individuals, so I set up one-to-many relationships to the primary table linked by StudyID, and also queries and reports are based on StudyID, and this value is automatically populated in my related tables, whenever I create a new record in the primary table.
So of course, I now realize that I should have linked all my tables and set up my queries and reports based on ScreenID or AutonumberID instead, since those are the variables that will always have unique and unchanging values for each person. However, when I try to change the relationship to link all my tables by ScreenID - well, I already have hundreds of records in my primary and related tables, and although ScreenID st a variable in those tables, it has never been populated in these tables because it was not the value upon which the relationship was linked. And AutonumberID was never added as a variable in the 3 related tables.
So I have 2 questions:
1) Is there a way for me to read in at least the ScreenID from the primary table, into the related tables retrospectively? (it would be pretty quick to copy and paste the entire column from StudyID, and then manually fix the 30 or so that changed, but that feels risky)
2) Is there a way to automatically populate both AutonumberID and ScreenID retrospectively, from my primary table into my 3 related tables, and have them automatically populate in the related tables whenever a new record is added to the primary table moving forward?
I hope that makes sense. I'm hoping for a simple solution, given my low-intermediate level of experience. Thanks in advance.
1) ScreenID is a unique ID I made up for each person, and will remain this way
2) StudyID is currently set up as a unique value for each person (it started out as the identical value to ScreenID, but some were later updated as people were added to the study and re-assigned a new ID). I need to re-code StudyID to give some individuals the same N/A value for that variable (since some of them technically are not in the study)
3) AutonumberID is automatically generated.
There are 3 other tables with repeated records for some of the individuals, so I set up one-to-many relationships to the primary table linked by StudyID, and also queries and reports are based on StudyID, and this value is automatically populated in my related tables, whenever I create a new record in the primary table.
So of course, I now realize that I should have linked all my tables and set up my queries and reports based on ScreenID or AutonumberID instead, since those are the variables that will always have unique and unchanging values for each person. However, when I try to change the relationship to link all my tables by ScreenID - well, I already have hundreds of records in my primary and related tables, and although ScreenID st a variable in those tables, it has never been populated in these tables because it was not the value upon which the relationship was linked. And AutonumberID was never added as a variable in the 3 related tables.
So I have 2 questions:
1) Is there a way for me to read in at least the ScreenID from the primary table, into the related tables retrospectively? (it would be pretty quick to copy and paste the entire column from StudyID, and then manually fix the 30 or so that changed, but that feels risky)
2) Is there a way to automatically populate both AutonumberID and ScreenID retrospectively, from my primary table into my 3 related tables, and have them automatically populate in the related tables whenever a new record is added to the primary table moving forward?
I hope that makes sense. I'm hoping for a simple solution, given my low-intermediate level of experience. Thanks in advance.
Last edited: