How can I ad a Score based on Ranking Position? (1 Viewer)

Hello,
I'm new to access and having trouble adding a score value to a row, based on ranking position, after a sort.
I'm wondering if it's possible with access and if so, if anyone can tell me how to do it?
The ranking information will be manually input and the score values will be constant.
I need to have the input look like the following:

ID # Empl Name Tasks Complete Ranking Score Ranking Position
000 Name 20 200 1
000 Name 18 180 2
000 Name 16 160 3
000 Name 15 140 4
000 Name 13 120 5
... and So on

Can anyone tell me how to accomplish this with access?
Any help you can give me will be greatly appreciated!
Thanks,
Ty

There is no real reason to store your score and ranking on the table. You only really need to store the ranking, and depending how that is arrived at it may not even be necessary to store that.

Let assume however you do store the ranking on your table. If the score is fixed and will never vary, you can simply determine the score at the point that it needs to be displayed, using an if then statement, in either your form or report.

If however your scoring is not fixed you could have a separate table to store your ranking scores for various criteria, and then simply collect the appropriate score using a query when you display the data.

The score sounds very much like a calculated value, if you do a search on this subject you will find plenty of discussion about why it is not a good idea to store calculated values or see this page. You might also gain from reading up on normalisation

Hello John,
The number scores are not actually calculated fields but rather they are assigned number values assigned to each ranking position. This information is updated weekly and is based on the performance of specific tasks, such as items sold, reports generated, items sent and so on.
Employees are ranked according to the number of tasks completed in each category.
The number of each task is entered and the field is sorted. I then need the system to assign a ranking score, based on position. In the example, everytime a person was ranked in the first position, they would receive a score of 200 or 200 points. The scores from each category will be added together to achieve an overall performance score.
The system will sort each field. But I haven't been able to assign the score value for each employee and update their information accordingly.
Each week bonuses are paid according to overall performance scores.
I have loked up queries to deal with ties.
I also know that there is a way to assign ranking numbers.
I was wondering if it would be possible to do an if / then query for each row after the sort. Perhaps something like "if position 1 then, score 200?"
Is this possible? If so what would be the formula? If not does anyone know a better way?
Like I said in the first post, I'm very new to access and not a professional programmer. So any help anyone can give me to resolve this problem will be greatly appreciated.
Thanks

Check out the attached DB, for an explanation of what I've done check this link

John Big Booty said:

Check out the attached DB, for an explanation of what I've done check this link

Click to expand...

Hi John,
Thanks for your help ...
Unfortunately, I have read the Microsoft help according to the link you sent and everytime I try and change the alias name of the query within a query, Access automatically says it is closing?
I've tried everything, but it shuts down everytime I try to enter an alias name for the query.
Any ideas?
Thanks,
Ty

Strange what version of access are you running?

Where you able to open and have a play with the sample I posted?

John Big Booty said:

Strange what version of access are you running?

Where you able to open and have a play with the sample I posted?

Click to expand...

I'm actually running the latest version of Access 2007. I was only able to pull up the initial forms of what you had sent to me. It wouldn't allow me to do anything else. It said that it was a "read only" document. I wasn't even able to pull up the SQL info to see what you had done.
I had also seen the link to MS Help that you sent, prior to our initial conversation. So, I was familiar with it. But when I went to go through the process I kept getting the error and Access would shut done, every time I tried to insert an Alias name for that step in the process. I still haven't been able to do it.
The interesting thing is when I did a search online, for information to try and get it to work, I found several blogs from other users. They also referenced the same article on the MS site and the fact that they were also having the same thing happen. There were no answers listed as to why or how to resolve it. So, if you have a way, or perhaps another way to achieve the same result, please let me know.
Again, thanks for you help!

I'm not sure why it would be read only. I've just downloaded to my Vista Machine with 2007, with no problem. When you first opened the DB, You should see a security warning, below the ribbon, Saying "Certain content in the database has been disabled" with an option button next to it. You will need to use this to enable the code that make the form run.