Automatic renumbering

[Tom Bock]

I am in need to replicate an MS-Word "Outline" structure in MS-Access.

The below is a sample structure that I need to create and be able to
automatically update.

1
1.1.
1.1.1
1.1.2
1.2
1.2.1
1.2.2
2
2.1.
2.1.1
2.1.2
2.1.3
2.2
2.2.1
2.2.2
2.2.3

Essentially, I need to be able to INSERT rows -- let's say between 2.1.2 &
2.1.3. If done, I will create a "new" 2.1.3 task and automatically update
the existing 2.1.3. to 2.1.4.

My challenge is that I don't know how to overcome the limitation of "single
period" for data types that are number. As of now, these values are of
text type which will allow me to enter any character like multipe "dots".

At the same time, I cannot do any calculation to a text type.

Does anyone know of a workaround or nice solution in Access?

Thanks,
Tom

 

[Adrian Jansen]

Two possibles.

Hold the sequence numbers as text in one field, like: 2.4.1
You can then pull them apart into single string bits using the Split
function, and the Val function to turn the individual strings into numerics.

Hold the numerics as numbers in a table with one field for each group (
assuming you never go more than a few groups deep ), then combine them for
display like txtGroup = G1 & "." & G2 & "." & G3
Where G1 etc are the names of the fields in your table.

Either way you will have to do some manipulations in code to renumber groups
as you insert numbers.

There may be better ways of handling this. Database structures really do
not cater for handling changes to sequences, they rely on a record having
all its fields "belonging" to a fixed entity, whereas what you are doing is
changing part of the entity - the grouping number, and still relating it to
a fixed item - the piece of text referred to.
Technically, the data you are dealing with breaks first normal form, because
the rows are not independent.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control