Update Query to Generate Sequential Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a query that will update a field in every record beginning
with a starting number and then incrementing by one. I have not found a
question/response that does quite what I want.
 
How do you define the starting number? What is the ordering sequence that
you'll use? Will you update one record at a time? Or do you want to update
all the records at one time? What have you tried that didn't work so far?
What have you found that you believe won't work for your needs?
 
I have done this already in code, but I still would rather do it in a query.
To answer your questions:

1. Starting number will always be 1.
2. Order sequence will be 1, 2, 3, 4, etc.
3. All records should be updated at one time.
4. I tried using a global variable and a function that added 1 to the global
variable. What I got was the first record updated and then an error saying
the rest could not be updated because it would create duplicate entries in an
index (the field is indexed -- no duplicates).
5. With respect to what I found in my search, I either was using
inapproriate keywords, or the explanations just did not make sense to me.

I remember somewhere in the past seeing something about using a query like I
used and somehow tricking the funciton to run more than jsut the once it
seems to run, but I cannot put my fingers on it.

So, rather than take someone down the rat hole of trying to make what I did
work in whatever convoluted way it was possible, I just kept my question
simple. How do you create an update query that will update a field with
sequential numbers?
 
OK - a three-step process:

(1) Create a query that will order the table's data in the correct sequence.
Let's call this qryOrdering for our example here. This query is needed so
that we can get the records in the desired order for the sequential
numbering to be applied.


(2) Create a public function (let's call it GetSeqNumber) in a regular
module:

Public Function GetSeqNum(varValue As Variant) As Variant
Static lngNum As Long
lngNum = lngNum + 1
GetSeqNum = lngNum
End Function


(3) Create an update query (let's name it qryUpdating) that looks like this:
UPDATE qryOrdering
SET SequentialFieldName =
GetSeqNum(qryOrdering.AnyFieldName);


When you run qryUpdating, it should do what you seek. Note that you can run
this query only once while the file is open because the lngNum variable in
the function will retain the last value, and does not start at 0 when the
query is run each time. If you want to be able to "reset" the lngNum
variable, you'd need to modify the function to have an argument that tells
the function to reset the variable instead of incrementing it, and then run
the function from the VBE Immediate Window with that argument:

Public Function GetSeqNum(varValue As Variant, _
Optional blnReset As Boolean = False) As Variant
Static lngNum As Long
If blnReset = True Then
lngNum = 0
Else
lngNum = lngNum + 1
End If
GetSeqNum = lngNum
End Function


from Immediate Window:
?GetSeqNum(1, True)
0
 
Back
Top