Getting current data from another table
I have 2 tables in a seed germination database, so far, that I want to create a layout with to get current data from a certain field. Tables are as follows
tblSow (filled out weekly)
tblSource (filled out when new seeds arrive)
The tables are linked by VarietyID, even though it is text, each variety is unique. The source ID is the source number of the seed lot, and only changes from time to time. What I want to have happen is have the SourceID field in tblSow automatically populate with the current SourceID for each variety, every week when germination counts are taken, based on the SowingDate. When the SourceID gets updated in tblSource (new batch of seeds comes in), with a new start date, then that will be the SourceID that will take over for that variety, if that makes sense. Should I restructure my tables so there isn't repeating fields? Any help would be appreciated. Thank you
Oh, also, I have one other table that is a stand alone table:
tblInventory (current needs of all seed)
This is updated when new projects come along, and shows the current needs for all varieties of seeds in-house. So far it is a stand alone table, but is there a reason to link it to the other tables? I am trying to figure out a use.