Calculate Running Total in a New Field

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

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.

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];

Select allOpen in new window

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

jaffer

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

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

Attached is a smaller sample file.
Desired-Result.xls

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

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.

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

jaffer

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

Select allOpen in new window

TestDB3.accdb

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.

Hmm
I guess what I did was wrong!!

jaffer

List some data as I did above.

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

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

Thank you.  I used a combination of both solutions.   Thanks.

"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.