Set Priority for tracking jobs (1 Viewer)

[berrymik]

I am creating a db to track jobs. I have a “PRIORITY” column in a “JOBS” table. What I would like to do is when a new JOB comes in I will set the PRIORITY. IF I have 10 JOBS current and a new job comes in and I set the PRIORITY to say “5” I need all the JOBS below 5 to increment by 1. Thanks for any suggestions.

 

[theDBguy]

Hi. Welcome to AWF!

You can use an UPDATE query to do that. However, you might want to be careful when trying to change records in batches. Just a thought...

 

[Cotswold]

Why not use larger numbers? Say tens, unless you have hundreds of jobs in the priority list. Say using 1.....100, or 1.0.....10.0
A new job comes in then should it be 5.1, or 5.4 ? ; or in integers 51, or 54. When entered all your jobs should then just sort themselves out into their correct position without doing batch updates. Which as tDBg advises, batch updates of this nature can have startling results if you get something wrong.

 

[jdraw]

What else do you have to identify Job "completeness"? Sometimes Jobs are composed of Tasks--is that applicable? Priority is one thing, scheduling and monitoring are associated. Might be good if you showed readers your tables and relationships along with a description of the bigger picture identifying how/where Priority fits.
Here is a sample model from Gina Whipp's site
Welcome to the forum by the way!!

 

[KitaYama]

Why not use larger numbers? Say tens, unless you have hundreds of jobs in the priority list. Say using 1.....100, or 1.0.....10.0
A new job comes in then should it be 5.1, or 5.4 ? ; or in integers 51, or 54. When entered all your jobs should then just sort themselves out into their correct position without doing batch updates. Which as tDBg advises, batch updates of this nature can have startling results if you get something wrong.

We had a situation like this and this solution failed. In our case it was the order of manufacturing parts of a machine.
You have job 1,10,20...
A new job comes you set the priority to15.
Another one...You set it to 13
Another one...You set it to 12
Now you need to add a job between 12 & 13.
What do you do? Use 12.1? the next one will be 12.2?
And the next one 12.01?

Then you end up with numbers like 12.000001

At some point you have to batch update.

 

[KitaYama]

As I said we had a problem like this.
We solved it by using a class that allowed us to drag the records of a form and change their position. I'm not sure, but I think it was by @MajP.
I don't know why I can't find it now.

 

[Cotswold]

We had a situation like this and this solution failed. In our case it was the order of manufacturing parts of a machine.
You have job 1,10,20...
A new job comes you set the priority to15.
Another one...You set it to 13
Another one...You set it to 12
Now you need to add a job between 12 & 13.
What do you do? Use 12.1? the next one will be 12.2?
And the next one 12.01?

At some point you have to batch update.

As I said, increase the number sequence. If you need more jack it up to 1..100, or 1...1000. You must choose a number sequence that will cover all future requirements for this fairly simple operation. You could prefix with a letter which gives you 2,400 splits, A01, B02, H22...etc. But it is too easy for some on the shopfloor to mistake or misread some alpha characters.

From your 1st post 1..10 would do but now it appears you'll need far more. If you use decimals and one isn't enough then use two. 12.00....12.99 from above. Surely 100 slots per integer are enough? Personally I'd start at 10.00 and end at 99.99 from your information. Once implemented, as I say they will automatically sort themselves. And to reiterate, if that isn't large enough, increase the numbers in your analysis. Increase the leading number, or the number of decimals, simples.
Regarding block updates, another problem with them is that if they aren't correct, it will probably be impossible to revert to the previous setting, once they have run.

 

[MajP]

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

This allows you to manually sort large lists, by moving records up and down

 

[KitaYama]

As I said, increase the number sequence. If you need more jack it up to 1..100, or 1...1000. You must choose a number sequence that will cover all future requirements

In an organization with hundreds of stuff and loads of jobs, you will never know.
And managing numbers with large gaps is very hard.
Check @MajP's class and see how it makes your life easier.
Hint : it updates the table at last.

 

[jdraw]

Seems MajP's class approach allows you to move records (adjust priorities) as and when needed. I don't see Priority being a hard and fast rule, but a more dynamic thing that can change based on some "business event".

 

[Cotswold]

In an organization with hundreds of stuff and loads of jobs, you will never know.

I would have thought that the analysis would provide that information.

From what berrymik asked it appeared to be a small manufacturer with maybe ten to twenty live jobs. In which case a numerical sort is a quick and easy solution without any extra code needed. With jobs being added and others presumably dropping off as they progress. Basically computerising something that a bright manager would be doing in his head during the working week. Four digits are ample.

However, to use a numeric sort in any form of production control or production planning is certainly not the way to proceed, as WIP is controlled by dates and processes.

 

[Pat Hartman]

This sample is for a custom unique ID but it also includes a simple numeric list. The rows are added with a gap in the generated number to make it easy to move the numbers around. I'm including it because it has a renumber function that you might find useful after you have renumbered manually or run out of room in a part of the sequence.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk