Resort numbers

[Rabidsnipe]

Brand new to Access.

Let me give some set up to better explain my problem.

Right now I am trying to develop a database to be used by two different
groups. The first group are shop supervisors and the second group are
mechanical engineers (of which I am one). The database will allow the shop
supervisors to input projects into a database. The engineers will then take
the projects, assign them to an engineer and so-on. The thing I'm having a
problem with is assigning priorities. I would like for the supervisors to be
able to assign a priority to each project; I'd like the priority to be
numerical ranked 1-# of projects. The problem I'm having is how to handle
changing priorities. I want to be able to simply go into the database,
change a priority on a single project, and then have all the priorities
refresh to reflect the new placement.
Example: I have three projects: Alpha, Bravo, and Charlie. They are ranked
Alpha #1, Bravo #2, and Charlie #3. I need to change Charlie to #2. I go
in, change priority to #2. I then want Access to change Bravo to priority #3.

Any ideas on how to do this? I imagine there is a simple way I just don't
know enough about the program. Thanks a bunch.

 

[Larry Daugherty]

What you're struggling to resolve isn't an Access issue. It's the
problem of assigning dynamic but workable priorities. There are all
sorts of schemes but they all break down because politics trumps
anything you might have in your priority system.

You might set up a system based on Importance, Urgency, Status and
Date of Entry.

Importance should have as many different levels as you think
appropriate with a weight for each item in the list. You must display
the element name with an unambiguous description so that different
people make the assignments at different times the Importance will be
the same. To have a hope of your priority scheme working at all you
have to address the likelihood that the company president or some such
may direct high priority treatment for some task.

Treat Urgency with the same as Importance above

For those two elements, it would be a good idea to hold a meeting or
two or more with all of the players involved - from the supervisors
down to the worker bees - to get the elements ironed out and decide
whether you want to small numbers or large numbers to trump.

A well thought out Status column might be added to the mix. Just a
few levels would probably suffice but there should probably be large
weight differentials. With an initial Status of "Unassigned" you'd
want to bias the priority to the low end. With a Status of "In
Progress" you'd want to bias the priority pretty high (you don't want
to be dithering projects in and out of the queue).

The product of Import X Urgency X Status will be the raw priority
value.

The Date and time of entry would resolve numeric ties.

There should also be a Note field in your scheme so you can enter the
source of the task assignment (see President above).

Good luck designing and implementing it. Get the buy-in of the powers
that be. The success of the system depends on those who have the
power to destroy or pervert it not do so.

HTH

 

[Rabidsnipe]

I am aware of the difficulty of implementing such a system. We have the go
ahead from the people that make these types of decisions, I just don't know
how to make it. Any advice.

 

[William Hindman]

....resorting the numbers is easy ...but what you are doing is changing the
numbers first and then resorting ...very different ballgame since Access
can't yet read your mind ...you have to tell it what to change and to what
value.

....if I were doing this, I'd probably reorder the project priorities using
drag n' drop in a treeview or list ...but if you're relatively new to Access
or unfamiliar with d&d, it is NOT a "simple" way to go.

....if the combination of project and priority is unique, you can set a
unique index on the field combo and then trap for the error when you try to
change #3 to #2 ...that would give you code control to delete the existing
numbers and reconfigure them either with a prompt or automatically with a
select case if the number of priority levels is short ...else it would get
convoluted.

....hth

William Hindman

 

[Larry Daugherty]

My earlier response contained the advice on the issue you posted.
What additional advice are you seeking?

Bear in mind that everyone who responds here is a volunteer. The
general expectation is that the issues posted will Access technical
issues and that an effort will be made to keep to one issue per
thread. Sometimes that's hard to do.

Many people post issues in the Access newsgroups that are very like
your own. The problem domain and the algorithms involved in the
solution have nothing to do with Access. They have to be dealt with
independent of any development platform. Access just happens to be
the platform in which you/they have already decided to provide the
solution. It's only when you have some pretty good ideas about the
solution

HTH