counting records in a table....

[chainedtodesk]

i have an access table with the following information (customer number / item number) i need to read the table and do a sequencial count of each customer/item combination 1-?? and then restart at 1 for the next combination, and so on. anything i have tried either runs from 1-99999 without restarting or just gives me the total count of all records, can this be done????? thanks RK

 

[rbinnington]

RK,

Dim db As Database
Dim rs As Recordset
Dim cust As Integer
Dim item As Integer
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)

cust = 0
item = 0
i = 1
While Not rs.EOF
If rs("Customer") <> cust Or rs("Item") <> item Then
i = 1
End If

rs("FieldName") = i
cust = rs("FieldName")
item = rs("Item")

rs.MoveNext
Wend

Hope this helps,
Richard

 

[chainedtodesk]

Richard, thanks that helps alot, can this actually update a field in the access table with the counter number?? i have the customer/item fields and a blank field called count, can this blank field be updated with the sequence number?
thanks

 

[rbinnington]

Yes, but I actually forgot a couple of lines int he code

rs("FieldName") = i

Should be

rs.Edit
rs("Count") = i
rs.Update

This will put the number i into the field Count

 

[chainedtodesk]

sorry , but this one is really confusing me. i tried the above and got some errors, changed it to match a few other modules i have written, i can get one record to update but none of the others, i need each occurance on a matching record to update 1,2,3 etc (examples attached) thanks for all the help

my access table:

cust ACCP custaccp counter would like
001388 TRS 001388TRS 1
001388 CMU 001388CMU 1
001388 CMU 001388CMU 2
001388 CMU 001388CMU 3
001388 CMU 001388CMU 4
001388 CMU 001388CMU 1 5
001396 LCC 001396LCC 1
001396 CMU 001396CMU 1
001396 CMU 001396CMU 2

my currnet code:

Sub CountX()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim ca As Variant
Dim ct As Variant
Dim i As Integer

Set db = CurrentDb
ca = "Select custaccp from tblCusts"
Set rs1 = db.OpenRecordset(ca)
ct = "Select counter from tblCusts"
Set rs2 = db.OpenRecordset(ct)
i = 1

While Not rs1.EOF
If rs1("custaccp") <> custaccp Then
i = 1
End If

rs2.Edit
rs2("counter") = i
rs2.Update

rs1.MoveNext
Wend

End Sub

 

[snotmare]

Hello chainedtodesk!

You have the right idea with your code, but I would make the following changes:

Sub CountX()
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim i As Integer
    Dim strHold As String
    
    Set db = CurrentDb
    SQL = "Select custaccp, counter from tblCusts order by custaccp"
    Set rs = db.OpenRecordset(SQL)
    i = 1
    
    While Not rs.EOF
        If rs("custaccp") <> strHold Then
            i = 1
            strHold = rs("custaccp")
        End If
        
        rs.Edit
        rs("counter") = i
        rs.Update
                
        rs.MoveNext
    Wend
End Sub

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[chainedtodesk]

thanks snotmare, that did go through and add the number 1 to each record, but what would i need for it to do the incremental count for all that are the same in that custaccp field???? thanks again

 

[snotmare]

Oh sorry, I forgot to add the increment statement.

Add "i = i + 1" in your code here...

    While Not rs.EOF
        If rs("custaccp") <> strHold Then
            i = 1
            strHold = rs("custaccp")
        End If
        
        rs.Edit
        rs("counter") = i
        rs.Update
                
        rs.MoveNext
        i = i + 1
    Wend

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[chainedtodesk]

many thanks to rbinnington and snotmare for all of their help, this works great... and as always a great earning experience

 

[rbinnington]

I just ran this on a table like you specified. The problem was that you need to specify only to reset the i to 1 when the customer field changes, not the combination of the two fields.

Let me know if this works!

Sub CountX()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim ca As Variant
Dim ct As Variant
Dim i As Integer
Dim cust As String
Dim accp As String

Set db = CurrentDb
ca = "Select cust, accp, counter from tblCusts"
Set rs1 = db.OpenRecordset(ca)
i = 1

While Not rs1.EOF
If rs1("cust") <> cust Then
i = 1
End If



rs1.Edit
rs1("counter") = i
rs1.Update

cust = rs1("cust")
accp = rs1("accp")
i = i + 1
rs1.MoveNext
Wend

End Sub

Richard

 

[rbinnington]

Sorry, I got it wrong again. Give me a minute.

 

[rbinnington]

never mind