Automatically replace numbers

[Rabidsnipe]

Brand new to the forum and 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 is engineers. 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 #1. I go in, change priority to #1. I then want Access to change Alpha and Bravo to priority #2 & #3 respectively.

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.

 

[SkipVought]

Hi,

Seems to me you would need to write some VBA code to...

1. Query to get the CURRENT Priority assignments Ordered By Priority.
2. Determine which row is chaging to Priority 1
3. Assign new Values and Update each row with a Priority less than that row.

Skip,

_[red][/red]

 

[Golom]

I guess you could do it with two UPDATE queries

Sub ChangePriority(ProjectName As String, NewPriority As Long)
Dim SQL As String

SQL = "UPDATE myTable " & _
      "SET Priority = Priority + 1 " & _
      "WHERE Priority >= " & NewPriority
CurrentDb.Execute SQL

SQL = "UPDATE myTable " & _
      "SET Priority = " & NewPriority & " " & _
      "Where Project = '" & ProjectName & "'"
CurrentDb.Execute SQL

End Sub

You do however still have the issue of assigning a priority to a new project and the issue of deleting projects if you want strictly monotonically increasing priority values with no gaps in the sequence.

 

[Rabidsnipe]

It seems like maybe this is over and above my ability as a "programmer". Any suggestions on where to learn to do either of the suggested solutions?

 

[Golom]

Probably ... here.

... but you do need to ask the questions.

We don't know the current state of your "... ability as a "programmer" ...", so, without you stating your problem and asking questions where you just aren't "getting it", we really can't effectively guess what to tell you.

Skip's and my suggestions are very similar, differing only in terms of mechanism. He told you "what" to do while my suggestion shows a bit more of the "how to do it" stuff.

Have you made any attempts to solve the problem on your own? If you have then post them and we can then better assess what the next steps are.

 

[missinglinq]

Another question: Is this always going to be a matter of reassigning a project's priority to #1, or would you sometimes need to reassign a project's priority from, say, #3 to #2?

The Missinglinq

There's ALWAYS more than one way to skin a cat!