Voting and Ranking System

[tothstevenj]

Hey everyone!

First, thanks in advance for any help that can be provided.
Second, I'm not necessarily asking for something I can use verbatim nor am I asking anyone to work for free.
Third, I've spent nearly 2 hours searching on Google for the answer to my questions and finally got frustrated enough that I'm openly asking for assistance.

Now onto the full question.

I have an Access 2010 database containing a few tables and forms, it is designed to be a voting and ranking system set up as follows.

1. Individual employees login and are then provided different menus based on predefined access levels. (I have this set up already)
2. Admins login and get an admin menu for setting up the tasks to be voted on, doing user maintenence, and opening and closing voting (also finished)
3. Voters login and should be presented with a form to input their votes. (This is where I need help)

3a. The options that they are presented with should be pulled from tblJobs (a table I have created and prepopulated with test data)
3b. The voting system needs to rank users based on the below example.

Example: Users 1, 2, and 3 all rank their desire to perform tasks on a scale of 1 to n (n = number of available tasks)
User 1 votes first since he was (randomly) given a vote ranking of 1. User 2 votes second and so on through every user.
There is no set number of tasks and the number of users will vary depending on hiring and firing.
Once everyone has placed their votes the system needs to calculate a new voting order based on:

1. Did the user get a task?


2. Did the user get their first choice, second choice or did they choose to vote 0 for a choice because they did not want it at all?

4. After the voting is closed Access needs to generate a report to display for the admin to inform the "winners" (I can create this once I have all of the voting data tallied)

I'm running into trouble with programming the logic behind the ranking system and how to set up the tables within Access to reach my desired results.

I'm hoping that someone can help me brainstorm the best way to get this to work in harmony. If anyone needs more specifics, a copy of my database to see, or just wants to tell me I'm insane for attempting this please let me know.

Thanks again for any ideas you might be able to come up with.

[June7]

What do you mean 'User 1 votes first'? Only one person will be in the db at a time?

The ranking algorithm will probably be extremely complicated.

[tothstevenj]

June7, thanks for the reply!

Sorry, now that I look back I probably didn't word that very well. Ideally there could be as many as 10 in the database at a time, I'm not sure how to acomplish that right off the top of my head but I can google around for that.

I'll make a table to illustrate what I'm meaning by the ranking system.

	Task 1 name	Task 2 name	Task 3 name
User 1	3	2	1
User 2	2	3	1
User 3	2	1	3
User 4	3	1	2

Using the above table for reference, User 1 will get Task 3 since he is ranked highest. User 2 will get Task 1 since he is ranked second highest and his first choice is already taken. User 3 will get Task 2 since he is ranked third and that is the first task that is not taken. User 4 will not get a task since there are none left.

On the next round of voting the system should rearrange the users into a new order as follows:

	Task 1 name	Task 2 name	Task 3 name
User 4
User 1
User 2
User 3

I think for the sake of simplifying this a bit I'll ammend my previous post saying that after a user gets assigned a task they are then simply sent to the bottom of the list. I hope that makes this a bit simpler.

Look forward to any ideas, Thanks!

Steve

[June7]

Database with multiple users should be split design, although I have one very small db that is not split and multiple users does not cause issue. That db has no code (macro or VBA) at all.

The algorithm will be complicated and re-ranking the users will be tricky. I remember working with a poster a long time ago that had similar issue on 'rotating' record ranks. I think we solved with a field for this value then a procedure that would update this field to assign the current top rank to the bottom and renumber the others one step up (-1). I don't remember if the procedure was an UPDATE query or VBA code looping through a recordset.

I don't have code to offer. Make an attempt and when encounter specific issue post question.

The quick fix is to produce a report of the user choices then personally analyze the data, make decisions, and manually enter the task assignments. Same goes for renumbering the ranking for next round. Ten users seems not so many for this.