query to increment record numbers

Hi,

I bet the question below has been answered many times before, but I
have not found an answer. If someone could point me to one that would
be great!

I have the following table:

num run
1 1
2 0
3 0
4 1
5 0
6 1
7 0
8 0
9 0
10 0
....etc...

The records are ordered according to [num], and the [run] field has a
"1" that marks the beginning of a new run and zeros elsewhere.

I want to transform the table to look like this:

num run
1 1
2 2
3 3
4 1
5 2
6 1
7 2
8 3
9 4
10 5
....etc...

Here the zero records in the [run] field are replaced by the value in
the previous record +1. Each time a [run] record in the original table
has a one, the new table's [run] record value begins again with a one.

I am a novice Access user, and I thought I would write a loop something
like the following to make the transformation

while Dmin([run]=0) do the following "update query"

Iif(
DLookUp("[run]","[table]","[num]=" & [num])>0,
DLookUp("[run]","[table]","[num]=" & [num]),
IIf(
DLookUp("[run]","[table]","[num]=" & [num]-1)=0,
0,
DLookUp("[run]","[table]","[num]=" & [num]-1)+1
)
)
end while

The update query which uses the nested Iif statement inside the while
loop, seems to work fine. But it is very slow (on a file with 20,000
records), and my files have run lengths in the 100s, so the loop would
run 100s of times.

Is there are easier/faster/simpler way to do all of this?

Thanks in advance for any help!
Noah

re: query to increment record numbers

Noah wrote:[color=blue]
> Hi,
>
> I bet the question below has been answered many times before, but I
> have not found an answer. If someone could point me to one that would
> be great!
>
> I have the following table:
>
> num run
> 1 1
> 2 0
> 3 0
> 4 1
> 5 0
> 6 1
> 7 0
> 8 0
> 9 0
> 10 0
> ...etc...
>
> The records are ordered according to [num], and the [run] field has a
> "1" that marks the beginning of a new run and zeros elsewhere.
>
> I want to transform the table to look like this:
>
> num run
> 1 1
> 2 2
> 3 3
> 4 1
> 5 2
> 6 1
> 7 2
> 8 3
> 9 4
> 10 5
> ...etc...
>
> Here the zero records in the [run] field are replaced by the value in
> the previous record +1. Each time a [run] record in the original table
> has a one, the new table's [run] record value begins again with a one.
>
> I am a novice Access user, and I thought I would write a loop something
> like the following to make the transformation
>
> while Dmin([run]=0) do the following "update query"
>
> Iif(
> DLookUp("[run]","[table]","[num]=" & [num])>0,
> DLookUp("[run]","[table]","[num]=" & [num]),
> IIf(
> DLookUp("[run]","[table]","[num]=" & [num]-1)=0,
> 0,
> DLookUp("[run]","[table]","[num]=" & [num]-1)+1
> )
> )
> end while
>
> The update query which uses the nested Iif statement inside the while
> loop, seems to work fine. But it is very slow (on a file with 20,000
> records), and my files have run lengths in the 100s, so the loop would
> run 100s of times.
>
> Is there are easier/faster/simpler way to do all of this?
>
> Thanks in advance for any help!
> Noah
>[/color]
You could write a routine. This is aircode. I'll assume "RUN" is indexed

Sub UpdateRunNum
Dim lngCounter As Long
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = ""Select Num, Run From Table Order By Num"
set rst = currentdb.openrecordset(strSQL,dbopendynaset)

If rst.RecordCount > 0 then
'see if there are any records to update.
Do while True
'find the first "0" run
rst.FindFirst "Run = 0"
If not rst.NoMatch Then
'one was found.
'go to previous record and get the counter
'the counter might have been previously update
'and then more zero records addedd so...
rst.MovePrevious
lngCounter = rst!Run
rst.MoveNext
Do While True
If rst!Run = 0 then
lngCounter = lngCounter + 1
rst.Edit
Rst!Run = lngCounter
rst!Update
rst.MoveNext
If rst.EOF Then Exit Do
Else
exit DO
Endif
Loop
Exit DO
Else
Exit DO
Endif
Loop
Endif
rst.close
set rst = Nothing
Msgbox "Done"
End Sub

re: query to increment record numbers

I'll give it a whirl. Thanks!

re: query to increment record numbers

Noah wrote:[color=blue]
> I'll give it a whirl. Thanks!
>[/color]
If you "give it a whirl" please make a backup of the table first. Run
it on the copy (all you need to do is change the table name in the SQL
statement) first and then review the results.

re: query to increment record numbers

Noah, have you thought of reorganizing your table to look like this:

num run
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 3
9 3
10 3

that way, you could keep track of the order within a run by just using the
order of the 'num' field itself, and you could easily query to get just the
records that belong to a particular run.

Also, if you are just wanting to transform your table one time to get a
bunch of data in the shape form you want it, you may find that dumping it
into excel, processing it, and then moving it back into Access may be very
helpful.
-John

"Noah" <gans@wharton.upenn.edu> wrote in message
news:1150562246.189836.35430@h76g2000cwa.googlegro ups.com...[color=blue]
> Hi,
>
> I bet the question below has been answered many times before, but I
> have not found an answer. If someone could point me to one that would
> be great!
>
> I have the following table:
>
> num run
> 1 1
> 2 0
> 3 0
> 4 1
> 5 0
> 6 1
> 7 0
> 8 0
> 9 0
> 10 0
> ...etc...
>
> The records are ordered according to [num], and the [run] field has a
> "1" that marks the beginning of a new run and zeros elsewhere.
>
> I want to transform the table to look like this:
>
> num run
> 1 1
> 2 2
> 3 3
> 4 1
> 5 2
> 6 1
> 7 2
> 8 3
> 9 4
> 10 5
> ...etc...
>
> Here the zero records in the [run] field are replaced by the value in
> the previous record +1. Each time a [run] record in the original table
> has a one, the new table's [run] record value begins again with a one.
>
> I am a novice Access user, and I thought I would write a loop something
> like the following to make the transformation
>
> while Dmin([run]=0) do the following "update query"
>
> Iif(
> DLookUp("[run]","[table]","[num]=" & [num])>0,
> DLookUp("[run]","[table]","[num]=" & [num]),
> IIf(
> DLookUp("[run]","[table]","[num]=" & [num]-1)=0,
> 0,
> DLookUp("[run]","[table]","[num]=" & [num]-1)+1
> )
> )
> end while
>
> The update query which uses the nested Iif statement inside the while
> loop, seems to work fine. But it is very slow (on a file with 20,000
> records), and my files have run lengths in the 100s, so the loop would
> run 100s of times.
>
> Is there are easier/faster/simpler way to do all of this?
>
> Thanks in advance for any help!
> Noah
>[/color]