# 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;
``````
###### Who is Participating?

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.

MIS LiasonCommented:
To be clear, post an exact example of the output you are expecting...
0
MIS 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
Author 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
``````
0
MIS 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
MIS LiasonCommented:
0
Commented:
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
Author 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
Commented:
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

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

MIS 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
Author 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
Author 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
``````
0
Commented:
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
Author Commented:
Hello Pat,
I noticed that I had opened two recordsets after I posted the code and have already fixed that.
0
Author 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
``````
0
Author Commented:
I found the problem, I forgot the movenext after the update
0
Author 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
``````

'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
``````
0
Author Commented: