Help with adding a calculated field to a table?

[bpneary1]

First of all, I want to thank everyone who posts messages in this
group. I am very new to VB and developing full-blown database apps,
and I have learned a great deal by reading these posts, so I am very
grateful.

I have a problem that I have been working on, and I was wondering if
anyone had a simple solution.

I have a table of data similar to the example shown below:

TextField NumberField
Txt1 1
Txt2 2
Txt3 3
Txt4 1
Txt5 2
Txt6 3
Txt7 4
Txt8 1
Txt9 2
etc.

I would like to add a calculated field to the table that starts with
the number 1 for the first series of data 1-3, ratchets up to the
number 2 for 1-4, and the to the number 3 for the third set of numbers,
so my final table would be:

TextField NumberField Calculatedfield
Txt1 1 1
Txt2 2 1
Txt3 3 1
Txt4 1 2
Txt5 2 2
Txt6 3 2
Txt7 4 2
Txt8 1 3
Txt9 2 3
etc.

My actual dataset in the number field would be sequences of numbers
starting with 1 and going up to either 6, 7, or 8.

Any ideas? Thanks in advance for the help.

[Bob Quintal]

"bpneary1" <bp******@yahoo.com> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

First of all, I want to thank everyone who posts messages in
this group. I am very new to VB and developing full-blown
database apps, and I have learned a great deal by reading
these posts, so I am very grateful.

I have a problem that I have been working on, and I was
wondering if anyone had a simple solution.

I have a table of data similar to the example shown below:

TextField NumberField
Txt1 1
Txt2 2
Txt3 3
Txt4 1
Txt5 2
Txt6 3
Txt7 4
Txt8 1
Txt9 2
etc.

I would like to add a calculated field to the table that
starts with the number 1 for the first series of data 1-3,
ratchets up to the number 2 for 1-4, and the to the number 3
for the third set of numbers, so my final table would be:

TextField NumberField Calculatedfield
Txt1 1 1
Txt2 2 1
Txt3 3 1
Txt4 1 2
Txt5 2 2
Txt6 3 2
Txt7 4 2
Txt8 1 3
Txt9 2 3
etc.

My actual dataset in the number field would be sequences of
numbers starting with 1 and going up to either 6, 7, or 8.

Any ideas? Thanks in advance for the help.

It's a little late to start thinking about this after the data
is in hte table, because no database will guarantee the order of
records that will be returned from a query.

Is there any other field that's common to all rows in any one
set? you could use that as a starter. add the numbers there,
then go back and insert the numbers in between

You could also open the recordset, and using a do loop, proceed
through the table, incrementing a counter when NumberField = 1
dim rs as recordset
dim lGroupCounter as long
SET rs = currentdb.openrecordset("tablename")

Do Until rs.EOF
if rs!Numberfield then lGroupCounter = lGroupCounter +1
rs.edit
rs!Calculatedfield = lGroupCounter
rs.update
rs.movenext
loop
rs.close
SET rs = nothing

--
Bob Quintal

PA is y I've altered my email address.

[bpneary1]

Bob,

Thanks for the help. I failed to mention that immediately prior to
running the code that you included in your post, I am importing the
data from a text file, therefore it comes into the database in the
proper order, and I immediated add that calculated field. I just tried
it out and it works great. Thanks again.

[Larry Linson]

"bpneary1" <bp******@yahoo.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

Bob,

Thanks for the help. I failed to mention that immediately prior to
running the code that you included in your post, I am importing the
data from a text file, therefore it comes into the database in the
proper order, and I immediated add that calculated field. I just tried
it out and it works great. Thanks again.

The point to be made is: records in relational DB tables is, by definition,
_un_ordered. So, unless there is a key or indexed field(s) to ensure the
order of the records when they are retrieved, you can't _count_ on the
calculated field.

Relational tables are not sequential data sets, and that appears to be the
assumption for the calculation you desire.

Larry Linson
Microsoft Access MVP

[Bob Quintal]

"Larry Linson" <bo*****@localhost.not> wrote in
news:JIccg.4067$oa3.3702@trnddc08:

"bpneary1" <bp******@yahoo.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

Bob,

Thanks for the help. I failed to mention that immediately
prior to running the code that you included in your post, I
am importing the data from a text file, therefore it comes
into the database in the proper order, and I immediated add
that calculated field. I just tried it out and it works
great. Thanks again.

The point to be made is: records in relational DB tables is,
by definition, _un_ordered. So, unless there is a key or
indexed field(s) to ensure the order of the records when they
are retrieved, you can't _count_ on the calculated field.

Relational tables are not sequential data sets, and that
appears to be the assumption for the calculation you desire.

Larry Linson
Microsoft Access MVP

About the only time I use an autonumber is when I encounter this
situation. I import to a temp table, that has an autonumber ID.
I then process the dataset and move the stuff to the permanent
table.

--
Bob Quintal

PA is y I've altered my email address.