troubleshooting Question

Calculating Running Total In Access

Avatar of Stewart_HendersonNO1
Stewart_HendersonNO1 asked on
Microsoft Access
20 Comments1 Solution447 ViewsLast Modified:
I recently went asked a similar question, and this is a slight variation on it.

I have enclosed the output from the access table into a spreadsheet. The figures in red are what should be calculated automatically going across from periods 1-12. (the price for 2010 Budegt / Latest and 2011) is set in column -1 and needs to multplied by the production issues each month.
Below is the code currently being used but it is not working. Help on amending the code would be appreciated.

Public Sub ApplyRunningTotal()

Dim rstProducts As DAO.Recordset
Dim rstSummary As DAO.Recordset
Dim dblRunningTotalCurrent As Double
Dim dblRunningTotalPrevious As Double
Dim n As Integer
Dim sSQL_StockValue As String
Dim sSQL_StockValue1 As String
Dim sSQL_StockValue2 As String
Dim dblStockPrice As Double
Dim dblStockPrice1 As Double
Dim dblStockPrice2 As Double


    DoCmd.SetWarnings False

    Set rstProducts = CurrentDb.OpenRecordset("qry_Products")
    Set rstSummary = CurrentDb.OpenRecordset("tblMatProfileRawM")

    rstProducts.MoveLast
    rstProducts.MoveFirst

    Do Until rstProducts.EOF
   
        sSQL_StockValue = "Update tblMatProfileRawM set "
        sSQL_StockValue1 = "Update tblMatProfileRawM set "
        sSQL_StockValue2 = "Update tblMatProfileRawM set "
       
       
        '-----------------------------------------------------------------------------------------------
        '-----------------------------------------------------------------------------------------------
        'Calculate the opening stock value of n
        If Not IsNull(DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
            dblStockPrice = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")
        Else
            dblStockPrice = 0
        End If
       
        sSQL_StockValue = sSQL_StockValue & "1 = " & Round(dblRunningTotalPrevious * dblStockPrice, 2)
       
        '-----------------------------------------------------------------------------------------------
        'Calculate the opening stock value of G
        If Not IsNull(DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
            dblStockPrice1 = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")
        Else
            dblStockPrice1 = 0
        End If
       
        sSQL_StockValue1 = sSQL_StockValue1 & "1 = " & Round(dblRunningTotalPrevious * dblStockPrice1, 2)
       
        '-----------------------------------------------------------------------------------------------
        'Calculate the opening stock value of B
        If Not IsNull(DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2011 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
            dblStockPrice2 = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2011 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")
        Else
            dblStockPrice2 = 0
        End If
       
        sSQL_StockValue2 = sSQL_StockValue2 & "1 = " & Round(dblRunningTotalPrevious * dblStockPrice2, 2)
       
        '-----------------------------------------------------------------------------------------------
        'Cycle through the remaining periods and calculate the values of all three running totals;
        'Stock Holding, Stock Value, Number of Pallets
        'Default value is zero for Stock Value and Number of Pallets if an opening value was not given
        For n = 1 To 12
       
            '-------------------------------------------------------------------------------------------
            'There isn't a period 0, so skip
            If n <> 0 Then
               
                dblRunningTotalCurrent = dblRunningTotalPrevious
               
                '---------------------------------------------------------------------------------------
                'Skip this calculation if Production Plans do not exist, otherwise add it to the running total
                If Not IsNull(DLookup("[" & n & "]", "[tblMatProfileRawM]", "[Type] = 'Production Issues' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
                    dblRunningTotalCurrent = dblRunningTotalCurrent + DLookup("[" & n & "]", "[tblMatProfileRawM]", "[Type] = 'Production Issues' AND [Item_No] = '" & rstProducts![Item_No] & "'")
                End If
                '---------------------------------------------------------------------------------------
               
                '---------------------------------------------------------------------------------------
               
                '---------------------------------------------------------------------------------------
                'Add the n'th period stock value to the SQL string if price exists, otherwise set the value to zero
                If dblStockPrice <> 0 Then
                    sSQL_StockValue = sSQL_StockValue & "," & n & " = " & Round(dblRunningTotalCurrent * dblStockPrice, 2)
                Else
                    sSQL_StockValue = sSQL_StockValue & "," & n & " = 0"
                End If
               
                '---------------------------------------------------------------------------------------
                'Add the n'th period stock value to the SQL string if price exists, otherwise set the value to zero
                If dblStockPrice1 <> 0 Then
                    sSQL_StockValue1 = sSQL_StockValue1 & "," & n & " = " & Round(dblRunningTotalCurrent * dblStockPrice1, 2)
                Else
                    sSQL_StockValue1 = sSQL_StockValue1 & "," & n & " = 0"
                End If
               
                '---------------------------------------------------------------------------------------
                'Add the n'th period stock value to the SQL string if price exists, otherwise set the value to zero
                If dblStockPrice1 <> 0 Then
                    sSQL_StockValue2 = sSQL_StockValue2 & "," & n & " = " & Round(dblRunningTotalCurrent * dblStockPrice2, 2)
                Else
                    sSQL_StockValue2 = sSQL_StockValue2 & "," & n & " = 0"
                End If
               
                '---------------------------------------------------------------------------------------
                'Re-set the brought forward running total
                dblRunningTotalPrevious = dblRunningTotalCurrent
           
            End If
           
        Next n
   
        '-----------------------------------------------------------------------------------------------
        'Add the where clause to the SQL string and execute
        DoCmd.RunSQL sSQL_StockValue & " where Type = 'Stock Price - 2010 Latest' and Item_No = '" & rstProducts![Item_No] & "'"
        DoCmd.RunSQL sSQL_StockValue1 & " where Type = 'Stock Price - 2010 Budget' and Item_No = '" & rstProducts![Item_No] & "'"
        DoCmd.RunSQL sSQL_StockValue1 & " where Type = 'Stock Price - 2011 Budget ' and Item_No = '" & rstProducts![Item_No] & "'"
        rstProducts.MoveNext
   
    Loop

    DoCmd.SetWarnings True

    MsgBox "Calculation of running total is done!"
 


End Sub


RawMat1.xls
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 20 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros