I'm building a project database to keep track of the various
engineering projects ongoing in our department. These projects will be
grouped by priority ([cmbPriority]=High, Medium, Low) and ranked within
the priority ([Rank]=1,2,3,etc.). What I'd like for it to do is
automatically update rankings when a project rank is changed, a new
project comes in, or a project is completed. For example, let's say
there are 4 high priority projects. If High #2 is changed to High #4,
then old High #3&4 should become High #2&3. Or, if High #2 is
completed, then High #3&4 should become High #2&3. If we add a new
project and rank it High #3, then High #3&4 should become High #4&5.
I'm assuming I'm going to have to use an append query for this, but I
just can't seem to figure it out to cover all of the scenarios. Any
help would be GREATLY appreciated.

prime80 wrote:[color=blue]
> I'm building a project database to keep track of the various
> engineering projects ongoing in our department. These projects will be
> grouped by priority ([cmbPriority]=High, Medium, Low) and ranked within
> the priority ([Rank]=1,2,3,etc.). What I'd like for it to do is
> automatically update rankings when a project rank is changed, a new
> project comes in, or a project is completed. For example, let's say
> there are 4 high priority projects. If High #2 is changed to High #4,
> then old High #3&4 should become High #2&3. Or, if High #2 is
> completed, then High #3&4 should become High #2&3. If we add a new
> project and rank it High #3, then High #3&4 should become High #4&5.
> I'm assuming I'm going to have to use an append query for this, but I
> just can't seem to figure it out to cover all of the scenarios. Any
> help would be GREATLY appreciated.[/color]

This is not a trivial problem so: <BeginHandWavingMode> I think the
cleanest way to do this is using linked lists. Linked lists can be
implemented in Access by using a Long field to contain the value of an
ID field (simulated pointer). Each item in each priority list points
to the next item. You'd need Add, Move (reprioritize Rank within a
Priority Group or Delete and Add to a different Priority Group) and
Delete operations. Most data structures books explain how to do these
operations in detail, especially the one by Nicolas Wirth. Hint: The
people in this NG love answering questions like this so you probably
don't need to visit the library.

Maybe the first rank element in the linked list (for a given priority
group) could contain a marker. This marker may need to be moved
depending on the operation. With three different markers one table
could hold all three linked lists. A single table would make it
somewhat easier to move between Priority Groups. You'd want to use a
form to handle these operations. Then you'd need a way to traverse the
lists to update priority numbers so that the data can be displayed
easily in a query. A Null value for the next ID would terminate the
list. There's potential ugliness any way you implement it. If you
don't want to use linked lists you can remove Rank number 3 and then
run an update query to subtract 1 from any Rank > 3 (within the
Priority Group) etc. Either way, you still have to deal with what to
do when the priority list is empty and other gotchas.
<EndHandWavingMode>

I hope someone comes up with something much easier.

James A. Fortune