Priority Field

[linvall]

Hello,



I've been working on an access database for a group at work which allows work to be prioritized in the order they should be executed. My current problem lies with renumbering finished work. For example, if there are 10 work orders in a list, and they are all ordered from 1 to 10, if priority number 1 is completed and deleted off of the list, priority #2 on that list doesn't move up so now the list is from 2 to 10. To fix this, we have to manually renumber each piece of work. Now in a list of 10, that isn't so bad but our list can range from 50 to 100 work orders, so it isn't efficient trying to reorder them every time a task is done or reprioritized. Is there a way to automatically order work orders when they appear on the list and if one of them is deleted or become a higher priority, the rest of the list updates to reflect this order change.

Thanks

[Minty]

What determines the order in the first place?

Generally speaking you would have some data point in your system that identified the order of work (Due date?) and simply sort your list on that - no need to have an actual priority number ?
Calculating and storing such a transient value is fraught with difficulties and uncertainties.

Maybe simply have a Urgent flag on your records, and sort by urgent flag then normal order of sort to give you a "prioritised" list.

In my experience most of these types of Hot list / Priority Customer / High Alert things don't actually work in real life, as sooner or later everything in the process becomes a "HOT Account", as each customer is gradually moved to the bottom of the list until they shout, because they aren't at the top of the list, so then you have a REALLY HOT Account flag...

Rinse and repeat ad infinitum / nauseum !

[kd2017]

Short answer, yes. There are a lot of ways to skin this cat. How comfortable are you with vba? Can you post a screen shot of your relationship window? How is priority determined in the first place?

[Micron]

Fairly certain this can be done via sql but will need a bit of time to come up with the syntax.

[Micron]

A picture might save a few words.


The sql

SELECT Count([tad1.ID]) AS Priority, tad1.Dte
FROM tblAvgData AS tad1, tblAvgData AS tad2
WHERE (((tad1.Dte)>=[tad2].[Dte]))
GROUP BY tad1.Dte
ORDER BY Count(tad1.ID);

This returns

Priority	Dte
1	3/01/18
2	5/01/18
3	6/01/18
4	7/01/18
5	8/01/18
6	9/01/18
7	10/01/18
8	11/01/18
9	12/01/18
10	1/01/19
11	2/01/19
12	3/01/19
13	4/01/19

There were 15 records in my test table, 1 with no date. It was ignored due to the >=, so Null values in the field used to compare will not be returned. I deleted a record and the rank went to 1 - 13, so it seems to work. Not sure how you would have to modify the comparison for your work order data but that shouldn't be too hard I think.

Basically, I'm including the same table 2x and aliasing them both (it's easier for me to discern between them and at least one has to be aliased anyway) but not joining them.

[linvall]

Sorry guys, was gone for the weekend.

To give some insight that will help answer some of the questions in this post. I work at a nuclear plant where things break and need fixing. Certain things need to be fixed right away while others can wait. For example, say if we have a pump that needs a gland seal replacement and leaking service water pipe. Priority wise, we would want to fix the leak first then work on the seal. So the leak would be the first priority and then the seal would be second. However, what if a shutdown system logic stopped working and we weren't able to monitor shutdown system conditions. Luckily, we have redundant systems and a second one goes into service, but we don't only want to have one working. If that one goes down, we have no choice but to shutdown. So that would have to become the first priority now. So I would like the list to change so that when I make the shutdown system the first priority, the other automatically move to 2 or 3.

I have attached a picture the table. Ignore the fact that the medium priority is higher than the high priority.

Also thanks Micron, I will look into your code and then see if i can apply it to mines.

Sample.zip

[CJ_London]

