Update query that adds 1 to a previous value

[Dixie]

I have asked this question before, but I could not get the suggested
solution work. So I will give more details this time.

I have an append query that adds several hundred records to a table into a
text field. Next to this text field is a separate Number (Single) field
with a unique ID number for the entry. I want each number to be 1 more than
the previous entry's number to keep it unique (I don't want autonumbering
for this, as I want to be able to change that number in some circumstances)

So, either as a part of the append query, or as a separate update query, I
need to be able to go through each entry that has a value of 0 in that field
(the default value) and find the maximum number in that field, then add 1 to
it. Is this even possible in a simple update query?

If it is not, is there some code I can put into a module and run. The table
I am updating is called tblNew, the text field I am appending is called
Category and the Integer field I want to increment by 1 is called
CategoryID.

Any solutions?

dixie

[Len Robichaud]

There are several issues to be discussed before I could offer the best
solution.
First, you say you want a unique number for each record and that you want
them to be sequentially numbered (add 1 to the previous number) however you
want the flexibility to change the number. What would you change the number
to be and how would you avoid eventually running into that number in the
future?
(By the way, single integer limits you to 32,000 numbers. why not use
Long?)
Since you want the flexibility to change the number suggests to me that the
number is meaningful in some way. What are you using the number for?
There are many ways to come up with a unique ID. Tell us a little more
about what you are doing.

"Dixie" <di***@dogmail. comwrote in message
news:12******** *****@corp.supe rnews.com...

>I have asked this question before, but I could not get the suggested
solution work. So I will give more details this time.

I have an append query that adds several hundred records to a table into a
text field. Next to this text field is a separate Number (Single) field
with a unique ID number for the entry. I want each number to be 1 more
than the previous entry's number to keep it unique (I don't want
autonumbering for this, as I want to be able to change that number in some
circumstances)

So, either as a part of the append query, or as a separate update query, I
need to be able to go through each entry that has a value of 0 in that
field (the default value) and find the maximum number in that field, then
add 1 to it. Is this even possible in a simple update query?

If it is not, is there some code I can put into a module and run. The
table I am updating is called tblNew, the text field I am appending is
called Category and the Integer field I want to increment by 1 is called
CategoryID.

Any solutions?

dixie

[Dixie]

Firstly let me answer the question about the Long - the most entries this
table is ever likely to see is about 3000 - 4000, it will certainly never
approach 32,000, but I take your point.

Basically, the table is full of class names for a K-6 school. Each year,
the classes change name, according to the grade the students are in and the
teacher's initials, so like 2KW, 3AV, 4-5ST. Each class has an accompanying
ID number for uniqueness. It is possible a class name may repeat in another
calendar year. I don't want to delete previous classes, as those classes
are tied to educational records.

Why do I not want to use Autonumber. Firstly, in my experience, autonumbers
will often jump huge amounts of numbers and in some of my databases, they
get so large, they start getting reported as exponential numbers.

I'll admit that right now, I cannot think of a reason to change the numbers
once they are there, but I don't like Autonumbers for the reasons given
above. I just thought it would be easy to add one to the highest ID number
in the table for each new entry. These entries are usually dumped in as one
job lot from the append query at the start of the school year and there is
usually not more than about 60, although it can be more, depending on the
structure of the school.

I have tried to do this and failed. I am now assuming I have to do some
kind of walking through the records routine.

dixie

"Len Robichaud" <le**********@c omcast.netwrote in message
news:6u******** *************** *******@comcast .com...

There are several issues to be discussed before I could offer the best
solution.
First, you say you want a unique number for each record and that you want
them to be sequentially numbered (add 1 to the previous number) however
you want the flexibility to change the number. What would you change the
number to be and how would you avoid eventually running into that number
in the future?
(By the way, single integer limits you to 32,000 numbers. why not use
Long?)
Since you want the flexibility to change the number suggests to me that
the number is meaningful in some way. What are you using the number for?
There are many ways to come up with a unique ID. Tell us a little more
about what you are doing.

"Dixie" <di***@dogmail. comwrote in message
news:12******** *****@corp.supe rnews.com...

>>I have asked this question before, but I could not get the suggested
solution work. So I will give more details this time.

I have an append query that adds several hundred records to a table into
a text field. Next to this text field is a separate Number (Single)
field with a unique ID number for the entry. I want each number to be 1
more than the previous entry's number to keep it unique (I don't want
autonumberin g for this, as I want to be able to change that number in
some circumstances)

