Record numbering problems

[Quicky]

A database I am creating is essentially a list of checks to be performed on an instrument. I would like the primary key to be a field that uniquely numbers each of these checks. The checks themselves are split into sections, e.g. sections 1,2,3,4 etc, and in each section there are some checks, a,b,c,d etc. Therefore any check is identified by, for example, 3a. (In the same way as a question on an exam paper for example).

The problem I am having is this: If at a later date I want to insert a new check (record) in an earlier section, I would want to name it, for example, 2b. If checks 2a, 2b, and 2c are already in existence, then by naming my new check 2b, I would want to insert it between checks 2a and (the old) 2b. I would then want every subsequent check in that section to be automatically incremented, therefore section 2 would now contain 2a,2b,2c, and 2d, (where 2c and 2d are the old checks 2b and 2c). Other subsequent sections (3  onwards) would not have to be incremented, as their names would not be affected.

Any help as to how I would do this would be invaluable.

Thanks,
Quicky

 

[DougP]

It can be done, but isn't there a better way to number your items or NOT number your items so you don't have this problem.

Use something else to sort them with DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Quicky]

The trouble is that the ultimate function of the database is to generate two reports, one which produces a detailed test procedure report (how to perform the checks on the instrument), and the second is essentially a list of the checks with boxes to allow someone to tick off whether the checks have been completed.

Each check has to be specifically numbered in the way described above, or similar way (eg 1.1, 1.2, 1.3), as many checks are performed on one part of a machine, then another.

Numbering them in this way is not a problem, but the addition of extra checks on a certain section that need to be performed are often inculded at later dates, and the whole thing is updated frequently. That is why the numbering system is required, and also where my Access knowledge hits a wall.

If anyone can help me, I'll buy them a pint.

 

[jwm]

The following will work, but I don't know if it's what you're seeking.

Create your document in MS Word using the engineering notation choice, e.g. 1, 1.1, 1.1.1, etc., in "Bullets & Numbering." This will serve as your master document, as any paragraph additions/subtractions will automatically be re-numbered accordingly by Word.

Save the document as a text file.

Import the document into your Access table.

I tested this and it actually worked, which surprised me. I had two fields in the access table...the paragraph number and the text paragraph.

Just guessing, I would think that the only thing you would need to modify would be the modified paragraph when it changes. That is to say, redo paragraph X.x.x in Word, delete all of paragraph X from Access, export the new complete paragraph X from Word to a text file and import it back into Access.

Access will even sort the data back into sequence for you afterward.

HTH.

 

[Quicky]

Thanks a lot for that, I'll give it a go today.

Quicky

 

[MichaelRed]

Well, I'll echo DougP's suggestion of either avoiding the numbering issue altogether or modifying the process to avoid the necessity of changing record Id numbers after the fact. A possible alternative would be to just include a new real 'record id' field, which would consist of a REAL (single data type) number. You would generally include a new record and assign this number as the value 1/2 way between the next and previous records, so as an example, if you have records 1.25 and 1.125 and need to assign a new record to be between them, it is assigned 1.1875. then, develop the routine which simply 'translates' the decimal part into a sequential Numeric.Alpha sequence, with the Alpha starting over each time the Numeric part cycles. A recordset ordered on the new id field would be able to use a simple subquery to determine the previous recored 'published' id and construct te currrent record publishing id.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over