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]