Stewart_HendersonNO1
asked on
Calculating Running Total In Access
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("q ry_Product s")
Set rstSummary = CurrentDb.OpenRecordset("t blMatProfi leRawM")
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(dblRunningTotalPrevi ous * 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(dblRunningTotalPrevi ous * 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(dblRunningTotalPrevi ous * 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(dblRunningTotalCurre nt * 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(dblRunningTotalCurre nt * 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(dblRunningTotalCurre nt * 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
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("q
Set rstSummary = CurrentDb.OpenRecordset("t
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(dblRunningTotalPrevi
'-------------------------
'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(dblRunningTotalPrevi
'-------------------------
'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(dblRunningTotalPrevi
'-------------------------
'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(dblRunningTotalCurre
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(dblRunningTotalCurre
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(dblRunningTotalCurre
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
ASKER
Hi,
Has anybody any thoughts on the question above
Stewart
Has anybody any thoughts on the question above
Stewart
I would have expected all the numeric field names to be enclosed in [ ]
ASKER
Hi,
The error that was coming up was 'duplicate destination output 1'
I have the code working on a differernt calculation but i now wanted to calculate on a different field, re example spreasheet and cannot get the code (understand fully) to work after being amended
Stewart
The error that was coming up was 'duplicate destination output 1'
I have the code working on a differernt calculation but i now wanted to calculate on a different field, re example spreasheet and cannot get the code (understand fully) to work after being amended
Stewart
ASKER
Hi,
The error is on this line
DoCmd.RunSQL sSQL_StockValue & " where Type = 'Stock Price - 2010 Latest' and Item_No = '" & rstProducts![Item_No] & "'"
The error is on this line
DoCmd.RunSQL sSQL_StockValue & " where Type = 'Stock Price - 2010 Latest' and Item_No = '" & rstProducts![Item_No] & "'"
I suggest you add msgbox(es) to see what your sql actually looks like.
Before each .runsql...
msgbox sSQL_StockValue & " where Type = 'Stock Price - 2010 Latest' and Item_No = '" & rstProducts![Item_No] & "'"
Before each .runsql...
msgbox sSQL_StockValue & " where Type = 'Stock Price - 2010 Latest' and Item_No = '" & rstProducts![Item_No] & "'"
ASKER
Hi,
I put in the msg box and amended the code and it runs, but does not give me the figures I require.
Looking at the spreadsheet I enclosed earlier have you any further ideas on how the running caluculation can be performed in access. I have the top line 'Productiin Issues' that have figures in from periods 1-12 and then in period -1 are 2010 BudgetN, 2010 Latest and 2011 Budget which need running calcalutions against them, ie each multiplies against the production issues each month.
Help would be much appreciated
I put in the msg box and amended the code and it runs, but does not give me the figures I require.
Looking at the spreadsheet I enclosed earlier have you any further ideas on how the running caluculation can be performed in access. I have the top line 'Productiin Issues' that have figures in from periods 1-12 and then in period -1 are 2010 BudgetN, 2010 Latest and 2011 Budget which need running calcalutions against them, ie each multiplies against the production issues each month.
Help would be much appreciated
"... but does not give me the figures I require."
You will have to be more explicit. I can't guess at what is going on.
Is it putting in values in all locations?
If not , have you already established that it is visiting each field?(just put a fixed value in to test it)
If it's getting the wrong answers then you I would have thought that would show up in the SQL.
You will have to be more explicit. I can't guess at what is going on.
Is it putting in values in all locations?
If not , have you already established that it is visiting each field?(just put a fixed value in to test it)
If it's getting the wrong answers then you I would have thought that would show up in the SQL.
ASKER
Hi,
I have enclosed another spreadsheet which shows the an example of the figures I start with before the code is applied ; then an example of what the figures are like once the code is run and finally an example of what I would like the figures to show. Below is the code being run
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 dbl
Dim dblStockPrice As Double
Dim dblStockPrice1 As Double
Dim dblStockPrice2 As Double
DoCmd.SetWarnings False
Set rstProducts = CurrentDb.OpenRecordset("q ry_Product s")
Set rstSummary = CurrentDb.OpenRecordset("t blMatProfi leRawM")
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 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
dblStockPrice = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")
Else
dblStockPrice = 0
End If
sSQL_StockValue = sSQL_StockValue & "-1 = " & Round(dblRunningTotalPrevi ous * dblStockPrice, 2)
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
'Calculate the opening stock value of G
If Not IsNull(DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
dblStockPrice1 = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")
Else
dblStockPrice1 = 0
End If
sSQL_StockValue1 = sSQL_StockValue1 & "-1 = " & Round(dblRunningTotalPrevi ous * 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(dblRunningTotalPrevi ous * 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(dblRunningTotalCurre nt * 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(dblRunningTotalCurre nt * 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(dblRunningTotalCurre nt * 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 Budget' and Item_No = '" & rstProducts![Item_No] & "'"
DoCmd.RunSQL sSQL_StockValue1 & " where Type = 'Stock Price - 2010 Latest' 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
RM.xls
I have enclosed another spreadsheet which shows the an example of the figures I start with before the code is applied ; then an example of what the figures are like once the code is run and finally an example of what I would like the figures to show. Below is the code being run
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 dbl
Dim dblStockPrice As Double
Dim dblStockPrice1 As Double
Dim dblStockPrice2 As Double
DoCmd.SetWarnings False
Set rstProducts = CurrentDb.OpenRecordset("q
Set rstSummary = CurrentDb.OpenRecordset("t
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 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
dblStockPrice = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Budget' AND [Item_No] = '" & rstProducts![Item_No] & "'")
Else
dblStockPrice = 0
End If
sSQL_StockValue = sSQL_StockValue & "-1 = " & Round(dblRunningTotalPrevi
'-------------------------
'Calculate the opening stock value of G
If Not IsNull(DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")) Then
dblStockPrice1 = DLookup("[-1]", "[tblMatProfileRawM]", "[Type] = 'Stock Price - 2010 Latest' AND [Item_No] = '" & rstProducts![Item_No] & "'")
Else
dblStockPrice1 = 0
End If
sSQL_StockValue1 = sSQL_StockValue1 & "-1 = " & Round(dblRunningTotalPrevi
'-------------------------
'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(dblRunningTotalPrevi
'-------------------------
'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(dblRunningTotalCurre
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(dblRunningTotalCurre
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(dblRunningTotalCurre
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 Budget' and Item_No = '" & rstProducts![Item_No] & "'"
DoCmd.RunSQL sSQL_StockValue1 & " where Type = 'Stock Price - 2010 Latest' 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
RM.xls
ASKER
Hi,
Any thoughts on the above ?
Stewart
Any thoughts on the above ?
Stewart
ASKER
Hi,
I have enlosed the database if that makes it easier (tblMatProfileRawM)
Essentially the format is four rows against each item number (always a Prod Issues line as this is the line the other 3 are caluclated against)
Item No Item Descr Type - 1 1 2 3 (up to 12)
10000 hhhhh Prod Issues 1520 2502 3562
10000 hhhhh 2010 Budget 1.5 2280 3753 5343
10000 hhhhh 2010 New 2.5 3800 6255 8905
10000 hhhhh 2011 Budget 2.8 4256 7005.6 9973.6
Can anyone suggest how I get these calculations to work baring in mind it starts like below
Item No Item Descr Type - 1 1 2 3 (up to 12)
10000 hhhhh Prod Issues 1520 2502 3562
10000 hhhhh 2010 Budget 1.5
10000 hhhhh 2010 New 2.5
10000 hhhhh 2011 Budget 2.8
Stewart
ee-Running-SubtotalRawM.mdb
I have enlosed the database if that makes it easier (tblMatProfileRawM)
Essentially the format is four rows against each item number (always a Prod Issues line as this is the line the other 3 are caluclated against)
Item No Item Descr Type - 1 1 2 3 (up to 12)
10000 hhhhh Prod Issues 1520 2502 3562
10000 hhhhh 2010 Budget 1.5 2280 3753 5343
10000 hhhhh 2010 New 2.5 3800 6255 8905
10000 hhhhh 2011 Budget 2.8 4256 7005.6 9973.6
Can anyone suggest how I get these calculations to work baring in mind it starts like below
Item No Item Descr Type - 1 1 2 3 (up to 12)
10000 hhhhh Prod Issues 1520 2502 3562
10000 hhhhh 2010 Budget 1.5
10000 hhhhh 2010 New 2.5
10000 hhhhh 2011 Budget 2.8
Stewart
ee-Running-SubtotalRawM.mdb
With such a query and names used, a database is required. Attach one and explain what to do and what to expect. Show an image of the expected results after running the query on the existing records.
I see there is an attached database. Hope it Try to reduce the size and reattach.
Try to compact and repair.
Try compressing the file.
Try to compact and repair.
Try compressing the file.
You need to be explicit about what is working and what is not.
I am assuming you have been testing your code as you have been going along so can you let us know what you know to be correct.
At the moment we know that the final answer is not what you want, but we need to know where to look for problems.
I am assuming you have been testing your code as you have been going along so can you let us know what you know to be correct.
At the moment we know that the final answer is not what you want, but we need to know where to look for problems.
ASKER
Hi,
I have enclosed a database which includes the tblMatProfileRawM, take the first product number in the tbl as an the example data contained (Item No - 100000).
I want the Prices contained in Period -1 against 'Stock Price - Budget 2010', 'Stock Price - Latest 2010' and 'Stock Prices - Budget 2011' to calculate against the Production Issues for each month (periods 1-12)
Used example item number 100001 as the result of how the calculation should look when finished against each product
Hope that suffices
RawM.mdb
I have enclosed a database which includes the tblMatProfileRawM, take the first product number in the tbl as an the example data contained (Item No - 100000).
I want the Prices contained in Period -1 against 'Stock Price - Budget 2010', 'Stock Price - Latest 2010' and 'Stock Prices - Budget 2011' to calculate against the Production Issues for each month (periods 1-12)
Used example item number 100001 as the result of how the calculation should look when finished against each product
Hope that suffices
RawM.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still can't find the logic how to produce the output. List input records and explain how to get the output.
Other point to notice, I compacted and repaired the database and it shrunk in size to just 250KB.
Other point to notice, I compacted and repaired the database and it shrunk in size to just 250KB.
;-)
ASKER
Hi,
I have looked at the comment / example given by Peter57r and this gives me the figures I wanted. Many thanks to you both for your comments today. Help has been much appreciated.
I will award the points to Peter in a short while.
Again, thanks.
Stewart
I have looked at the comment / example given by Peter57r and this gives me the figures I wanted. Many thanks to you both for your comments today. Help has been much appreciated.
I will award the points to Peter in a short while.
Again, thanks.
Stewart
ASKER
Many thanks for your help on this question, solution was precise and to the point.
ASKER
The user who answered the similar question last time was RunRigger, and your advice (or anybody else ) would br greatly appreciated.
Stewart