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

[jon steele]

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

Now, there is an option to allow the user to delete a record. Say they
delete record with id '2'. The table looks like this:

id    |   field1   |    field2
 1    |      a     |       b
 1.1  |      a1    |       b1
 3    |      e     |       f
 3.1  |      e1    |       f1
 3.2  |      e2    |       f2
 4    |      g     |       h

BUT, I need the table to look like this after they delete a record:

id    |   field1   |    field2
 1    |      a     |       b
 1.1  |      a1    |       b1
 2    |      e     |       f
 2.1  |      e1    |       f1
 2.2  |      e2    |       f2
 3    |      g     |       h

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.

__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/