quite a complex scenario. It sounds like you need a very granulated order of priority (to replace Micron's dates), but also something that is time related as well i.e. something that is lower priority will always remain at the bottom until suddenly it causes a high priority event. i.e. preventative maintenance. It needs to move up the list the longer it is left. Example would perhaps be low pressure in a car tyre. Low priority to reinflate v recharge battery, put fuel in car etc - until it bursts. Addressing it sooner would have prevented the burst event from happening.

Minty's solution principle is the best way forward - but your issue is determining whether recharging the battery is a higher priority than putting fuel in the car (both are required for the car to be able to fulfil its function) and whether there is a time issue - perhaps the longer it is on the list the priority is increased by 1 for each day, or perhaps there is a servicing schedule that must be maintained, so the nearer it gets to the completion date the priority is increased exponentially. I would suggest consider having a wide number range - perhaps start with steps of 10 or 100, leaving plenty of gaps to insert new events. It may be even more complex that that, so do consider perhaps several priority type fields which use a calculation to give you an overall priority. Objective being to avoid duplicate values - if everything is 'high' how do you decide which one to do first?

[linvall]

Oh sorry, wasn't specific enough. The ANO (Authorized Nuclear Operator) will be setting the priorities manually. Access doesn't need to be able to tell if the shutdown system is a higher priority than the gland seal. I just want to make the ANO's life easier so if he does have to put something at a higher priority (ranking), he doesn't have to recode everything, Access will do it for him.

Example
Ranking
1 Leak
2 Gland seal

--------- Now the shutdown system logic malfunctions so it is added to the list. When the ANO reviews the list and sees the shutdown system logic on there, he will want make that ranking one on the list ----------
Ranking
1 Leak
2 Gland Seal
3 Shutdown System

---------If he changes shutdown system to 1, I want the leak to automatically go down to 2 and then the gland to go down to 3, almost like in word where if you start a count and change it, the numbers below automatically change and follow the sequence--------
Final Result
Ranking
1 Shutdown System
2 Leak
3 Gland Seal

[ArviLaanemets]

Let's assume you register some events, you need to be process. When all other conditions are same, you process those events in order you registered them. When some events are more urgent, you need to process them earlier. I think there you can have 2 possible solutions.

1. Events are registered in table with structure like tblEvents: EventID, EventDateTime, EventInfo, EventPriority, ..., EventStatus, where EventPriority has smallint values like 0, 1, 2, ..., and EventStatus = 0 means event is not closed, and EventStatus = 1 means the event is closed. EventPriority = 0 is lowest priority level. The To Do List of events is returned with query

SELECT * FROM tblEvents WHERE EventStatus = 0 ORDER BY EventPriority DESC, EventDateTime ASC

2. (On fly!) Events are registered in table with structure like tblEvents: EventID, EventDatetime, ProcessingTime, LastDatetime, EventInfo, ..., EventStatus, where LastDateTime is latest possible time the event must be processed. When LastDateTime is empty, it means the event is processed when there is enough time for this. EventStatus is like p.1. ProcessingTime is estimated time needed to process the event.
You need a saved query like

qUrgentEvents = SELECT EventID,  (LastDateTime - ProcessingTime) AS LastStart FROM tblEvents WHERE EventStatus = 0 AND LastDateTime Is Not Null

You need a saved query like (it returns non-urgent event which has shortest processing time - you can return oldest one instead when you want)

qTopmostNonurgentEvent = SELECT TOP 1 EventID, ProcessingTime FROM tblEvents WHERE EventStatus = 0 AND LastDateTime Is Null ORDER BY ProcessingTime, EventDateTime

You need a saved query like

qToDoEvents = 
SELECT ue.EventID, ue.LastStart FROM qUrgentEvents ue
UNION
SELECT te.EventID, NOW() AS [LastStart] FROM qTopmostNonurgentEvent te WHERE NOW() + te.ProcessingTime < (SELECT MIN(ue0.LastStart) FROM qUrgentEvents ue0)

Now you can return the To Do list of events, like

SELECT e.EventID, e.EventInfo, tde.LastStart
FROM (tblEvents e JOIN qToDoEvents tde ON tde.EventID = e.EventID
ORDER BY tde.LastStart ASC

[linvall]

Hey ArviLaanemets,

Thanks for such a detailed solution but I don't think we are on the same page here. Let me know if I'm wrong but from my understanding, this code will rank the items in my list automatically based on certain criteria, which is not what I want. The ranking will be done manually by a person, all I need is Access to reorder then when the ranking is changed. So if the ranked 8 item is manually changed to rank "1", all the other items are shifted down by 1(previously ranked "1" item is now ranked 2, previously ranked 2 item is now rank 3, etc.). This should also work the other way around, so if ranked "7" item is manually changed to rank 8, the ranked "7" item now becomes rank 8 on the list while previously ranked "8" will become rank 7 on the list.

[CJ_London]

think you are talking about what is often called a bubble sort. You need a couple of queries and I suggest a separate table for ranking, linked to your events. Code might be something like

Q1. to move existing rankings down

UPDATE tblRanking SET Rank=Rank+1 WHERE Rank>=newRank

Q2. to insert the new item

INSERT INTO tblRanking (Rank, EventID) VALUES(newRank, newRankID)

If you remove an event from rankings

Q3. DELETE * FROM tblRanking WHERE RANK=oldRank

Q4. UPDATE tblRanking SET Rank=Rank-1 WHERE Rank>oldRank


where you get newrank and oldrank from is up to you. I'm guessing a form

Pretty sure if you google/bing you will find code around 'drag and drop' where the user can drag an event to a new position to act as the events to provide these values

[linvall]

Alright thanks, I will look into this method.

[Minty]

You can use list boxes quite effectively for this, but it does take some effort with the coding, you can select and move an item up or down in the list.

[ArviLaanemets]

Let me know if I'm wrong but from my understanding, this code will rank the items in my list automatically based on certain criteria, which is not what I want. The ranking will be done manually by a person, all I need is Access to reorder then when the ranking is changed.

The solution in p.2. allows you to change the ranking order at any time editing this event only - you need to change LastDateTime for event earlier, and the event is moving up in To Do List to proper place automatically.

With solution in p.1. you can change the ranking order too - setting EventPriority to higher value. As EventPriority doesn't have any fixed values (except 0), you can rearrange events at will entering any priorities in range of smallint (or integer) values for any event with EventStatus = 0 (events with EventStatus = 1 are always ignored when To Do List is generated). But differently from p.2., events in To Do List are displayed in order based on EventPriority at first - when event with higher priority must be really processed in next week as last term, and it is possible to process some other events with less priority before that, then you have to increase priority for those events, or decrease the priority of event too high in To Do List, to get the proper To Do List.

[linvall]

Ah ok, that makes sense. Is there anyway to work with just one column (Ranking) and not have any of the numbers excluded (would like number 1 to be the highest priority on the list). Just trying to make this as simple as possible for the ANO. All he needs to do is change a number and refresh the list. After the refresh, the list is updated and showing the correct rankings. I've been trying to figure out Ajax's method as it seems exactly what I'm looking for, but the query keeps asking for a newRanking value. I've also tried changing one of the ranking values in my table but the query still doesn't work.