Calculate Running Total in a New Field

squishking1
squishking1 used Ask the Experts™
on
I am trying to calculate the running sum and saving the results in a new field either in the same table or a new one.  

To help explain, I've attached sample DB with the original table (tblTestData) and the desired output (Desired Output).  Basically - for each change in field PARE, calculate the running sum QTY_ONHAND - REQ_QTY  and store in field called Balance.

The table is sorted by PART and DUE_DATE.

I've also attached Excel file with function logic used to calculate the desired results.  Looking for something similar to do in Access.

Prefer a solution using queries/macros as I am not very strong w/VBA but could manage if it is not too complicated.

Thanks.




Desired-Result.xlsx
TestDB3.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Usually you don't save the calculated field in the table,
you can perform the calculation on the fly,
so in the Form if you have a textbox control called txtBallance, then this is how you show the result:

on the txtBallance Control source write this:
=[QTY_ONHAND] - [REQ_QTY]

if you want to save this result in your table, then make a field, call it Ballance,
then in the Form, place this field (it can be hidden too), and have this code on "Afterupdate" Event of the field PARE (or maybe you meant PART):
me.Ballance =[QTY_ONHAND] - [REQ_QTY]

you can make an update query t do this too, in which you will have to refer to a unique key of the record from your Form, then update the field Ballance.

jaffer

Author

Commented:
Thanks for the response.  I do have a typo - should be PART, not PARE.

I do need to save the calculaton as I will be using it in a subsequent query in a Macro.  I am not using any form(s) so it would be best to simply use a query (or VBA code if necessary) to do the calculation and save in the new field - if possible.  It is fine to create a new table with the original data + new field called BALANCE or use an update query on the current table.

Appreciate the help.

Commented:
Using the Tables, you cannot save after you change the field PART (well, I read that Access 2010 have this capacity which is similar to SQL server, but not earlier Access versions),
so you will have to manually run the query.

it's easiest to save in the same table, using an update query.

1. Creaye a new field in the Table tblTestData, call it Ballance, DataType is numer, save the Table.
2. Please see the update query in the code box below, and save the query as qry_Update_Ballance_tblTestData.

jaffer
UPDATE tblTestTable SET Ballance = [QTY_ONHAND]-[REQ_QTY];

Open in new window

Commented:
are you having a problem implementing the query?
OR you prefer updating the other table?

jaffer
Hamed NasrRetired IT Professional

Commented:
I prefer a smaller sample database, with few records and list the required few output records.

Author

Commented:
Thanks.  This works but it is not providing me with the running total for PART.   I can do a calculation between fields of a record just fine.  I need help in determining when the PART changes to reset and begin a new running totoa.

I believe a loop of some sort is needed to check whether PART is the same.  Performing this with VBA is fine if someone can provide me the code.

I've updated an excel file with the before and after (desired output) results to better explain.

Thanks.


Desired-Result.xlsx

Author

Commented:
Attached is a smaller sample file.
Desired-Result.xls
Hamed NasrRetired IT Professional

Commented:
Try this. Run Form1
Query1 calculates the Balance, and DLookUp fills the Balance Field on Form1.
TestDB3.accdb

Author

Commented:
Thanks but noticed Balance is not being changed when DUE_DATE does not change.  DUE_DATE should not play a factor.  Only a change in PART should result in change in Balance.

I tried changing the query expression by removing the reference to DUE_DATE but it still does not work properly.  Example of my change below.


Balance: (tblTestTable.QTY_ONHAND-(Select sum(x.REQ_QTY) FROM tblTestTable x Where x.PART=tblTestTable.Part))


Thanks.
Commented:
I think I know what you want:

1. Check if the field PART running sum (see the note below *** )is the same as before,
2. If it is different, than do the BALLANCE calculation.
3. Update the new PART sum

*** The only way we can do this is, by saving a value for PART in a seperate table, tbl_PART for example,
there will be only ONE record in this table, the PART old value (from the last PART sum),

for this, we need to have:
1. tbl_PART (as mentioned up),
2. a code in the Form to check if the NEW running sum of PART from tblTestData , is the same as the old PART in tbl_PART
3. if it is different then:
4. Update BALLANCE in tblTestData
5. update PART in tbl_PART with the new running sum (taken from tblTestData).

