Subjective Ranking within an Array

[RyanP]

I reviewed a few ways to rank based on calculations and use them, however what I'm looking to accomplish is subjective ranking whilst limited the ranks to the total number of records in the recordset.

e.g. you have 50 records. Manually moving Record # 30 up to #3 would bump the existing records #3 to #29 down one (to #'s 4 through 30) and records >30 would stay the same.

Would deploy this to the end users within a continuous form, each row having Up/Down arrows to move each record up/down 1 rank as well as provide a combo box so they may choose any value within the array they want to set it to...

simple?

[orange]

Add a field to the recordset -number datatype. Let user populate the field with a value indicating his/her ranking of the record within the recordset.
Sort recordset by User Ranking field to show ranking in rank order.

[June7]

Not simple to do programmatically. I have seen this topic before.

Why would you want this feature?

Besides, not practical with multi-user db.

[RyanP]

We do calculated ranking but the evaluation system is not 100% perfect or all encompassing (There are a plethora of outside factors that are either too difficult to apply an algorithm too, or just not well suited for calculated ranking).

So, we want the decision makers to be able to make their own picks/ordering regardless of the calculated rank (they are doing this already in excel exports).
e.g. Calculated Rank = 3ofX. Pick = 1ofX

Not worried about multiple users in this scenario, as it would typically be a committee of decision makers running the selection process . I presumed that the "picks" table would be its own separate table as this function would need to lock the entire recordset.

[RyanP]

Add a field to the recordset -number datatype. Let user populate the field with a value indicating his/her ranking of the record within the recordset.
Sort recordset by User Ranking field to show ranking in rank order.

This method would allow for duplicates and would not move the value of all the other records. e.g. If I select value = 2, this would not add 1 to every value that is 2+...

[orange]

Ryan,

My interpretation was that a user/client could modify/apply the "subjective" rankings. Instead of moving records(rankings) with each user entry, just provide a field for that user/client to rank the records according to their "subjective criteria". In this simple set up, I allowed for that user/client to rank all records. (You could certainly add code to add incremental/sequential numbers and to enforce No Ties.
Numbers start at 1, max is RecordCount, field has No Duplicates)

What exactly do you want to achieve? Why does it have to be with Up/Down arrows?
Does a user/client see someone else's rankings, or do they "subjectively" rank records independently?

If there are other conditions, then please list them.

[RyanP]

Not simple to do programmatically.

Figured Microsoft would make it easy... This function is technically already built into Access in a few places (via drag and drop mind you; Tab Order, Table Design) And in Excel - Chart Data Sources - Legend Entries.

[RyanP]

Numbers start at 1, max is RecordCount, field has No Duplicates

Yes.

Why does it have to be with Up/Down arrows?

I thought of this optional method based on having seen it used before... Figured that it may be more user friendly if the list is short. Not 100% required if I can get setting it via manually entering the value into a text box (or selecting from a combo box).
This method would be limited to only moving one value in the ranking at a time (taking the adjacent higher/lower rank and modifying the other accordingly).

Does a user/client see someone else's rankings, or do they "subjectively" rank records independently?

Independently. Others would view them (but only via read-only snapshot recordsets)

If there are other conditions, then please list them.

None... RE: Usage - Subjective Ranking is the primary purpose. The other would be manually setting the order of lists (e.g. they have added 6 references to a record and want them to be displayed in a particular order that is not something as simple as alphabetical, or date added etc...Then, when they run a report on the main record, the references in the subreport would then be ordered according the 'rank' or 'display order' they set in the form.

[orange]

Can you post 5 -6 records to show Where/How subjective ranking would be applied?

I'm sort of visualizing:

Here are 10 records /statements

please rank them in importance to you 1 is most ---10 is least.

[RyanP]

This function is technically already built into Access in a few places

This functionality is in the Page Indexes too

[June7]

Yes, Access manages adjusting ranking for some properties and the algorithm can't be simple. Unfortunately there is no intrinsic feature for developers to use on data.

I know I've seen threads on this same topic but finding them again is a challenge.

This article seems to describe similar issue http://www.fontstuff.com/mailbag/qaccess02.htm

[RyanP]

Can you post 5 -6 records to show Where/How subjective ranking would be applied?

I'm sort of visualizing:

Here are 10 records /statements

please rank them in importance to you 1 is most ---10 is least.

1234567890 characters

[orange]

See the code in the form and buttons in this link from Chip Pearson. This should give some vba options.

I am not an Excel person, but you can see the(vba) in functions associated with each of his many buttons.

[RyanP]

See the code in the form and buttons in this link from Chip Pearson.

Thanks... the completeness and complexity of the code was a bit above me... Regardless, I will refer to and/or use this in the future I'm sure.

[RyanP]

(Example DB attached)

Managed via the form. Required 3 queries.
I am not explicitly limiting the values to Numbers start at 1, max is RecordCount, field has No Duplicates, Must be contiguous

Just assuming the values will stay limited to this via the logic between the form & queries... (Logic doesn't keep from going beyond the max of the recordset or using 0).
Obviously, would turn warnings off to use this in production...