Looping Question

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi All,

Consider the following sample table and values:

Field1 Field2
______ ______
A
A
A
A
B
B
B

I would like to loop through the records so that where the Field1 value is A,
Field 2 values are updated to 1, 2, 3, 4. Where the Field1 value is B, the
Field2 values become 1,2,3.

I'm looking to update a sequence number field in a table with 30,000 records
that follows the above pattern and re-starts the sequence number from 1 when
it hits a new Field1 value.

Thanks for the help.

Greg
 
This kind of thing should do it:

Function MakeSeq()
Dim rs As DAO.Recordset
Dim strSql As String
Dim strPriorValue As String
Dim lngCount As Long

strSql = "SELECT * FROM Table1 WHERE Field1 Is Not Null ORDER BY Field1,
ID;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
If rs!Field1 = strPriorValue Then
lngCount = lngCount + 1
Else
lngCount = 1
strPriorValue = rs!Field1
End If

rs.Edit
rs!Field2 = lngCount
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

I have assumed that you added an AutoNumber field named ID to this table, so
there there is a defined order for each "A" in Field1, etc.
 
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intCurrCount As Integer
Dim strPrevValue As String
Dim strSQL

strSQL = "SELECT Field1, Field2 FROM MyTable " & _
"ORDER BY Field1"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
strPrevValue = vbNullString
Do While rsCurr.EOF = False
If rsCurr!Field1 <> strPrevValue Then
strPrevValue = rsCurr!Field1
intCurrCount = 0
End If
intCurrCount = intCurrCount + 1
rsCurr.Edit
rsCurr!Field2 = intCurrCount
rsCurr.Update
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top