how to do a cumulative count

quacka

Registered User.
Local time
Today, 21:08
Joined
May 16, 2007
Messages
16
I think this is a very simple question, that (I think) is asked a lot before,but I don't know the correct search terms... :confused:. The searchresults I get are to complexed to understand for me (at this moment): I first need to start simple with this.

I have recordset that has the following fields:

Name number

Example:
Name - number
Bla - 1000
bla - 1005
bla - 1100
blo - 1200
blo - 1210
blu - 900
blu - 950
blu - 960

What do I want as result?
Name - number - count
Bla - 1000 - 1
bla - 1005 - 2
bla - 1100 - 3
blo - 1200 - 1
blo - 1210 - 2
blu - 900 - 1
blu - 950 - 2
blu - 960 - 3

So: If 'Name' is a new value, 'count' should start with '1'
If 'Name' is the same, count should be '1 + 1' etc.

Can someone help me how to do this? Or give me the search terms I should use.
 
Have a search for "Running sum" or "Running count" dont know if the second will work or not.

Either should return some examples that you can addapt to your requirements.
If you run into problems, post back here and we will see what we can do to resolve the issues.

Good luck!
 
Have a search for "Running sum" or "Running count" dont know if the second will work or not.

Either should return some examples that you can addapt to your requirements.
If you run into problems, post back here and we will see what we can do to resolve the issues.

Good luck!

Running sum is no standard for queries, because I searched and searched for a running sum solution (for another database).

But I already created a VBA-code that does the trick. In the 'locals-windows' I can see the correct data (when I halt before looping)

My only problem now is to write the result to a table.
But I think that I need to do this after every line and not at the end.
 
why write this to a table? The number is liable to change is it not??

No "running sum" is NOT part of any default part of any package in Access. It is done via your own function .... yes in VBA.
 
Yes, I tried earlier a 'running sum'-function, that I found on the Internet. I tried it, but did get strange results. So after a while I stopped trying.

But my vba-code now works. In a few seconds I have the results in a table. It's a temp-table, so I will empty it after use. It's only to give someone the option to mass-update data outside access (in an easy way).

But still thanks for trying to help!
 
Quacka,

Could you post your VBA code. Always interested in working solutions.

Thanks in advance.

k
 
Quacka,

Could you post your VBA code. Always interested in working solutions.

Thanks in advance.

k
This does the trick (blue text is the real code: the rest isn't so interesting)

Code:
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim strSQL As String
    Dim strSQL3 As String
    Dim rstTMP As DAO.Recordset
    Dim rstGRP As DAO.Recordset
    Dim count0 As Double
    Dim Nam00, Nam01 As String


    strSQL = "SELECT blablabla :p;"
    strSQL3 = "tableX"

    Set rstTMP = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Set rstGRP = dbs.OpenRecordset(strSQL3, dbOpenDynaset)

[COLOR="Blue"]
        With rstTMP
        Do While Not .EOF
        Nam00 = rstTMP!Name 
        If (Nam01 = Nam00) Then
        'name is equal
        count0 = (count0 + 1)
        Else
        'other name:
        count0 = 1
        End If
        Nam0 = rstTMP!Name
            With rstGRP
                .AddNew
                !Name = rstTMP!Name
                !Count = count0
                'And so on for all fields...
                .Update
            End With
        
        .MoveNext
        Loop
        End With[/COLOR]

        Set rstTMP = Nothing
        Set rstGRP = Nothing
        Set dbs = Nothing
 
Why dont you indent your "With" and "IF" parts of the code???
 
I forgot to change it :)
I should do it automatically instead of changing it afterwards: It will be easier to read the code when I need to change something in the future.
 
One more thing I just noticed...

Dim Nam00, Nam01 As String

This doesnt declare Nam00 as String.... but leaves it as the default Variant.
To declare it as string you have to do:
Dim Nam00 As String, Nam01 As String

A common mistake that I used to make as well, as it seems logical that both must be strings.
Since I have changed from this syntax to simply making sure to declare each variable on each seperate line.
 

Users who are viewing this thread

Back
Top Bottom