Numbering

[gst]

I have a list of work orders in a database that I would like to assign a
priority number. This will be the order in which the work orders will be
worked. As new work orders are created the priority for the work orders may
be reassigned. If I have a list of work orders and they are numbered from 1
to 100, when the next work order comes in, I may assign that work order a
priority of 10. I would like to have the rest of the work orders to
automatically be reassigned priority numbers 11 through 101.

Any ideas?

 

[Marshall Barton]

I have a list of work orders in a database that I would like to assign a
priority number. This will be the order in which the work orders will be
worked. As new work orders are created the priority for the work orders may
be reassigned. If I have a list of work orders and they are numbered from 1
to 100, when the next work order comes in, I may assign that work order a
priority of 10. I would like to have the rest of the work orders to
automatically be reassigned priority numbers 11 through 101.

Execute an Update query like this in the AdterUpdate event
of form text box (named txtpriority) bound to the priority
field:

UPDATE thetable
SET priority = priority + 1
WHERE Priority >= txtpriority
And . . .

The ... in the where clause is whatever criteria you would
use to isolate the work orders that are either completed or
not ready to have a priority.

If you also allow work orders to be deleted, then you would
need an analagous update query to reduce priorities above
the one being deleted.

 

[gst]

Marshall-

Thank you for your response. I am a beginner Access user. I have never had
to create a script before. I am using Access 2007. Do I place this in a Macro?

Thank you for your help.

 

[Marshall Barton]

This exercise might be beyond your current skill level. The
technique I outlined was intended to be done using VBA code
in a form text box's AfterUpdate event procedure. I can not
even begin to help with the code until you provide a lot
more information about where/how you expect users to
set/reset the priority value (including form, control, table
and field names).

I have yet to use A2007 beyond just poking around, so I
don't know if the new macro features are sufficient to this
task and I certainly could not help with it.

 

[gst]

I work for a help desk. The requests have become extremely backed up. We are
attempting to clean up the requests by working on them according to a
priority number assigned by a teammember. One person will be assigning a
priority to each work order.

Right now, I have a text box with a field called Priority in the
IS_Reporting Table. I have also created a form IS_Reporting which will allow
for easy input of data for the end user.

As new tickets come in, some of the priorities previously assigned will be
changed. I would like to have the remaining orders automatically renumbered
when these changes are made.

This exercise might be beyond your current skill level. The
technique I outlined was intended to be done using VBA code
in a form text box's AfterUpdate event procedure. I can not
even begin to help with the code until you provide a lot
more information about where/how you expect users to
set/reset the priority value (including form, control, table
and field names).

I have yet to use A2007 beyond just poking around, so I
don't know if the new macro features are sufficient to this
task and I certainly could not help with it.
--
Marsh
MVP [MS Access]

Thank you for your response. I am a beginner Access user. I have never had
to create a script before. I am using Access 2007. Do I place this in a Macro?

 

[gst]

-adding a new record without specifying its priority.
The work orders that have an open status are downloaded into an Excel
spredsheet nightly. The new requests are submitted with no priority level
assigned. I would just do an append query to grab the new records and add
them to MS Access. Once the new records are retrieved that is when the
priorities are assigned.

-deleting an existing record
once the status has changed to resolved or closed i would need to delete
those records. currently i just have the filter set on the status field to
show only the open orders.

-just clearing an existing record's priority
it was my hope that I would be able to clear the existing priority and
update it with a new assigned priority. because i have no experience with
Access programming then I assumed it would not present a problem.

-how to identify other records that should not have their priority adjusted
I have not reached this part of the process. I was assuming that I could
start with just the records that had been reassigned. The records that had
not been reassigned would not need to be changed.

I understand your general goal about adding a new record and
setting its priority

You have to provide details about other possible
interactions with your data such as:
- adding a new record without specifying its priority
- deleting an exiting record
- just clearing an exiting record's priority
- editing an existing priority
You also failed to explain how you identify other records
that should not have their priority adjusted (e.g. closed
calls).
--
Marsh
MVP [MS Access]

I work for a help desk. The requests have become extremely backed up. We are
attempting to clean up the requests by working on them according to a
priority number assigned by a teammember. One person will be assigning a
priority to each work order.

Right now, I have a text box with a field called Priority in the
IS_Reporting Table. I have also created a form IS_Reporting which will allow
for easy input of data for the end user.

As new tickets come in, some of the priorities previously assigned will be
changed. I would like to have the remaining orders automatically renumbered
when these changes are made.

 

[Marshall Barton]