So, either as a part of the append query, or as a separate update query,
I need to be able to go through each entry that has a value of 0 in that
field (the default value) and find the maximum number in that field, then
add 1 to it. Is this even possible in a simple update query?

If it is not, is there some code I can put into a module and run. The
table I am updating is called tblNew, the text field I am appending is
called Category and the Integer field I want to increment by 1 is called
CategoryID.

Any solutions?

dixie

[Bob Quintal]

"Dixie" <di***@dogmail. comwrote in
news:12******** *****@corp.supe rnews.com:

[snip 4 lines]

>
Basically, the table is full of class names for a K-6 school.
Each year, the classes change name, according to the grade the
students are in and the teacher's initials, so like 2KW, 3AV,
4-5ST. Each class has an accompanying ID number for
uniqueness. It is possible a class name may repeat in another
calendar year. I don't want to delete previous classes, as
those classes are tied to educational records.

how about doind this: 2006_2KW,2006_3 AV,2006_4-5ST etc.

You could even just use a separate field for the year, and
create a composite primary key based on hte two fields.

Any simple query solution will not work, as all records in the
batch will get the same number.

So we are left with a recordset solution manipulated in VB.
Copy the code below and Paste this into the database, then save
and exit. Make a copy of the .mdb to test. Test in the copy. if
it looks ok, run it in the original .mdb.

public sub SetNewId()
Dim RS as dao.recordset
Dim strSQL as string
Dim iCounter as single 'you really want to change the id number
'to an integer or long int.

strSQL = "SELECT ClassID FROM Classes WHERE ClassID = 0"
icounter = Dmax("ClassID", "Classes")+ 1

set RS= Currentdb.OpenR ecordset(strsql ,dbOpenDynaset)
do until RS.EOF
RS.edit
RS.fields("clas sID") = iCounter
RS.Update
iCounter = iCounter + 1
loop
RS.close
set RS = nothing
End Sub

[snip]
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dixie]

I have done that Bob. Firstly, using a Single number just sends it off into
Oblivion and I have to do a control break to get it back. I then changed
both the field I am working on to Integer and your code to Dim iCounter As
Integer. I then get the error Run-time error '6': Overflow.
The line
iCounter = DMax("FacultyID ", "tblFaculti es") + 1 is highlighted when I take
the debug option.

When I then look in the table, only one value has changed and it has gone
from 0 to 32767

dixie

"Bob Quintal" <rq******@sPAmp atico.cawrote in message
news:Xn******** **************@ 66.150.105.47.. .

"Dixie" <di***@dogmail. comwrote in
news:12******** *****@corp.supe rnews.com:

[snip 4 lines]

>>
Basically, the table is full of class names for a K-6 school.
Each year, the classes change name, according to the grade the
students are in and the teacher's initials, so like 2KW, 3AV,
4-5ST. Each class has an accompanying ID number for
uniqueness. It is possible a class name may repeat in another
calendar year. I don't want to delete previous classes, as
those classes are tied to educational records.

how about doind this: 2006_2KW,2006_3 AV,2006_4-5ST etc.

You could even just use a separate field for the year, and
create a composite primary key based on hte two fields.

Any simple query solution will not work, as all records in the
batch will get the same number.

So we are left with a recordset solution manipulated in VB.
Copy the code below and Paste this into the database, then save
and exit. Make a copy of the .mdb to test. Test in the copy. if
it looks ok, run it in the original .mdb.

public sub SetNewId()
Dim RS as dao.recordset
Dim strSQL as string
Dim iCounter as single 'you really want to change the id number
'to an integer or long int.

strSQL = "SELECT ClassID FROM Classes WHERE ClassID = 0"
icounter = Dmax("ClassID", "Classes")+ 1

set RS= Currentdb.OpenR ecordset(strsql ,dbOpenDynaset)
do until RS.EOF
RS.edit
RS.fields("clas sID") = iCounter
RS.Update
iCounter = iCounter + 1
loop
RS.close
set RS = nothing
End Sub

[snip]
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dixie]

I think I have found it. I just added an RS.MoveNext after RS.Update and it
is working.

Does that make sense?

Thanks again Bob

dixie

"Dixie" <di***@dogmail. comwrote in message
news:12******** *****@corp.supe rnews.com...

>I have done that Bob. Firstly, using a Single number just sends it off
into Oblivion and I have to do a control break to get it back. I then
changed both the field I am working on to Integer and your code to Dim
iCounter As Integer. I then get the error Run-time error '6': Overflow.
The line
iCounter = DMax("FacultyID ", "tblFaculti es") + 1 is highlighted when I
take the debug option.