jaffer

PS

After saying the above, I looked at your PART filed, it is TEXT datatype with numbers and dash in it, so we cannot get the running sum of PART!!!
What we can do now is, tbl_PART should have the old PART values of tblTestData,
so we compare each value between the two tables, and when we find a differance, then we change tbl_PART values to the new PART values in tblTestData, and do the BALLANCE update.

You don't hvae a PK (primary key) in tblTestData, which is a bad design, as there is NO unique key for each Record to identify if, so I added an auto_ID PK in tblTestData, and use this for the comparison between the two tables.
Private Sub cmd_Compare_Click()

    
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
' Load both the Tables in Recordsets

    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblTestTable")
    Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_PART")
    
    'set the cursor to properly read the records
    rst1.MoveLast: rst2.MoveLast
    rst1.MoveFirst: rst2.MoveFirst
    
    AllOK = "Yes"
    
    
    'easy comparison 1st, if the number of Records is not the same
    If rst1.RecordCount <> rst2.RecordCount Then
        'number of Records not the same
        AllOK = "No"
        GoTo Delete_Append_Update
        
    Else
    
    'compare item by item of PART
        For i = 0 To rst1.RecordCount

            'MsgBox rst1!Auto_ID & " = " & rst1!PART & vbCrLf & _
                    rst2!ID & " = " & rst2!PART_Old_Value
       
            If rst1!Auto_ID <> rst2!ID Or rst1!PART <> rst2!PART_Old_Value Then
                'things are not the same
                AllOK = "No"
                GoTo Delete_Append_Update
            End If
        
            rst1.MoveNext: rst2.MoveNext
        Next i
    
        
    End If
    
    
    'close the recordset
    rst1.Close: rst2.Close
    Set rst1 = Nothing: Set rst2 = Nothing
    
    Exit Sub
 
 
 
Delete_Append_Update:
 
    'turn off the warning
    DoCmd.SetWarnings False
    
    'Delete ALL the Records of tbl_PART
    DoCmd.OpenQuery "qry_Delete_tbl_PART_Records"
    
    'Append ALL the PART form tblTestTable to tbl_PART (for the next comparison)
    DoCmd.OpenQuery "qry_Append_PART_from_tblTestTable_to_tbl_PART"
    
    'Do the BALLANCE calculation on tblTestTable
    DoCmd.OpenQuery "qry_Update_Ballance_tblTestData"
    
    'turn ON the warning
    DoCmd.SetWarnings True
    
    'close the recordset
    rst1.Close: rst2.Close
    Set rst1 = Nothing: Set rst2 = Nothing
    
    MsgBox "Changes found in PART, and the BALLANCE is updated"
    
End Sub

Open in new window

TestDB3.accdb

Commented:
I forgot to say,
the attached accdb has the code in it, in a Form, and it is a click away

jaffer

Commented:
Ah, didn't take care of Empty tbl_PART for the 1st run,
and Looped with 1 additional Record.

corrections implemented.

jaffer
Private Sub cmd_Compare_Click()
On Error GoTo cmd_Compare_Click
    
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
' Load both the Tables in Recordsets

    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblTestTable")
    Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_PART")
    
    'set the cursor to properly read the records
    rst1.MoveLast: rst2.MoveLast
    rst1.MoveFirst: rst2.MoveFirst
    
    AllOK = "Yes"
    
    
    'easy comparison 1st, if the number of Records is not the same
    If rst1.RecordCount <> rst2.RecordCount Then
        'number of Records not the same
        AllOK = "No"
        GoTo Delete_Append_Update
        
    Else
    
    'compare item by item of PART
        For i = 1 To rst1.RecordCount

            'MsgBox rst1!Auto_ID & " = " & rst1!PART & vbCrLf & _
                    rst2!ID & " = " & rst2!PART_Old_Value
       
            If rst1!Auto_ID <> rst2!ID Or rst1!PART <> rst2!PART_Old_Value Then
                'things are not the same
                AllOK = "No"
                GoTo Delete_Append_Update
            End If
        
            rst1.MoveNext: rst2.MoveNext
        Next i
    
        
    End If
    
    
    'close the recordset
    rst1.Close: rst2.Close
    Set rst1 = Nothing: Set rst2 = Nothing
    
    MsgBox "No Changes found in PART, BALLANCE is Not updated"

    Exit Sub
 
 
 
