CODEWITHSUNDEEP
ms-access
jdiaz5513
jdiaz5513

Reputation: 168

MS Access: Rank/Priority Sorting

I'm at a total loss at how to accomplish the following scenario in Access:

Say I have two tables, Users and Tasks.

Users                        Tasks
----------------------        ----------------------
Username [Primary Key] <1--∞> Username [Foreign Key]
                              Task ID [Primary Key]
                              Priority

I'd like to display a form that shows a particular user's tasks, sorted by Priority (an integer). The priority is not fixed, and I need to make sure no two tasks from the same user have the same priority. I have no clue how to accomplish this using Access. What kind of query do I need to build for the form? Ideally, I'd like to have two buttons on the form to raise/lower the Task's Priority.

Any suggestions would be greatly appreciated. I'm not averse to using VBA if that's what it takes, but I have a feeling there's an elegant solution to this.

(Note: the actual DB I'm working on is very different, but knowing how to do this will allow me to figure out how to get what I need.)

Upvotes: 0

Views: 3804

Answers (2)

Stephen Turner
Stephen Turner

Reputation: 7314

Knowing the current record's Username, TaskID and Priority you need to run three queries:

  • First park the priority of the current record, set it to null or zero or some other value for a moving record.
  • Then make the space by changing the record thats in the way.
  • Finally move the parked record into it's new slot.

To move the current record up run each of these in VBA:

"UPDATE Tasks SET Priority = 0 WHERE Username='" & MyUsername & "' AND [Task ID]=" & MyTaskID

"UPDATE Tasks SET Priority = " & MyPriority - 1  & " WHERE Username='" & MyUsername & "' AND Priority=" & MyPriority + 1

"UPDATE Tasks SET Priority = " & MyPriority + 1  & " WHERE Username='" & MyUsername & "' AND Priority = 0"

Swap the +1 and -1 s to go the other way.

You can do it with two queries if you don't have a unique index on unername/priority, but I suggest that you add one. You can also wrap them in a transaction for some safety in a multi user environment.

Upvotes: 0

JeffO
JeffO

Reputation: 8043

A unique index on Username and Priority on the Tasks table will enforce your rule. A warning will be given when the user attempts to save data that violates this key. You may want to trap the error and provide a little better explanation.

The main form can be based on the Users and a subform based on Tasks. Access will aid in helping you use the UserName (your key fields) for the Parent Form and Child Form.

A little VBA on the current record can increment the Priority field. You would have two buttons each with their on On_Click event to increase/decrease the value. Example: [Priority] = [Priority] + 1

Upvotes: 1

Related Questions