Automatic reassigning of numbers

C

Chris Potter

I have a bit of a problem here. I am trying to make an Access database for
my business, and this requires all the people to be numbered like this:

1,2,3,4,5,6,7,8,9,10

The problem is when I have to add someone. I might have to fit the new
person in between two others:

1,2,3,4,(new person),5,6,7,8,9,10

so now the new person becomes 5, 5 becomes 6, 6 becomes 7 etc. Currently I
am stuck with manually doing the renumbering. Does anyone know of a way of
automatically doing this?
 
P

Pete

You can do this with a paramaterized update query. Obviously substitiute the
name of your table and your number field. (I am assuming that yournumberfield
is a numeric field and not an autonumber.

UPDATE yourtable SET yourtable.[yournumberfield] = ([yournumberfield]+1)
WHERE (((yourtable.[yournumberfield])>=[Enter Start Number]));

When you run this query it will prompt you to enter the start number that
you wish to increment from.
 
V

Van T. Dinh

This may or may not work if the Field is a PrimaryKey Field or uniquely
indexed since we don't know the order that JET processes the Records.
 
V

Van T. Dinh

Is this a PrimaryKey Field? Do you have related (Child) Records using this
number as the Link Field?

What do you use this number for? Sorting?

Note that Records are stored in a Table like items in a bucket, i.e. no
inherent order. So whether the new person has number 5 or 11, it doesn't
affect how the Record is stored. If you need to retrieve Records in a
certain order, you need to specify the ordering in the Query.
 
M

Mike Painter

Even if it did work the use of this key as a foreign key would require a bit
of code to update all related records.
Clearly the number is of no value as either a key or a customer number.
I can picture an interesting exchange between a customer who was 100 last
week, 75, the week before that and will be 110 tomorrow and the person
asking for that number.

I work with a group which insists on this and the solution seems to be a
word processor in an outline view. Insert or delete a new line and all the
numbers change.

A simple database, which I've offered to do, would save them at least an
hour every time they use it ( 8:00 - 1:00 every Tuesday) but they have to
have those hand written receipts and use the adding machine... (Hint. At 64
I'm one of the kids in the organazation)
 
V

Van T. Dinh

My other post in this thread checks whether this Field is used in a
relationship or not. Besides if the O.P. used R.I. with Cascade Update,
this should be OK. The intention of changing the PK often is still
incorrect, though.

Talking about resistance to technology, I used to work in a big faimily-run
business (nearly 20 years ago). The sons decided to put in a fancy
telephone with buttons, all sort of facilities, whistles and bells. The dad
in charge (70+) insisted to have just a normal rotary-dial telephone so that
he could simply pick up the handset & dial. He said he used to his card
rotadex so there was no needs for the telephone number memory ...
 
C

Chris Potter

I am employed by a post office, and it is neccesary to deliver the letters
in a particular order. So therefore it would mean chaos if 6 people were to
get letters delivered at the same time.
 
Top