[thelist] ColdFusion and Access - auto-ID update..??

[Joshua OIson]

This should not me too tough.  You'll have to run an update after the
delete.  You *could* do this in a delete trigger, but you should first get
it working without a trigger.

To make this easier, you may consider dropping the decimal field for two
fields, an id_major field, which contains the unit portion of the id, and an
id_minor, which contains the decimal portion.  Now you will have to do two
additional updates after a delete:

UPDATE table
SET id_minor = id_minor - 1
WHERE id_major = *id_major of the one just deleted*
   AND id_minor > *id_minor of the one just delete*

UPDATE table
SET id_major = id_major - 1
WHERE id_major > *id_major of the one just deleted*

Btw, I am assuming that the ordering is 3.8, 3.9, 3.10, 3.11, as opposed to
true decimal ordering which would not differentiate between 3.1 and 3.10
etc.

If this assumption is true, then we can turn these two updates into
triggers.  Otherwise, this technique won't work at all.

-joshua

----- Original Message -----
From: "jon steele" <jjsteele22 at yahoo.com>
To: <thelist at lists.evolt.org>
Sent: Monday, May 07, 2001 4:34 PM
Subject: [thelist] ColdFusion and Access - auto-ID update..??


> Hello,
>
> I have a table with several records. Each has an id field, as well as
> other fields. The id can also be decimal, sort of like a 'sub id'.
> So...something like this:
>
> id    |   field1   |    field2
>  1    |      a     |       b
>  1.1  |      a1    |       b1
>  2    |      c     |       d
>  3    |      e     |       f
>  3.1  |      e1    |       f1
>  3.2  |      e2    |       f2
>  4    |      g     |       h
>

<snip>

> Do you see what I'm saying? I need the ids to change to account for the
> deleted record, so the id's are consecutive, with no gaps. Also, the
> decimals need to be updates. Please tell me there is an easy way to do
> this (like an auto-update in Access or something).
>
> Anyone's input is appreciated.
>
> Thanks,
> Jon.