Link to home
Start Free TrialLog in
Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America

asked on

Calculate Remaining Stock as Running totla

Hello,
I am trying to figure out a set based solution to determine the total remaining nettable stock by transaction date for each item_code.  For instance, below is the result I want.  However, I am unable to get the correct TotalRemainingNettableStock.  It is not being reduced by the TotalPlanned quantity for each transaction date.  The problem is in Sum([Total_Nettable_Stock]-[Planned_Quantity].  It looks I may have to use a code based solution to use the total nettable stock column as the initial starting point and then use the TotalRemainingNettableStock column from the previous record in subsequent calculations Sum([TotalRemainingNettableStock]-[Planned_Quantity].  It seems there should be a way to accomplish this via the query designer

Item Code    Transaction Date              Total_Nettable_Stock                 TotalPlannedQuantity        TotalRemainingNettableStock  
A01                  4/1/2015                                 100                                                   30                                       70                                                 70
A01                  4/5/2015                                 100                                                   10                                       60                                      
A01                  4/10/2015                               100                                                   20                                       40
A02                 4/6/2016                                    30                                                     5                                       25
A02                 4/8/2015                                    30                                                   30                                        -5


SELECT BaseBidRelease.Item_Code, BaseBidRelease.Transaction_Date AS Trans_Date, Sum(BaseBidRelease.Total_Nettable_Stock) AS TotalNettableStock, Sum(BaseBidRelease.Planned_Quantity) AS TotalPlannedQuantity, Sum([Total_Nettable_Stock]-[Planned_Quantity]) AS TotalRemainingNettableStock
FROM BaseBidRelease
WHERE (((BaseBidRelease.Site)="LTN"))
GROUP BY BaseBidRelease.Item_Code, BaseBidRelease.Transaction_Date
ORDER BY BaseBidRelease.Item_Code, BaseBidRelease.Transaction_Date;

Open in new window

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

To be clear, post an exact example of the output you are expecting...
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Juan Velasquez

ASKER

Hello Jeffrey,

The required output is shown below.  As you can see for Item_Code A01, the intitial total nettable stock was 100 and the planned quantity was 30 which means that 70 remained.  On the second day, I want to subtract that days Planned Quantity of 10 from the remaining stock of 70 which would give me a new remaining nettable stock of 60 and so on.

Item Code    Transaction Date              Total_Nettable_Stock               TotalPlannedQuantity     TotalRemainingNettableStock     
A01                  4/1/2015                                 100                                                   30                                       70                                                 
A01                  4/5/2015                                 100                                                   10                                       60                                       
A01                  4/10/2015                               100                                                   20                                       40
A02                 4/6/2016                                    30                                                     5                                       25
A02                 4/8/2015                                    30                                                   30                                        -5

Open in new window

You can easily do this in a report.

You just have to create another control based on TotalPlannedQuantity and set the "running Sum" property to: Over Group.
Then create another control that subtracts this running sum value from the TotalNetable field.
Sample attached
Database73.mdb
Assuming that you have a uniqueID that can be relied on to represent the actual sequence of the records, you can do this by using a self join.

From tblInv as A Inner Join tblInv as B
Where B.UniqueID <= A.UniqueID AND A.ItemCode = B.ItemCode

HOWEVER, since this is actually more of a sequential process, it is better to do it in a report.  Reports process their recordsource, one record at a time, from the beginning to the end so it is trivial to do running sums and in fact there is a built in property to accommodate just that.  Queries are sets of data and do not lend themselves to processes where a record is dependent on the sequence where it occurs.
Unfortunately, the user want the output in Excel. In additio, there is no unique id per se.  I'm going to take a code base solution
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Unfortunately, the user want the output in Excel.
...this is something that you did not state in your original post.

You can still use the report I posted, ...then use code to export the report to Excel:
    DoCmd.OutputTo acOutputReport, "YourReport", acFormatXLS, "C:\YourFolder\YourReport.xls"

JeffCoachman
Unfortunately Pat, the user does not want to add the running sum himself.  In addition, the user wants the output to match the excel spreadsheet, with no deviations.  I've got a solution and it's a bit clunky.  However since we are dealing with 2000 records, speed shouldn't be much of an issue.  When I complete the solution, I will post it.
Below is the following code I created. However I am getting a no current record error on line 56
 lngPrevTotalRemaining = rs("Total_Remaining")

Public Sub SetTotalRemaining(varCurrentItemCode As Variant)
    
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strItemCode As String        'strItemCode As String.
     
    Dim strSQL As String            'SQL statement.
             
    Dim lngQtyRemaining As Long
    Dim lngInitialTotalNettableStock As Long
    Dim lngPlannedQuantity As Long
    Dim lngTotalRemaining As Long
    Dim lngPrevTotalRemaining As Long
    Dim i As Long
    
    Dim strSite As String
    strSite = "LTN"
    If Not IsNull(varCurrentItemCode) Then
        
        Set db = CurrentDb()
        strItemCode = CStr(varCurrentItemCode)

        strSQL = "SELECT TOP 1 Transaction_Date, Total_Nettable_Stock , Planned_Quantity FROM BaseBidRelease " & _
           "WHERE ((Item_Code = '" & strItemCode & "')  AND Site = '" & strSite & "'" & _
          ") ORDER BY Transaction_Date ASC;"
      
          'Get initial quantities for the given item code
          Set rs = db.OpenRecordset(strSQL)
          If rs.RecordCount > 0 Then
            lngInitialTotalNettableStock = Nz(rs("Total_Nettable_Stock"), 0)
            lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
            lngTotalRemaining = lngInitialTotalNettableStock - lngPlannedQuantity
          End If
          
          rs.Close
          
        strSQL = "SELECT  Transaction_Date, Total_Nettable_Stock , Planned_Quantity, Total_Remaining FROM BaseBidRelease " & _
            "WHERE ((Item_Code = '" & strItemCode & "') " & " AND Site = '" & strSite & "'" & _
            ") ORDER BY Transaction_Date Asc;"
     

        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        
        Dim varBookMark As Variant
        
            If rs.RecordCount > 0 Then
                For i = 0 To rs.RecordCount - 1
                    If i = 0 Then
                        rs.Edit
                        rs("Total_Remaining") = lngInitialTotalNettableStock - lngPlannedQuantity
                        rs.Update
                    Else
                        lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
                        varBookMark = rs.Bookmark
                        rs.MovePrevious
                        lngPrevTotalRemaining = rs("Total_Remaining")
                        rs.Bookmark = varBookMark
                        lngTotalRemaining = lngPlannedQuantity - lngPrevTotalRemaining
                        rs.Edit
                            rs("Total_Remaining").Value = lngTotalRemaining
                        rs.Update
                    End If
                        
                Next
            End If
        End If

      

    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
    
End Sub

Open in new window

Why are you opening two recordsets against the same table?  All you need is a single recordset.  You just need to create variables to hold the identifying information so you can reset your accumulator when the grouping info changes.
Hello Pat,
I noticed that I had opened two recordsets after I posted the code and have already fixed that.
Hello,

Here is the updated code. I'm still getting the no current record errors

Public Sub SetTotalRemaining(varCurrentItemCode As Variant)
    
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strItemCode As String        'strItemCode As String.
     
    Dim strSQL As String            'SQL statement.
             
    Dim lngQtyRemaining As Long
    Dim lngInitialTotalNettableStock As Long
    Dim lngPlannedQuantity As Long
    Dim lngTotalRemaining As Long
    Dim lngPrevTotalRemaining As Long
    Dim i As Long
    Dim varBookMark As Variant
    
    Dim strSite As String
    strSite = "LTN"
    If Not IsNull(varCurrentItemCode) Then
        
        Set db = CurrentDb()
        strItemCode = CStr(varCurrentItemCode)

        strSQL = "SELECT Transaction_Date, Total_Nettable_Stock , Planned_Quantity,  Total_Remaining FROM BaseBidRelease " & _
           "WHERE ((Item_Code = '" & strItemCode & "')  AND Site = '" & strSite & "'" & _
          ") ORDER BY Transaction_Date ASC;"
          
          Debug.Print strSQL
        
            Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
            If rs.RecordCount > 0 Then
                For i = 0 To rs.RecordCount - 1
                    If i = 0 Then
                        lngInitialTotalNettableStock = Nz(rs("Total_Nettable_Stock"), 0)
                        lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
                        lngTotalRemaining = lngInitialTotalNettableStock - lngPlannedQuantity
                        rs.Edit
                        rs("Total_Remaining") = lngInitialTotalNettableStock - lngPlannedQuantity
                        rs.Update
                    Else
                        lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
                        varBookMark = rs.Bookmark
                        rs.MovePrevious
                        lngPrevTotalRemaining = rs("Total_Remaining").Value  'NO CURRENT RECORD
                        rs.Bookmark = varBookMark
                        lngTotalRemaining = lngPlannedQuantity - lngPrevTotalRemaining
                        rs.Edit
                            rs("Total_Remaining").Value = lngTotalRemaining
                        rs.Update
                    End If
                        
                Next
            End If
        End If     

    Set rs = Nothing
    Set db = Nothing
    Exit Sub    
    
End Sub

Open in new window

I found the problem, I forgot the movenext after the update
Here is the final code.  The first code is the calling code in the form while the second code section is the code called when looping through the item codes.  I'll be  including some additional housekeeping code to clean up any objects

'Calling code----------------------------------------------------------------
Private Sub cmdRunBIDReport_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strItemCode As String
strSql = "SELECT Distinct Item_Code FROM BaseBidRelease ORDER By Item_Code;"

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset)

If rst.RecordCount > 0 Then
    rst.MoveLast
    rst.MoveFirst
    Do While Not rst.EOF
        strItemCode = rst("Item_Code")
        Call basUtilities.SetTotalRemaining(strItemCode)
        rst.MoveNext
    Loop
    
End If


Set rst = Nothing
Set dbs = Nothing
MsgBox "Bid Release Processed"
Call Create_BIDS_Workbook

'DoCmd.OpenQuery "qryBaseBidRelease"
MsgBox "Completed"

Exit Sub

End Sub

Open in new window


'Called subroutine --------------------------------------------
Public Sub SetTotalRemaining(varCurrentItemCode As Variant)
    
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strItemCode As String        'strItemCode As String.
     
    Dim strSql As String            'SQL statement.
             
    Dim lngQtyRemaining As Long
    Dim lngInitialTotalNettableStock As Long
    Dim lngPlannedQuantity As Long
    Dim lngTotalRemaining As Long
    Dim lngPrevTotalRemaining As Long
    Dim i As Long
    Dim varBookMark As Variant
    
    Dim strSite As String
    strSite = "LTN"
    If Not IsNull(varCurrentItemCode) Then        
        Set db = CurrentDb()
        strItemCode = CStr(varCurrentItemCode)
        strSql = "SELECT Transaction_Date, Total_Nettable_Stock ,   Planned_Quantity,  Total_Remaining, Release_Status FROM BaseBidRelease " & _
           "WHERE ((Item_Code = '" & strItemCode & "')  AND Site = '" & strSite & "'" & _
          ") ORDER BY Transaction_Date ASC;"
                  
            Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
            If rs.RecordCount > 0 Then
                rs.MoveLast
                rs.MoveFirst
                i = 0
                Do While Not rs.EOF
                    If i = 0 Then
                        lngInitialTotalNettableStock = Nz(rs("Total_Nettable_Stock"), 0)
                        lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
                        lngTotalRemaining = lngInitialTotalNettableStock - lngPlannedQuantity
                        rs.Edit
                        If lngTotalRemaining >= 0 Then
                                rs("Release_Status") = "Release"
                            Else
                                rs("Release_Status") = "Do Not Release"
                            End If
                            rs("Total_Remaining") = lngTotalRemaining
                            rs.Update
                            i = i + 1
                            rs.MoveNext
                        Else
                            lngPlannedQuantity = Nz(rs!Planned_Quantity, 0)
                            varBookMark = rs.Bookmark
                            rs.MovePrevious
                            lngPrevTotalRemaining = rs("Total_Remaining").Value
                            lngTotalRemaining = lngPrevTotalRemaining - lngPlannedQuantity
                            rs.Bookmark = varBookMark
                            rs.Edit
                            If lngTotalRemaining >= 0 Then
                                rs("Release_Status") = "Release"
                            Else
                                rs("Release_Status") = "Do Not Release"
                            End If
                            rs("Total_Remaining").Value = lngTotalRemaining
                        rs.Update
                        rs.MoveNext
                    End If
                Loop
            End If
        End If   
    
    Set rs = Nothing
    Set db = Nothing
    Exit Sub       
End Sub

Open in new window

Thanks, your comments pointed me to a working solution