I apologize if my subject line does not clearly explain what I am trying to do.

2 tables: teams and games

teams

------------

*team_id, team_name

T0001, TEAM 1

T0002, TEAM 2

games

------------

*home_tm_id, home_tm_name, *away_tm_id, away_tm_name

T0001, , T0002,

I am trying to calculate each by the specific team_id being entered in the home and away field ids.

So when you select a team as a home team (which is linked to teams by team_id) it will know to populate the home_tm_name with the team_name that corresponds to the team_id used in home_team_id.

As I am pulling in two team_ids I have been unsuccessful and calculating these fields to recognize each one separately.

I am not sure you will even want the home_tm_name and away_tm_name fields. If you need it for some type of historical ( for exmaple, if you plan on changing the team name in the team table and want to know what it was at that point in time when the game happened ) then you can use lookups.

If not, then you can just put the related team name on the game layout.

You should have two relationships.

game::home_tm_id >-- TeamID ( let's call this table occurrence HomeTeam )

game::away_tm_id >-- TeamID let's call this table occurrence AwayTeam )