renumbering series

[Charles E. Vopicka]

i am not as good with SQL as i should be. my dilemma is simple i have a
form that i load to populate a table. what i need is for the form upon
loading to renumber a treatment number field. this would not be a
problem if all data was entered from the form since i number things
sequentially by default but people will populate the table by hand also.


i was able to make a query to do this but i was going to do a
docmd.runsql when the form loaded and i think i ran in to problems. the
query i designed was in the query designer and used a
dcount(treatment_number,treatment_table,treatment_name = treatment_name
and treatment_number <= treatment_number)


this seemed to work and gave me a good number sequence for each
treatment name. but i have no doubt that there is a better way to do
this directly and i had problems translating it in to SQL that
docmd.runsql would allow.


so i have multiple treatment names with multiple treatment numbers. i
would like to renumber the treatment numbers sequentially from 1 for
each treatment name.


i am sure that this will seem easy for many of you. at least it doesn't
seem like it should be hard to do. i would like to thank you in advance.


p.s. i thought i found an example to do this but i kept getting an
unspecified error

--
Charles E. Vopicka's (Chuck) : (e-mail address removed)

Database Management, GIS Specialist and Research Assistant

Forest Biometrics Research Institute
University of Montana - College of Forestry and Conservation
Missoula, MT 59812
United States of America

Phone:
(406)243-4526
(406)243-4264
(406)549-0647 (Home)

HAVE A NICE DAY (-:

"UNLESS" (The Lorax, by Dr. Seuss)

 

[John Vinson]

what i need is for the form upon
loading to renumber a treatment number field.

If this is a dynamic count which should be renumbered every time you
open the form, then it *should not* be stored anywhere in any table -
just calculate it on the fly. Can you base the form on a Query, with a
calculated field like

=DCount("*", "[tablename]", "[somefield] <= " & [somefield])

where somefield is a field in the recordsource which can be counted on
to be in ascending sort order?

John W. Vinson[MVP]

 

[Charles E. Vopicka]

the reason for renumbering is purely to keep things sequential. so i
have 1, 2, 3, 4, 5 and not an instance of 2, 5, 10, 13, etc. i have to
keep the order that they occur but remove gaps in the series. i am only
worrying about it because of the need to delete some of the sequence and
insert some. and also the problem of users entering data in the table
and not the form.


sorry i wasn't as clear as i should be. as my adviser for my degree
tells me writing is not my strong suit. thanks

what i need is for the form upon
loading to renumber a treatment number field.

If this is a dynamic count which should be renumbered every time you
open the form, then it *should not* be stored anywhere in any table -
just calculate it on the fly. Can you base the form on a Query, with a
calculated field like

=DCount("*", "[tablename]", "[somefield] <= " & [somefield])

where somefield is a field in the recordsource which can be counted on
to be in ascending sort order?

John W. Vinson[MVP]

 

[John Vinson]

the reason for renumbering is purely to keep things sequential. so i
have 1, 2, 3, 4, 5 and not an instance of 2, 5, 10, 13, etc. i have to
keep the order that they occur but remove gaps in the series. i am only
worrying about it because of the need to delete some of the sequence and
insert some. and also the problem of users entering data in the table
and not the form.

If they can enter data in the Table datasheet...


T H E Y S H O U L D N ' T!


What's to stop them from entering 2411 or -446 in the sequence
number???

Again... since this sequential number *can* be calculated on the fly
and not stored, I would suggest that it *should* be.


John W. Vinson[MVP]

 

[Charles E. Vopicka]

i am sorry to be difficult but i guess i am not understanding.


in my mind i have to store the sequential numbers. and not calculate
them on the fly the reason for the numbers is to keep order. i have
tried numbering things on the fly on other things and the order never
comes out right. here is an example of what i am doing.


process step action
shoes 1 put on socks
shoes 2 slide on shoe
shoes 3 tie shoes


then if someone were to delete step 1 because they don't like socks
(can't say i blame them) i would be left with.


process step action
shoes 2 slide on shoe
shoes 3 tie shoes


i would want it to then be


process step action
shoes 1 slide on shoe
shoes 2 tie shoes


or a user could put in a record and or modify another record then


process step action
shoes -243 put on socks
shoes 2 slide on shoe
shoes 799 tie shoes


i would then want to end up with the original example above


my experiences thus far of trying to number on the fly have sometimes
come out


process step action
shoes 1 slide on shoe
shoes 2 tie shoes
shoes 3 put on socks


and that doesn't make sense. i need to maintain the order but i want to
renumber them so that they will always start from 1. i am open to
better ways i just can't think to them. i am always looking to learn.


thanks for the time.

 

[John Vinson]

and that doesn't make sense. i need to maintain the order but i want to
renumber them so that they will always start from 1.

Two ways: one dynamic, the other (redundantly) storing the sequence,
come to mind.

1. SELECT Process, DCount("*", "[tablename]", "[Step] <= " [Step]) AS
SEQ, Action FROM [tablename] ORDER BY Step;

will return values 1, 2, 3 so long as the Step field is monotonically
ascending. The numbers in Step need not be consecutive - it will work
just as well regardless of any gaps.

2. To renumber and store the values, use an Update query calculating
the sequence number (again) using DCount:

UPDATE tablename
SET Step = DCount("*", "[tablename]", "[Step] <= " & [Step]);


John W. Vinson[MVP]