When I then look in the table, only one value has changed and it has gone
from 0 to 32767

dixie

"Bob Quintal" <rq******@sPAmp atico.cawrote in message
news:Xn******** **************@ 66.150.105.47.. .

>"Dixie" <di***@dogmail. comwrote in
news:12******* ******@corp.sup ernews.com:

[snip 4 lines]

>>>
Basically, the table is full of class names for a K-6 school.
Each year, the classes change name, according to the grade the
students are in and the teacher's initials, so like 2KW, 3AV,
4-5ST. Each class has an accompanying ID number for
uniqueness. It is possible a class name may repeat in another
calendar year. I don't want to delete previous classes, as
those classes are tied to educational records.

how about doind this: 2006_2KW,2006_3 AV,2006_4-5ST etc.

You could even just use a separate field for the year, and
create a composite primary key based on hte two fields.

Any simple query solution will not work, as all records in the
batch will get the same number.

So we are left with a recordset solution manipulated in VB.
Copy the code below and Paste this into the database, then save
and exit. Make a copy of the .mdb to test. Test in the copy. if
it looks ok, run it in the original .mdb.

public sub SetNewId()
Dim RS as dao.recordset
Dim strSQL as string
Dim iCounter as single 'you really want to change the id number
'to an integer or long int.

strSQL = "SELECT ClassID FROM Classes WHERE ClassID = 0"
icounter = Dmax("ClassID", "Classes")+ 1

set RS= Currentdb.OpenR ecordset(strsql ,dbOpenDynaset)
do until RS.EOF
RS.edit
RS.fields("clas sID") = iCounter
RS.Update
iCounter = iCounter + 1
loop
RS.close
set RS = nothing
End Sub

[snip]
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"Dixie" <di***@dogmail. comwrote in
news:12******** *****@corp.supe rnews.com:

I think I have found it. I just added an RS.MoveNext after
RS.Update and it is working.

Does that make sense?

make me stay after class for forgetting the movenext. Yes it
makes perfect sense. it finds the highest number, adds 1 and
puts it into the same place as the last record. since it never
se4es the EOF, it just keeps going until the number is too big
to fit into the field.

>
Thanks again Bob

dixie

"Dixie" <di***@dogmail. comwrote in message
news:12******** *****@corp.supe rnews.com...

>>I have done that Bob. Firstly, using a Single number just
sends it off into Oblivion and I have to do a control break to
get it back. I then changed both the field I am working on to
Integer and your code to Dim iCounter As Integer. I then get
the error Run-time error '6': Overflow.
The line
iCounter = DMax("FacultyID ", "tblFaculti es") + 1 is
highlighted when I take the debug option.

When I then look in the table, only one value has changed and
it has gone from 0 to 32767

dixie

"Bob Quintal" <rq******@sPAmp atico.cawrote in message
news:Xn******* *************** @66.150.105.47. ..

>>"Dixie" <di***@dogmail. comwrote in
news:12****** *******@corp.su pernews.com:

[snip 4 lines]

Basically, the table is full of class names for a K-6
school. Each year, the classes change name, according to
the grade the students are in and the teacher's initials,
so like 2KW, 3AV, 4-5ST. Each class has an accompanying ID
number for uniqueness. It is possible a class name may
repeat in another calendar year. I don't want to delete
previous classes, as those classes are tied to educational
records.

how about doind this: 2006_2KW,2006_3 AV,2006_4-5ST etc.

You could even just use a separate field for the year, and
create a composite primary key based on hte two fields.

Any simple query solution will not work, as all records in
the batch will get the same number.

So we are left with a recordset solution manipulated in VB.
Copy the code below and Paste this into the database, then
save and exit. Make a copy of the .mdb to test. Test in the
copy. if it looks ok, run it in the original .mdb.

public sub SetNewId()
Dim RS as dao.recordset
Dim strSQL as string
Dim iCounter as single 'you really want to change the id
number 'to an integer or long int.

strSQL = "SELECT ClassID FROM Classes WHERE ClassID = 0"
icounter = Dmax("ClassID", "Classes")+ 1

set RS= Currentdb.OpenR ecordset(strsql ,dbOpenDynaset)
do until RS.EOF
RS.edit
RS.fields("clas sID") = iCounter
RS.Update
iCounter = iCounter + 1
loop
RS.close
set RS = nothing
End Sub

[snip]
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from
http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com