Delete_Append_Update:
 
    'turn off the warning
    DoCmd.SetWarnings False
    
    'Delete ALL the Records of tbl_PART
    DoCmd.OpenQuery "qry_Delete_tbl_PART_Records"
    
    'Append ALL the PART form tblTestTable to tbl_PART (for the next comparison)
    DoCmd.OpenQuery "qry_Append_PART_from_tblTestTable_to_tbl_PART"
    
    'Do the BALLANCE calculation on tblTestTable
    DoCmd.OpenQuery "qry_Update_Ballance_tblTestData"
    
    'turn ON the warning
    DoCmd.SetWarnings True
    
    'close the recordset
    rst1.Close: rst2.Close
    Set rst1 = Nothing: Set rst2 = Nothing
    
    MsgBox "Changes found in PART, and the BALLANCE is updated"
    
    
Exit Sub
cmd_Compare_Click:

    If Err.Number = 3021 Then
        'tbl_PART is empty, it's 1st run, ignore
        Resume Next
    Else
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
End Sub

Open in new window

TestDB3.accdb
Retired IT Professional
Commented:
Run Query1 and compare with your required output.
This required from your xlsx file
BALANCE
40
28
16
4
45
33
21
9
47
35
23
11
13
1
-11
And this output from Query1
Balance
40
28
16
4
45
33
21
9
47
35
23
11
13
1
-11

The DUE_DATE is just to allow for individual record total, and you may replace it with other fields to make the record unique.

Author

Commented:
Thanks.  The output you have above is correct but I could not replicate.  When I run the query, only certain records (where PART changes)  get BALANCE updated.  I need every record to have a balance calculated, not just the ones where PART changes.

Below is the logic that I need

If PART = previous record PART value, BALANCE = previous record’s BALANCE value-REQ_QTY, else BALANCE = QTY_ONHAND-REQ_QTY

Continue this loop until all records are processed.

Thanks.

Commented:
Hmm
I guess what I did was wrong!!

jaffer
Hamed NasrRetired IT Professional

Commented:
List some data as I did above.
Hamed NasrRetired IT Professional

Commented:
What is the date format of your database date fields.
1 dd/mm/yyyy    ex:  18/06/2010
or
2 mm/dd/yyyy    ex:  06/18/2010

Author

Commented:

This is what the result is when I run the query you code you provided.  I added column to show the Correct Value that should have come up.


PART      QTY_ONHAND      REQ_QTY       BALANCE            Correct Value
1100001-00                50      10      40            40
1100001-00                50      12      38            28
1100001-00                50      12      38            16
1100001-00                       50      48      2            -32
1100002-00                55      10      45            45
1100002-00                55      12      43            33
1100002-00                55      12      43            21
1100002-00                55      12      43            9
1100004-00                57      10      47            47
1100004-00                57      12      45            35
1100004-00                57      12      45            23
1100004-00                57      12      45            11
1100005-00                23      10      13            13
1100005-00                23      12      11            1
1100005-00                23      12      11            -11
1100005-00                23      12      11            -23


The result you listed previously in message ID 33122437 is correct.  I just can't reproduce it.

Ignore date field.  It does nothing.  All I need is to calculate the running total of a part.  When that part changes, we need to start over using QTY_ONHAND and REQ_QTY.

Reattaching desired output Excel file again. Please see the logic I used.  Replicating this in Access is what I want to do.


Thanks.
Desired-Result.xls

Author

Commented:
Thank you.  I used a combination of both solutions.   Thanks.
Hamed NasrRetired IT Professional

Commented:
"Looking for something similar to do in Access."
In excel you have fixed references to data, thes are row numbers "1, 2, 3, ..." and column headings "A1, A2, ...."
In access you need a way to identify the record, especially if it has a repeated field like "PART" in your example. Here an autonumber or a date field will help in providing a reference to an access record.

This of course when using queries, but when using a recordset, it can be manged in a roughly similar way.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial