Re-sequencing a numeric field

[Anthony Viscomi]

I have a dillemma; my client is a cabinet maker and the database
requirements that he initially supplied to me were vague. Until a majority
of my development was done. Here is what he has "sprung" on me.

I have a table with a numeric field named "Position" this field can contain
a whole number (1, 2, 3, etc) which represents the position that a cabinet
would be placed within a row of cabinets. The field can also contain a
decimal (1.1, 2.3, 3.1, etc.) the decimal value represents any
accessory/add-on for the cabinet in the referenced position.

My dillemma is that if the user decides they want to add a position
(cabinet) I need a way of resquencing all of the subsequent positions
(cabinets). In other words; there may be a position number 2 along with
various add-ons 2.1, 2.2 and so on. The user decides they want another type
of cabinet for position 2, thus they create another position 2. I need a way
for everything that follows to be re-ordered.

Any thoughts and help would be great service; I'me stumped!

Thanks in advance!
Anthony

 

[Andi Mayer]

I have a dillemma; my client is a cabinet maker and the database
requirements that he initially supplied to me were vague. Until a majority
of my development was done. Here is what he has "sprung" on me.

I have a table with a numeric field named "Position" this field can contain
a whole number (1, 2, 3, etc) which represents the position that a cabinet
would be placed within a row of cabinets. The field can also contain a
decimal (1.1, 2.3, 3.1, etc.) the decimal value represents any
accessory/add-on for the cabinet in the referenced position.

My dillemma is that if the user decides they want to add a position
(cabinet) I need a way of resquencing all of the subsequent positions
(cabinets). In other words; there may be a position number 2 along with
various add-ons 2.1, 2.2 and so on. The user decides they want another type
of cabinet for position 2, thus they create another position 2. I need a way
for everything that follows to be re-ordered.

Any thoughts and help would be great service; I'me stumped!

Thanks in advance!
Anthony

With SQl it would:
Add 0.1 to all MyField which are greater or equal StartValue and the
Integer is integer of StartValue

UPDATE MyTable SET MyField=MyField+0.1 WHERE MyField>= StartValue AND
int(MyField)=int(StartValue)

insert StartValue in the table again
INSERT INTO MyTable (MyField) VALUES (StartValue)

 

[Anthony Viscomi]

I intend on placing this SQL to run on a "click event" for a cmd button, but
I am not familiar with the syntax for running the SQL that you've provided
to me. Can you please help?

Thanks!
Anthony

 

[Andi Mayer]

I intend on placing this SQL to run on a "click event" for a cmd button, but
I am not familiar with the syntax for running the SQL that you've provided
to me. Can you please help?

test this on a copy of your database!!!!!!!!!!!!

myField is probably Position
MyTable is your table name
Mybutton is your command button

sub Mybutton_Click
dim StartValue as Double
StartValue=Me!MyField

currentdb.execute "UPDATE MyTable SET MyField=MyField+0.1" _
&" WHERE MyField>=" & StartValue &" AND int(MyField)=" _
& int(StartValue)
currentdb.execute "INSERT INTO MyTable (MyField) VALUES (" _
&StartValue &")"

end sub