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

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.

 

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.

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.

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).