Results 1 to 9 of 9

Thread: Count records

  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: Count records

    Hi,

    I'm trying to write some code in Access 2000. What i'm trying to do is count the number of records and update the coloum (Number). At the moment the column Number is blank. (I have just put in test data as example.) What i want to do is count the number of records and update the number coloum in sequence order, where the Name colum and date column are the same. If they are not the same then it will start at 1 again and carry on until it has gone through all the records.


    Name_____Date___________Number
    H_______ 01/01/2005___________1
    H_______01/01/2005___________2
    H_______01/01/2005 ___________3
    H_______23/01/2005___________1
    S_______01/01/2005___________1
    S_______23/01/2005___________1
    S_______30/01/2005___________1
    S_______30/01/2005___________2

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Hemish
    Hi,

    I'm trying to write some code in Access 2000. What i'm trying to do is count the number of records and update the coloum (Number). At the moment the column Number is blank. (I have just put in test data as example.) What i want to do is count the number of records and update the number coloum in sequence order, where the Name colum and date column are the same. If they are not the same then it will start at 1 again and carry on until it has gone through all the records.


    Name_____Date___________Number
    H_______ 01/01/2005___________1
    H_______01/01/2005___________2
    H_______01/01/2005 ___________3
    H_______23/01/2005___________1
    S_______01/01/2005___________1
    S_______23/01/2005___________1
    S_______30/01/2005___________1
    S_______30/01/2005___________2
    Well let's see what you've got ... This is real easy to do ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Record Count

    I have been thinking about it and trying to figure it out for the past couple of hours and it is not working.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well what is the purpose for this anyways???? Start showing what you've done also ... A way to do this is to build a query and iterate thru the recordset adjusting your count as you go ... Are you trying to save this? If so, NOT a good idea ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Record Counts

    Hi below is the code which i have written so far. Its for a system, i'm building showing how many order we get in for each company on different dates. if we have more then 1 order then it will start counting number of records for the same company for that same day.



    Dim db as Database
    Dim rst as Recordset
    Dim Num = Integer
    Dim tot_numb as Integer
    ORD_NUMB as Ingeger
    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("WATFORD_AUTO_shop_head")


    rst.MoveLast
    rst.MoveFirst
    tot_numb = rst.RecordCount
    NUM = 1



    Do Until NUM > tot_numb
    ORD_NUMB = 1
    With rst
    .Edit
    !h_prim = ORD_NUMB
    .Update
    End With
    rst.MoveNext
    NUM = NUM + 1

    Loop
    rst.Close
    Set dbs = Nothing

    End Sub

  6. #6
    Join Date
    Feb 2005
    Posts
    333
    Do you really need a number for each entry as you show in your example or do you WANT a count of the jobs per day for each customer? If you want the latter here is the SQL.

    Code:
    strSql = SELECT test1.name, test1.date, Count(test1.date) AS [Number]
                FROM test1
                GROUP BY test1.name, test1.date;

  7. #7
    Join Date
    Jan 2002
    Location
    London
    Posts
    73
    I need a number for each entry.Counting how many records in a group is very simple to do.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Hemish,

    I'll tell you where you went wrong ... You need to capture your differentiating criteria to test when you have a condition change (for setting/resetting your count) ... You do not have any of that with the code you provided.
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Thanks for your help. Finally it works

    Mark, thanks for your help below is the code that worked.

    Public Sub CountRecords()
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strCurName As String
    Dim strPrevName As String
    Dim dtmCurDate As Date
    Dim dtmPrevDate As Date
    Dim cI As Integer

    ' Set our connection
    Set con = Application.CurrentProject.Connection

    ' Set our source (You need to change the table name to your table name)
    strSQL = "SELECT cod, dates, Number FROM sales ORDER BY cod, dates;"

    ' Open our recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, con, adOpenKeyset, adLockPessimistic

    With rst
    ' Set the first row as the previous and update it with 1 since it is the first
    If Not .EOF Then
    cI = 1
    !Number = cI
    strPrevName = !cod
    dtmPrevDate = !dates
    .Update
    .MoveNext
    End If

    ' If more exist, check them against the last row.
    ' If they are the same, add one to the counter
    ' If they are different, reset the counter to 1
    '
    ' Update the row and set the current as previous.
    ' Repeat until no rows remain
    While Not .EOF
    strCurName = !cod
    dtmCurDate = !dates

    If (strCurName = strPrevName) And (dtmCurDate = dtmPrevDate) Then
    cI = cI + 1
    Else
    cI = 1
    End If

    strPrevName = strCurName
    dtmPrevDate = dtmCurDate

    !Number = cI
    .Update
    .MoveNext
    Wend

    .Close
    End With

    Set rst = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •