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

Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
To be clear, post an exact example of the output you are expecting...
0
Jeffrey CoachmanMIS LiasonCommented:
This can be tricky depending on you table structure/design.
Also compounding this is the new for this to be done across multiple "Item codes"
Another kicker might be exactly when and where,  you need the update t happen ... and also if it need or be canceled or validated.

In other words, this may not be a "simple" query.
0
Juan VelasquezAuthor Commented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
sample
0
PatHartmanCommented:
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.
0
Juan VelasquezAuthor Commented:
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
0
PatHartmanCommented:
If the user wants the output in Excel, perhaps he can add the running sum function himself.  You just export the raw data.

You CANNOT do an accurate running sum without a unique identifier to sort the records.  In a relational database, tables and queries are unordered sets and from one execution to the next, records might move if the set is not sorted on a unique identifier.  In practice, Access doesn't move records around willy-nilly but if you update the records and an updated record is larger than it was originally, Access will "delete" the original version and write the updated version at the physical end of the recordset.  That will essentially move it to a different location.

You could create a code loop to sort the recordset and write it with a running sum (again, without a uniqueID, your results will vary).  Or, you could create a report and in the Print event of the detail section, write the row to Excel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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
0
Juan VelasquezAuthor Commented:
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.
0
Juan VelasquezAuthor Commented:
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

0
PatHartmanCommented:
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.
0
Juan VelasquezAuthor Commented:
Hello Pat,
I noticed that I had opened two recordsets after I posted the code and have already fixed that.
0
Juan VelasquezAuthor Commented:
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

0
Juan VelasquezAuthor Commented:
I found the problem, I forgot the movenext after the update
0
Juan VelasquezAuthor Commented:
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

0
Juan VelasquezAuthor Commented:
Thanks, your comments pointed me to a working solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.