Automatically enter data

[Guest]

I am creating a database for a road race that I am organising. As the runners
finish, we will enter their finishing time and I'd like to find a way of
automating the entry of their position in the race based on their time. In
general, the finishing times will be entered in time order, although
occasionally we may have to go back and amend/correct an entry, so the
ability to update/refresh the 'Position' field would be handy. Last year we
ended up exporting the data to Excel, sorting by time and auto-entering the
position, then re-importing the data back in to Access, but this meant we
could not really do anything until the last runner finished.
Any thoughts welcomed please?

 

[Allen Browne]

See:
How to Rank Records Within a Query
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946&Product=acc2000

 

[Allen Browne]

Not sure what's happening with your query, but I do not believe these
numbers should be stored in the table, even though you could use an Update
query to write a value back.

 

[Guest]

Thanks again. With some judicious shenanigans, it's now working. I do
actually need to be able to use the places results that are calculated by the
query in calculations down the line which is why storing them would be the
easiest way to do the next bit. Can I create an update query based on the the
original query? How can I refer to the calculated field?

 

[Allen Browne]

The subquery in the article above will be the most efficient approach.

Once you have the query working correctly, you can turn the query into an
Append query (Append on Query menu, in query design), so that the rankings
are stored in their field.