Resetting records within a database

[bhunecke]

I've got a database I put together that we use to keep records for a youth sports organization. At the end of the season there are two tasks I am trying to figure out if I can automate.
1. Clearing selected pieces of information within each record. For example the team a player is assigned to will change from year to year, but his or her address will not. Is there a way to selectively clear fields that do change.
2. Also, each year the children are another year older. Is there a way to automatically increase the values of certain fields by a predetermined value, say add one to each?

Any help is appreciated.

bjh

 

[Norie]

I think you should probably think about restructuring your database.

You should have a seperate table containing player information, including a unique ID, say an AutoNumber.

And you should have a seperate table listing team names (and any other team info eg sport), again with a unique ID.

Then create a table called say Teams with two fields PlayerID and TeamID.

In design view for the teams table use the Lookup up tabs for these fields to the names from the other two tables.

 

[Andrew Fergus]

I agree with Norie that a re-organisation may be in order. Regarding the players ages, have you considered storing their date of birth rather than their age? Then the age can be determined via a query with something like this :
DateDiff("yyyy", [DOB], Date()) - IIf(Month(Date()) < Month([DOB]), 1, IIf(Month(Date()) = Month([DOB]) And Day(Date()) < Day([DOB]), 1, 0))
HTH, Andrew.