Reassigning

[TravelerOn]

Hello all,

Question...the database I'm writing monitors several clinical trials (a four digit number/primary key) at a given time. They are assigned a priority number for reports...trial #9999 has priority 1, #9998 priority 2...etc.... HOWEVER, the priority number is reset on a monthly/weekly basis. If I decide to make #9998 priority 1, is there a way for Access to understand or automatically reassign the priority numbers for the other trials....in other words, "bump" the former priority #1 trial to #2....#2 to #3...and so on?

Thanks for your help,

 

[Jack Cowley]

Since I am not sure how you are keeping track of this priority number I am not sure how to answer your question. If you have a separate table with the Trial Number and its Priority then I would think that a small form with each trial number and its current priority would do the trick for you. Since trial #2 can go from priority 1 to priority 2 that means that trial #1 can become either priorty 2 or priority 3 as you may not always want to 'bump' them to the next available position.

[This message has been edited by Jack Cowley (edited 12-17-2001).]

 

[Matthew Snook]

I haven't written this out, but it seems that you should be able to do this with a query. Assuming you have just entered a priority number into some control, you could query for all records with a value <= the current priority. This is because the current reprioritizing will not affect records with a lower priority rating (if you're giving this one a "5", then numbers 1,2,3, and 4, will remain "as is"). If you're entering priority 1, all the records will come up in the query. Now simply update (through an update query) all priorities to be Priority=Priority+1. Excluding the current record, of course.

There. I've done the hard part...

Matt

 

[TravelerOn]

Hi, let me see if I can explain better....There is a table containing numbers...1-15, and another table containing, along with copious additional information, trial numbers....the tables are related such that a lookup box searches the Priority table (the one with the numbers) and assigns a number to a corresponding trial...the field name in the Trial table is Priority (etc etc). Now, one week...trial #3999 is priority #1, so that when I sort the report ascending by the Priority field in the Trial table....#3999 pops up first, the trial with Priority #2 pops up second...and so on. But a week later, due to internal corporate interest, trial #3999 is no longer as important as trial #3994, so #3994 has to take priority spot #1, while trial #3999 now has to be reassigned priority #2, the trial formerly with priority #2 has to take priority #3.......ad infinitum....you see my problem? I can manually assign the priority numbers to each trial methodically every week...but say I go over 600 trials...thats a lot of tabbing and clicking through the tables to reassign the numbers...is there a better way?

 

[Matthew Snook]

A query should work whether the field is in a separate table or merely a field in the main data table. Jack's concern is more likely to be a problem.

Matt

 

[TravelerOn]

Would there be a way to automate this procedure for an end-user? I understand his concerns, but in this company, they would never be an issue. We would ALWAYS want the trials "bumped" so to speak, into the next priority slot....thanks for your advice so far.

 

[Matthew Snook]

If you place the query in the "afterUpdate" event of the control, then all priorities would be reassigned whenever one was updated. No user intervention required.

Matt

 

[Jack Cowley]

Assume 5 records, 5 priorities and an update query. Change priority 3 to priority 1. The old #1 becomes #2; #2 becomes #3. #4 becomes #5 and #5 becomes #6. Now #4 is missing and you have and extra number (#6). What am I not getting in your scheme?

 

[Matthew Snook]

You're right, of course. My algorithm is too simple to work. I guess an iterative process of checking for empty priorities and bumping up the ones below if found.

Or the query could leave intact all priority numbers greater than the current record's initial value, and bump down all priorities >= the new value and < the initial value of the current record.

I guess I shouldn't respond unless I'm willing to write the thing! Thanks for the doublecheck!

Matt

 

[Jack Cowley]

Matt -

At first glance your solution sounded good, but then I started thinking (dangerous)and I realized that it wouldn't work, but I think you are on to something now. I have been giving this some thought (more danger) but now I am waiting for Pat Hartman to jump in with her incredible wisdom and problem solving ability to give us all a simple and clever answer....

Jack

[This message has been edited by Jack Cowley (edited 12-17-2001).]

 

[TravelerOn]

Hello,

Thanks for your ongoing advice/discussion...I realized the point you made, Jack, after trying several combinations of "updating" on a dry-erase board.The query would have to apply the "+1" only to values between the original priority (let's say 5) and the new priority, assuming the priority was 'decreased', or went from 5 to 2......2 (less than/including) [x+1] (less than) 5.....I'm going to go try it out and then post the results.
-Kate

 

[Mitch_____W]

Maybe you could create a macro that runs immediately after your update which updates the priority fields which have a value of 6, to a value of 5. Then you would limit the priority count to five. Still the problem of after 4 updates all of your records would be 5 except for the records that you made #1.

Wow... This could be a fun one to figure out... Tell me more...

Glutton for punishment!!

Mitch

 

[Mytholibar]

What about this?

Instead of allowing a user to enter the number to set the priority you use a up down arrow or a first last. You could then know the direction of the numbers you are needing to change.

So if I click up one I swap the two numbers (Unless I'm 1 or 0 or the top of the list).

Same with down.

If you click last pull the last number set it and decrease the numbers from there as Travelon had posted.

do the oposite for the ascending.

??? Think this would work..

I'm trying it now but I was to excited to try it before I posted0.