Filled-In Fields From Related Records

[CBarnes]

I am trying to fill in two fields from related records but cannot figure the correct working way to do it.

In my main table (DwellTimes) I have a lot of imported data from an AVL system. One of the fields with data is Stop, which has all of the stop names. I created two fields, StopNumber and Timepoint (the imported data does not have these two fields). I created a table (StopNumbers_Names) that has all of the stop names and the associated stop numbers. I also created another table (Timepoints) that has a list of the stops that are timepoints. Not every stop is a timepoint, that is why I created two tables.

1. I would like the StopNumber field to be filled in from the StopNumbers_Names table. If a stop name equals a name in the StopNumbers_Name table, the Stop Number should be filled in. Example, in the DwellTimes table a stop named Lorene7th matches in the StopNumbers_Names to Lorene7th and has a stop number of 10000, I would like the 10000 to be placed in the StopNumber field of the DwellTimes table. I made the StopNumber field a calculation field with the following calculation with a result field of number (all stop numbers are 5 digits), but it does not enter the number in any of the records.

If (Stop = StopNumbers_Names::StopName ; StopNumbers_Names::StopNumber ; "" )

2. If the DwellTimes field Stop is in the Timepoints table, I would like "Yes" to be entered in the DwellTimes Timepoint field. I made the Timepoint field a calculation with the following calculation with a result field of text, but it returns "No" in all records.

If ( Stop = Timepoints::TimepointName ; "Yes" ; "No" )

Any help would be greatly appreciated.

Thanks,

Craig

[comment]

I am trying hard to follow your description, but I am not very successful. 

Why do you need to duplicate data at all? If you have two tables with a common field value, use this to define a relationship between them. Then just look directly at the data from the related record - no calculation is necessary for this.

In addition:

21 minutes ago, CBarnes said:

Not every stop is a timepoint, that is why I created two tables.

 That's not a good reason to create two tables. If not every stop is a timepoint, then you simply have two kinds of stops. It would be different if one stop had many time points, or vice versa.

 

[CBarnes]

My apologies for not being very clear.

Below is the data that is imported. It does not have the Stop Number or whether it is a timepoint or not. My goal is to have the Stop Number and whether it is a timepoint or not to be displayed also. In each record the stop may be different.

Below is how I have things related.

[Wim Decorte]

I don't think you need two tables for the stops.  One table would do.  The 'timepoint' or 'stop' is just an attribute of a stop.  That way you need only to make relationship to the one stop names table and read whether it is a stop or a timepoint.

[comment]

I am afraid this is not getting any clearer. I suspect your relationships are not set up correctly. Going by your original description, I would expect a relationship based on matching:

DwellTimes::Stop = StopNumbers_Names::StopName

You show a relationship based on matching primary and foreign keys - but you don't say how you populated them (if at all).