Juan Velasquez
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 TotalRemainingNettableStoc k. 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 TotalRemainingNettableStoc k column from the previous record in subsequent calculations Sum([TotalRemainingNettabl eStock]-[P lanned_Qua ntity]. It seems there should be a way to accomplish this via the query designer
Item Code Transaction Date Total_Nettable_Stock TotalPlannedQuantity TotalRemainingNettableStoc k
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
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 TotalRemainingNettableStoc
Item Code Transaction Date Total_Nettable_Stock TotalPlannedQuantity TotalRemainingNettableStoc
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;
To be clear, post an exact example of the output you are expecting...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
JeffCoachman
ASKER
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.
ASKER
Below is the following code I created. However I am getting a no current record error on line 56
lngPrevTotalRemaining = rs("Total_Remaining")
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
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.
ASKER
Hello Pat,
I noticed that I had opened two recordsets after I posted the code and have already fixed that.
I noticed that I had opened two recordsets after I posted the code and have already fixed that.
ASKER
Hello,
Here is the updated code. I'm still getting the no current record errors
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
ASKER
I found the problem, I forgot the movenext after the update
ASKER
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---------------------- ---------- ---------- ---------- ---------- --
'Called subroutine -------------------------- ---------- --------
'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
ASKER
Thanks, your comments pointed me to a working solution