Make Table with autonumber ID field for ranking

[fishy]

I have the following query:

SELECT tbl_times.[Staff Name], tbl_times.Date, tbl_times.Productivity INTO
tbl_Leaguestage1
FROM tbl_times
WHERE (((tbl_times.Date)=[Forms]![MainMenu]![txt_importdate]))
ORDER BY tbl_times.Productivity DESC;

I want tbl_Leaguestage1 to have an autonumber field so that I can assign a
ranking. I have seen various historic suggestions but couldn't get these to
work (maybe missing a function?).

I need the number sequence to be from 1-250 each time.

 

[Dale Fye]

An autonumber field is not what you want in this case.

1-250 based on what (productivity?) What happens if you have two staff
members with the same productivity (I know, this is probably unlikely)?

The way I would handle this is to:

1. create tbl_LeagueStage1 with the fields you want, including an extra
field (Ranking).
2. run a Delete query to delete all the records from the table
3. run an Append query to append the records to the table
4. run an Update query to update the ranking field, something like:

Update tbl_LeagueStage1
Set [Ranking] = DCOUNT("Staff Name", "tbl_LeagueStage1", _
"[Productivity] <= " & [Productivity])

Note that the second [Productivity] in the DCOUNT function is outside the
quotes. This will cause Access to interpret it as the value of
[Productivity] for the record being updated.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.