Please help: I need to copy a Field from one table to another

[rightclick]

I am setting up a database to be used to keep a point ranking system for my table tennis school.

I have a table tblMain with records for each player. It includes a field called StartOfSeasonRank. I have set this to a text datatype. No calculations are performed on this field. It only stores the ranking position of the player at the start of the season and is used in the reporting.

At the start of a new season I would like to reset the value in this field according to the player's position at the end of the previous season.

My solution to do this was to create another table tblNumbers with a single field containing a sequence of numbers. The tblMain is sorted in descending order of points so a simple copy of the field from tblNumbers to overwrite StartOfSeasonRank woks perfectly for my purpose. Is there a way to do this through a macro or query?

Alternatively, is there a way to set the value of StartOfSeasonRank to the actual record number of the record in tblMain. Since the records are sorted then the record number is in fact the ranking position of the player.

Thanks :O)

[Kevin Wilcox]

posted in error, removed

[Kevin Wilcox]

Hi

This sounds like a risky way of doing things! Sorting the table won't change the record numbers, just the order in which they are displayed. Much better to have an explicit link between the records in each table, e.g. include a playerID in tblNumbers, which corresponds to their recordID in tblMain. (Personally I'd also rename the recordID field to PlayerID, or vice versa, just to make it obvious, and link them in the relationships window). You can then use an update query to update tblMain from tblNumbers.

HTH
Kevin