Nigel has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Access VBA course
Numbering records based on their relative values
My form, and the table or query it is based on, sorts the data according to the criteria I specify. Once it is sorted, I want to designate a position to each record, 1 (highest), 2 (next highest) and so on, like a league table. This number will then be referred to in subsequent calculations.
RE: numbering records based on their relative values
Hi Nigel,
Sorry for the delay in getting back to you.
It sounds like you do not order the table based on the number you want to insert into the table.
If this is the case I would work out which number (position) you want to allocate to the team in the VBA code, and then just store it within the code an array, then you will be able to so all you calculations.
If you do want to actually insert into the database, then work out with VBA code which position goes with which row, then for each row insert the number (position) into the database. If you want to do it this way remember that this will not be able to act as the primary key.
Hope this helps
David
RE: numbering records based on their relative values
Hi David ( I am replying to you and not to me I hope),
You are correct that I don't order the table with the number, because the order is based on other values that are entered in the table for each occasion. The number that I want to allocate to each row is then based on the order that has been established.
I do want to insert the number into the database once it has been worked out. You suggest that I work it out using VBA code, but what code? I haven't done the VBA course yet, so don't really have a clue where to start, although if I had the code, I'd be confident I could get it onto my form.
By way of further explanation, what I need for my form I can produce on a report with the Running Sum function, but that is only available on reports apparently.
Thanks in anticipation.
Nigel
RE: numbering records based on their relative values
Hi Nigel
My suggestion would be wait until you have done the VBA course, and then basically you can would everything out with some code and then insert the number into the database. I think it would be best to wait until you have completed the course then once you have and understanding of VBA the solution may become clear, otherwise get back to me and I will go through some code with you after the VBA course.
Sorry I could not come up with an easy solution for you here and now, but hopefully once you sit the Access VBA course all will become clear.
David
