Find latest occurrence

[Randy Bricker]

I have created a related database for my Strat-o-matic baseball league. I want to track a player's movement over his entire career as he moves from team to team by way of the  player being originally drafted into the league, traded, cut, drafted again, etc.

I have created one table that is the master Player Tracker, which has portals to the following tables:  Rookie Draft, Trades, April Supplemental Draft, Mid-season Supplemental Draft, Players Cut. All of these tables have a "date" field that records the occurrence, and a "team" field that records the team. There are multiple times a player changes teams.

I'm trying to write a formula for the Master Player table that will search these related databases and tell me what team the player is currently on (the occurrence that happened most recently) and then enter the current team in the Master Player table "team" field.

Any help would be appreciated.

Thanks.

[comment]

I am not sure you need that many tables. I would suggest you try and do with two tables only: Players and Drafts (or Transfers?).  This way all movements are in the same table, and the team of the last related record is the player's current team. There is no need to search multiple tables, and there is no need to duplicate the information into the Players table.

You might want to add a table for Teams, though.

 

[Randy Bricker]

Thanks for the response. I will give that some thought. It would be a lot of reconfiguring, but you might be right. Right now in the Players table I have one layout for individual players (form view) and one for teams (list view).

[Randy Bricker]

OK. I did as you suggested. I now have two tables, one for players that has a portal to the other table transactions. I have a field in players called "Team". I want to write a calc that finds the last "transaction" for the player and places the new team name in the "Team" field. All of the transactions have a date associated with them. While I am able to find the latest date (using the Max function) I have not figured out how to place the correct team in the "Team" field. Thank you ahead of time for your help.

[comment]

There are several ways to accomplish this, each with their pros and cons.

If you only want to display data from the last transaction, add a one-row portal to the Transaction table to your layout and sort it in reverse chronological (or reverse creation, if records are created in chronological order) order. Then place the fields you want to see (date, team name) in this portal. 

If you need the data for more than just display - e.g. for further calculations or to perform a find - you can use the Last() function to retrieve data from the last related record. However, this will not work for you if the relationship is defined to have a different sort order, or if records are not created in chronological order. In such case, more elaborate methods may be required.