-adding a new record without specifying its priority.
The work orders that have an open status are downloaded into an Excel
spredsheet nightly. The new requests are submitted with no priority level
assigned. I would just do an append query to grab the new records and add
them to MS Access. Once the new records are retrieved that is when the
priorities are assigned.

-deleting an existing record
once the status has changed to resolved or closed i would need to delete
those records. currently i just have the filter set on the status field to
show only the open orders.

-just clearing an existing record's priority
it was my hope that I would be able to clear the existing priority and
update it with a new assigned priority. because i have no experience with
Access programming then I assumed it would not present a problem.

-how to identify other records that should not have their priority adjusted
I have not reached this part of the process. I was assuming that I could
start with just the records that had been reassigned. The records that had
not been reassigned would not need to be changed.

Start with this in the priority text box's AfterUpdate event
procedure:

Dim db As Database
Dim SQL As String

Set db = CurrentDb()

SQL = "UPDATE [IS_Reporting Table] " _
& "SET Priority = Priority + 1 " _
& "WHERE Priority >= " & Me.Priority _
& " And Status <> 'resolved' " _
& " And Status <> 'closed' "

db.Execute SQL


That should take care of the situation when you are setting
a priority on a record that did not previously have a
priority.

If you will want to edit the priority on a record that
already has a priority, it will be a different situation,
but I'm not sure if you said you needed to do this.

 

[gst]

What you have outlined would assign a new priority to the new records. Thank
you!

However, it would not take of the records that currently have priorities
assigned. If the currents records are numbered 1 to 10, I may want to assign
the new record a priority of 1. I would need the current records to be
renumbered 2 to 11.

-adding a new record without specifying its priority.
The work orders that have an open status are downloaded into an Excel
spredsheet nightly. The new requests are submitted with no priority level
assigned. I would just do an append query to grab the new records and add
them to MS Access. Once the new records are retrieved that is when the
priorities are assigned.

-deleting an existing record
once the status has changed to resolved or closed i would need to delete
those records. currently i just have the filter set on the status field to
show only the open orders.

-just clearing an existing record's priority
it was my hope that I would be able to clear the existing priority and
update it with a new assigned priority. because i have no experience with
Access programming then I assumed it would not present a problem.

-how to identify other records that should not have their priority adjusted
I have not reached this part of the process. I was assuming that I could
start with just the records that had been reassigned. The records that had
not been reassigned would not need to be changed.

Start with this in the priority text box's AfterUpdate event
procedure:

Dim db As Database
Dim SQL As String

Set db = CurrentDb()

SQL = "UPDATE [IS_Reporting Table] " _
& "SET Priority = Priority + 1 " _
& "WHERE Priority >= " & Me.Priority _
& " And Status <> 'resolved' " _
& " And Status <> 'closed' "

db.Execute SQL


That should take care of the situation when you are setting
a priority on a record that did not previously have a
priority.

If you will want to edit the priority on a record that
already has a priority, it will be a different situation,
but I'm not sure if you said you needed to do this.

 

[Marshall Barton]

What you have outlined would assign a new priority to the new records. Thank
you!

However, it would not take of the records that currently have priorities
assigned. If the currents records are numbered 1 to 10, I may want to assign
the new record a priority of 1. I would need the current records to be
renumbered 2 to 11.

The code I posted is supposed to renumber all the existing
records that have priority >= the new record's priority.

Did you try it? If you did, what, exactly, was wrong with
the result?

 

[gst]

Marshall you are awesome! This works perfect. There was a pilot error before.

I have never coded in VBA before this assignment. This was a great exercise
and I learned how powerful Access and VBA are.

THANKS SO MUCH!!

-adding a new record without specifying its priority.
The work orders that have an open status are downloaded into an Excel
spredsheet nightly. The new requests are submitted with no priority level
assigned. I would just do an append query to grab the new records and add
them to MS Access. Once the new records are retrieved that is when the
priorities are assigned.

-deleting an existing record
once the status has changed to resolved or closed i would need to delete
those records. currently i just have the filter set on the status field to
show only the open orders.

-just clearing an existing record's priority
it was my hope that I would be able to clear the existing priority and
update it with a new assigned priority. because i have no experience with
Access programming then I assumed it would not present a problem.

-how to identify other records that should not have their priority adjusted
I have not reached this part of the process. I was assuming that I could
start with just the records that had been reassigned. The records that had
not been reassigned would not need to be changed.

Start with this in the priority text box's AfterUpdate event
procedure:

Dim db As Database
Dim SQL As String

Set db = CurrentDb()

