Renumbering System (For Projects)

[roystreet]

Hello,
Here is my scenario & I am very open to sugesstions. I have a projects DB in which each item has a unique item number generated via VB code. Although these items vary in priority & that can change at the voice of the boss. So this means that not necessarily does item 303 have to be done before item 304. That said, now on to my "Problem", I need to generate a list of items due. OK, simple enough - Run a query & filter all items in the system status is <> "Complete". But that does not allow for me to know what order they should be worked on in. So, I can via a form in a priority field, label item 400 to be item prirority 2 on the list to do today & 403 to be item priority 1 on the todo list today. OK, that's pretty simple to do. But say today, I only am able to complete the first item on the "Todo" list (Marking item 403 as complete will stop it from showing up on my to do list - Which is great!) But when I run my todo list for the day, it still shows item 400 = Priority 2 on the list. I want a way to renumber the available items on the list - Make the item that is now item priority 2 to now become item priority 1 since it's replacing the old item priority 1. Now, remember - I may need to go in via a form & override the numbers because the boss said, no this is your priority today. So, the number must have the ability to be manually changed at hand. I hope I'm not confusing you to much.

Thanks,
---roystreet

 

[JoeAtWork]

I'm not sure I've grasped it. You want it to renumber everything after one of the "To Do" items is marked as complete? If so, I think a single SQL statement would do that. Let's say you've ticked off the first item, that means everything below that goes up by one, so an update something like this:

UPDATE MyTabel SET Priority = Priority - 1 WHERE status <> "Complete"

Or were you asking about changing it on the form, when the boss changes his mind?

Let's say the current priority 3 gets bumped to the top. In that case, everything in front of # 3 gets bumped down one, so:

UPDATE MyTabel SET Priority = Priority + 1 WHERE status <> "Complete" AND Priority < 3

Slightly more complicated is if something gets moved but not all the way to the top. In that case, everything between it's old position and it's new position gets bumped down. For example, if priority # 6 gets moved to # 3, then the current 3, 4, and 5 move down to positions 4, 5, and 6

UPDATE MyTabel SET Priority = Priority + 1 WHERE status <> "Complete" AND Priority < 6 AND Priority >= 3

More generically, lets use variables, where OldPosition is where it used to be, and NewPosition is where it got moved to (on working on the assumption that things always get moved up, not down):

CurrentDb.Execute "UPDATE MyTabel SET Priority = Priority + 1 WHERE status <> ""Complete"" AND Priority < " & OldPosition & " AND Priority >= " & NewPosition

 

[roystreet]

Hello,
Thank you for your response & you have given me several options which is very nice. Based upon the first example you gave me, I went ahead & made a query. Just for kicks (For now), the main table is named: tbl_DIL & I named the query: qry_DIL. The SQL in my query is as follows:

UPDATE tbl_DIL SET tbl_DIL.Priority = Priority-1
WHERE (((tbl_DIL.Status)<>"Complete"));

Unfortunately if I change the status value of the item that was set at Priority = 1 to "Complete" (via the table) & then I run the query...It updates 0 records. No change, all records continue to hold the same priority value. What do you see that I'm doing wrong?

Thanks,
---roystreet

 

[PHV]

Perhaps tbl_DIL.Status may contain Null values ?
UPDATE tbl_DIL SET Priority = Priority-1
WHERE Nz(Status,' ')<>'Complete';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roystreet]

Hi PHV,
That was the issue - As I went back I changed all of the status values to "Awaiting" for now & then I came back to see your response. Thanks, that was quick. This is my idea. (Now remember, I'm still formulating in my mind) On the form, I will allow the user to see the list of items (Priority Ascending). The user can go in & change the value of Status to "Complete". At that point, they can press a button "Re-Number Items" (Or something like that) & then it will update the list running that query. My concern is duplicate numbers. Do you know what I mean? Would it be more wise to have the form update the numbering & not a query? If the user wanted to put a new item in between some records - I would have to renumber all of the items manually wouldn't I?

Thanks,
---roystreet

 

[JoeAtWork]

>>>> I would have to renumber all of the items manually wouldn't I?

No, I would use SQL update statements for any of these scenarios. It's more efficient and reliable.

The UPDATE statement basically guarantees you an all or nothing update, either all qualifying records get updated or none do. In contrast, if you update every record one at a time you are much more likely to get an inconsistent update. Let's say there's some error in the middle of updating one of the records, for example. Depending on your error handling, that record may not get updated properly, or worse all records following that one don't get updated either.

If somebody inserts a new priority record in the middle, you simply need to bump all the other records that are the same or lower priority down. For example, if somebody inserts a new item as priority 5, the existing priority 5 gets bumped down to 6, and everything below that also gets bumped down, so:

UPDATE MyTabel SET Priority = Priority + 1 WHERE status <> "Complete" AND Priority >= 5

Note: you would run that UPDATE statement before inserting the new priority 5 record (otherwise it would get bumped down too).

 

[roystreet]

Sorry - I had a telecon. My question before I begin to roll this out is...I want to make it so that if the user needs to add an item, my best way to do this is to have a query in which performs the above "Priority + 1", correct? If the user is taking out an item, then they would need to run a separate query where it is "Priority - 1", correct? I guess I'm looking for flexibility. I would definetly prefer to do as little manually as possible.

 

[roystreet]

Scenario One: The user has now completed the item Priority = 1. He clicks on a button that runs the first SQL subtracting 1 from all of the rest of the items. (So the list is ready for the next day's workload)

Scenario Two: The boss comes in & adds a new item that will now be item Priority = 2. He is not getting rid of the old Priority = 2 item, but bumping it up to 3. OK, so I would run following:

 UPDATE MyTabel SET Priority = Priority + 1 WHERE status <> "Complete" AND Priority >= 2

(It will kinda "Ripple" all of the lesser items up to a higher number. But will not effect item priority = 1 at all.

Scenario Three: The boss now says that item Priority = 5 needs to be changed to 10. What wouild be the best action here? Because items between 6-9 need to be lowered so that they now equal 5-8, but I don't want items 11-20 to change their values at all?

Thanks,
---roystreet

 

[roystreet]

OK, I've gone ahead addressed scenario two. The boss wants the user to add a new item priority = 2 to the list bumping 2 & above to a higher number. The SQL I have listed below prompts the user what item priority number they want to add this new item to. This of course is going to be done previous to adding the new item.

UPDATE tbl_DIL SET tbl_DIL.Priority = Priority+1
WHERE (((tbl_DIL.Priority)>=[What Number Are You Adding]) AND ((tbl_DIL.Status)<>"Complete"));

This of course will probablly get perfected later on, but for now what do you think?

---roystreet

 

[JoeAtWork]

Looks like it will work to me. Of course, test and then test again. Three test to try would be:

1. Insert something at the beginning
2. Insert something in the middle
3. Insert something at the end

I believe I've already dealt with your scenario # 3 in a previous post.