Renumbering a field value in a set of records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table containing various records identifying items. Each item is
given a sequential number (lets call it a line number). For example, I have
10 records each having a line number field containing the values from 1 - 10.
I want to add a new record but it should logically be placed between lines 5
and 6.

Therefore I need to renumber record 6 - 10 so that the revised line number
field values are 7 - 11, and finally insert my new record with the line
number 6.

I guess this is a simple update query, but I'm not so advanced at the moment.

Can anyone giude me...

many thanks

regards,

Who
 
What are you trying to accomplish by using the sequence numbers? Is this to
help you sort your records? If the "new record ... should logically be
placed" somewhere, what is that logic? Sorted by date? Sorted by ?? How
would you explain to a new person how to decide where a new record
"logically" belongs?

I'm focusing on the WHY before the HOW..., because there may be another way
to get done what you want to, if we only knew what that was...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Ok, brief explanation....

I work in oil tanker conversion. We create a schedule of works (estimated
what is required), and this comprises around 8,000 line items. The work maybe
done, or it may not or there may be new work to be carreid out.

The work follows a logical series of steps, for example...

Primary steelwork
Secondary steelwork
Tertiary steelwork.

Assume I have only allowed for the following in my schedule,

line 50: Primary steelwork - hull repairs
line 51: Tertiary steelwork - access ladders.

At the end of the project we produce an as-built document to reflect the
actual work performed. But let say during the hull repairs, we had to perform
some bracket replacements which are classed as secondary steelwork. This is a
new line and falls between lines 50 and 51. I therefore need to insert the
line item Secondary Steelwork to ensure the correct order for the as-built
document.

You may think wait until the project is complete, but this we cannot do as
the projects are typically 12 ~ 14 months in duration and we update our
documents weekly.

Hence I need a method to renumber the lines to ensure the correct order as
the work progresses. Actually the schedules are very complicated, but this
example simplifies the situation.

regards,

Who
 
I realize you simplified the explanation. You gave me a prescribed order
(primary, secondary, tertiary). If you know what category each of three
"works" belong to, you can use that (instead of line numbers) to sort the
works in their correct order.

If you can extend that approach to your more complicated situation, you
wouldn't need line numbers at all.

I'm wondering if you might not be better off using something that lets you
put things to be done in an order, with interdependencies... i.e., project
management software. If you were using that instead of Access, it would
automatically re-order and re-number when you insert new tasks.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
May I suggest and alternative.

Change your numbering scheme to use numbers with decimals - in Access I
would probably use the currency field if I were using the numbers to link
between tables, otherwise number of type double.. Then you can easily
insert a record with a number between two other numbers without a lot of
complications of renumbering the lines and if the numbers are used to relate
to other records updating all those relationships.

You can then use a ranking query to get an integer number to display or you
can use an update query rebuild the number list if it is necessary (and
feasible).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

believe it or not, that it what I was doing when this work using excel.... I
just thought it be just as easy to automate in Access. Guess I be using the
same method....

regards,

Who
 
It took me a while to get used to the idea that records in Access are not
"in order" in the same way as on a spreadsheet. In Excel you can choose any
order, including an arbitrary one (or one based on unstated criteria). In
Access the records will be ordered by a field's value. If you want a
different order on the page or screen, you need to find or create a field on
which Access can sort to produce the desired result. If the number alone
determines the order, that is the field on which you must sort.
I doubt it applies to your situation, but it is pretty simple to number rows
in a report; however, the number is not associated with a particular record,
but with its position relative to other records. If the numbering
increments by 1, the fifth row is numbered 5 regardless of the actual
record. The number 5 does not become part of the record.
 
Back
Top