I have two tables "COACHES" and "GAMES"

In "COACHES" I have the following fields

Name, Year_Start, Year_End

In "GAMES" I have numerous fields including

Game, Coach, Year_Season

When I create a new record in "GAMES" I want the coach field to be calculated based on the season. In other words, lookup the name based on comparing the Year_Season with the Year_Start and Year_Finish fields. Hopefully I'll be able to pull a summary of games won and lost back into the "COACHES" table.

Thanks in advance!

Just give me one reason for not referencing instead of looking up or calculated data ... you are missing a join table, from where the reporting should be done!

--sd

IIUC, there is one coach to many games - so I see no need for a join table. However, I believe a coach may be replaced in mid-season, so the 'real' relationship should be based on CoachID (not name!). The relationship by season can be used to lookup the CoachID as a suggestion only.

I think we need more information about the "Year_Season" field. If all three fields (Year_Start, Year_End and Year_Season) are plain year numbers, the relationship can be by range:

COACHES::Year_Start ≤ GAMES::Year_Season

AND

COACHES::Year_End ≥ GAMES::Year_Season

Thanks for your response. The season is simply a year (number). e.g. The games started in 1998 and ended in 1999. Therefore the season is 1998.

I can't figure out how to make my "Games" table pull in the coach's name from my "Coaches" table based on the season. For example, if I enter information about a specific game that occured in "Season" field = 1972, I want the "Coach" field to be automatically filled in. The tricky part is some coaches left and came back. e.g. coached from 1982-1987 and again from 1992-1995. I think this is why you are suggesting using coachID as the relationship link. I just can't figure out how to make a conditional relationship link.

See if the attached makes sense. Note that it's a simplification - a smarter implementation would have separate tables for Coaches and for their 'terms in office'.

The reason to use CoachID instead of name is that you only want to enter a name once. You also want to be able to correct the name without breaking existing relationship. And it seems that most coaches are named "Coach" anyway.

Coaches.fp7.zip

FANTASTIC!!!

That is exactly what I needed. It took some trial and error on my part to figure out the details of what was going on but I finally got it. I would only warn others that using a lookup does not update automatically when a change is made to the original reference table. I had to create a simple script and then made a button to execute the script.

The script is:

Show All Records

Relookup Field Contents [No dialog; Current Layout::Source Field]