AnsweredAssumed Answered

Getting current data from another table

Question asked by BayArea123 on Jan 9, 2010
Latest reply on Jan 13, 2010 by BayArea123

Title

Getting current data from another table

Post

Hi there,

 

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)

Variety

SowingDate

AmountSown

Germinated

%Germinated

SourceID

 

tblSource  (filled out when new seeds arrive)

VarietyID

SourceID

DateStarted

DateEnded

 

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)

Variety

Sow#

Transplant#

Researcher

 

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.

 

thanks again

Outcomes