Annual Database Updates

[jcrane]

I am a relatively inexperienced Access user. I set up database for a highschool football program and I want to start a new year. I am uncertain of whether I should simply add new freshman players into the former Seniors records or if there is a more sound way to manage the dbase

 

[CameronM]

As I assume you will want to keep the previous history so you should add all the new players into the database, but not replace the existing players. You can then have a flag 'Active' or a 'Year' field that you can use to determine the current players.

The year option may be useful as you could then do a report on which players played in a past year.

If you wanted to get more complex, you could add a couple of tables called 'Seasons' and 'SeasonPlayers' table that would link the ID fields of all the players for a given season, which would enable you to do some really great reports, like who played in 2007 and 2008!

Table [Seasons]
SeasonID, Name
1, 2008
2, 2009
3, 2010
 
Table [Players]
PlayerID, FirstName, LastName
1, Bob, Smith
2, Frank, Jones
 
Table [SeasonPlayers]
SeasonID, PlayerID
1, 1
1, 2
2, 1
3, 2

Using a simple select query you can then join the three tables to see who played in each season.

SELECT Seasons.SeasonName, Players.FirstName, Players.LastName
FROM (Seasons INNER JOIN SeasonPlayers ON Seasons.SeasonID = SeasonPlayers.SeasonID) INNER JOIN Players ON SeasonPlayers.PlayerID = Players.PlayerID;

The results of the query would be

2008,Bob,Smith
2008,Frank,Jones
2009,Bob,Smith
2010,Frank,Jones