Solved Changing table relationship - can I retrospectively fill values from primary table to related table? (1 Viewer)

HGCanada

Registered User.
Local time
Today, 09:53
Joined
Dec 30, 2016
Messages
82
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.
 
Last edited:

June7

AWF VIP
Local time
Today, 05:53
Joined
Mar 9, 2014
Messages
5,423
To update dependent records with autonumber and/or ScreenID, the tables already have to have a relationship. Join tables in a query then switch to UPDATE in query builder and identify which field in dependent table to update with which field from primary table. Do this for each dependent table.
 

HGCanada

Registered User.
Local time
Today, 09:53
Joined
Dec 30, 2016
Messages
82
Thank you, June7. Much appreciated. OK, I've figured out how to do the update query. A couple of followup questions.

1) Does this sound like a reasonable plan?
  • Create a single query that updates AutonumberID and ScreenID in each of the 3 tables - run them to update the values. (In the query, the tables are still linked by StudyID, since those are still unique values at this time).
  • After the queries have been completed to update the missing values, change my table relationships to link by ScreenID, so from now on, ScreenID should populate in each of my 3 related tables, any time I add a new record.
  • Go back and correct the StudyIDs in my main table such that some will now have an NA value. I guess I can then also create an update query to update the StudyID to the 3 related tables.
2) Moving forward, when I create new records in the main table, how do I make both AutonumberID and StudyID populate automatically in the 3 related tables? I think only ScreenID will update automatically in the related tables, since that is variable that relates the tables.

Thanks.
 

June7

AWF VIP
Local time
Today, 05:53
Joined
Mar 9, 2014
Messages
5,423
You should not save all 3 values to dependent table. Save only the linking ID value (probably should use autonumber). Retrieve related data in query that joins on primary and foreign key fields. Build form/subform arrangement so key value will automatically save to dependent record.
 

HGCanada

Registered User.
Local time
Today, 09:53
Joined
Dec 30, 2016
Messages
82
Thanks June. So, to rephrase that in terms I understand:
- AutoNumberID should be in all of my tables (primary key in my primary table, and foreign key in my 3 related tables) - I fixed the database relationships to reflect this.
- I fixed my sub-forms so that master and child fields link by AutoNumberID.
- ScreenID and StudyID should only be in the primary table, and not in the 3 related tables at all, so I shouldn't concern myself with updating those in the related tables.

For ease of viewing, I like to have all 3 IDs in all tables, but I guess this is poor practice, because if anyone accidentally modified one of these values in one of the tables, the referential integrity will have been compromised.

Is my understanding correct?

Thanks.
 

Users who are viewing this thread

Top Bottom