SQL = "UPDATE [IS_Reporting Table] " _
& "SET Priority = Priority + 1 " _
& "WHERE Priority >= " & Me.Priority _
& " And Status <> 'resolved' " _
& " And Status <> 'closed' "

db.Execute SQL


That should take care of the situation when you are setting
a priority on a record that did not previously have a
priority.

If you will want to edit the priority on a record that
already has a priority, it will be a different situation,
but I'm not sure if you said you needed to do this.

 

[gst]

Marshall-

I'm Back. I tried this query again today and I noticed something that I
didn't notice before.

If I have list numbered from 1 to 5 and I want to reassign priority 5 to
number 2, the result that I am getting is the list is being numbered
1,2,4,5,6. If I run the query again, the result I get is 1,2,5,6,7. So I am
getting gaps in the numbering sequence. I have tried for loops and counter
variables but I am not getting the desired results.

Thanks!

 

[Marshall Barton]

I'm Back. I tried this query again today and I noticed something that I
didn't notice before.

If I have list numbered from 1 to 5 and I want to reassign priority 5 to
number 2, the result that I am getting is the list is being numbered
1,2,4,5,6. If I run the query again, the result I get is 1,2,5,6,7. So I am
getting gaps in the numbering sequence. I have tried for loops and counter
variables but I am not getting the desired results.

That's what I meant when I asked if you would be reassigning
an existing priority. Here's an aircode version that will
do that too:

Dim db As Database
Dim SQL As String
Dim SetCond As String
Set db = CurrentDb()

If Not ((IsNull(Me.Priority.OldValue) _
And IsNull(Me.Priority)) _
Or (Me.Priority.OldValue = Me.Priority)) Then
If IsNull(Me.Priority.OldValue) Then
SetCond = "SET Priority = Priority + 1 " _
& "WHERE Priority >= " & Me.Priority
ElseIf IsNull(Me.Priority) Then
SetCond = "SET Priority = Priority - 1 " _
& "WHERE Priority > " & Me.Priority.OldValue
Else
If Me.Priority.OldValue < Me.Priority Then
SetCond = "SET Priority = Priority - 1 " _
& "WHERE Priority > " & Me.Priority.OldValue _
& " And Priority <= " & Me.Priority
ElseIf Me.Priority.OldValue > Me.Priority Then
SetCond = "SET Priority = Priority + 1 " _
& "WHERE Priority < " & Me.Priority.OldValue _
& " And Priority >= " & Me.Priority
End If
End If
SQL = "UPDATE [IS_Reporting Table] " _
& SetCond _
& " And Status <> 'resolved' And Status <> 'closed'"
db.Execute SQL
End If

 

[gst]

Thanks. This works fine for the existing records.

Now I am getting duplicate priority numbers when a new record is added. I am
importing the new data data from a table. These new records are appended with
no assigned priority. A form is used to assign priorities to the new work
orders and this is where I am using the query that you have outlined below.

I'm Back. I tried this query again today and I noticed something that I
didn't notice before.

If I have list numbered from 1 to 5 and I want to reassign priority 5 to
number 2, the result that I am getting is the list is being numbered
1,2,4,5,6. If I run the query again, the result I get is 1,2,5,6,7. So I am
getting gaps in the numbering sequence. I have tried for loops and counter
variables but I am not getting the desired results.

That's what I meant when I asked if you would be reassigning
an existing priority. Here's an aircode version that will
do that too:

Dim db As Database
Dim SQL As String
Dim SetCond As String
Set db = CurrentDb()

If Not ((IsNull(Me.Priority.OldValue) _
And IsNull(Me.Priority)) _
Or (Me.Priority.OldValue = Me.Priority)) Then
If IsNull(Me.Priority.OldValue) Then
SetCond = "SET Priority = Priority + 1 " _
& "WHERE Priority >= " & Me.Priority
ElseIf IsNull(Me.Priority) Then
SetCond = "SET Priority = Priority - 1 " _
& "WHERE Priority > " & Me.Priority.OldValue
Else
If Me.Priority.OldValue < Me.Priority Then
SetCond = "SET Priority = Priority - 1 " _
& "WHERE Priority > " & Me.Priority.OldValue _
& " And Priority <= " & Me.Priority
ElseIf Me.Priority.OldValue > Me.Priority Then
SetCond = "SET Priority = Priority + 1 " _
& "WHERE Priority < " & Me.Priority.OldValue _
& " And Priority >= " & Me.Priority
End If
End If
SQL = "UPDATE [IS_Reporting Table] " _
& SetCond _
& " And Status <> 'resolved' And Status <> 'closed'"
db.Execute SQL
End If