cumulative multiplication in the same field

[vsssarma]

I have a query in MS Access indicating monthly growth%, that looks as under:

Year	Month	MonthlyGr%	WorstDayGr%InA_Month	MonthFormat	MonthlyGrFactor
2017.00	Dec,17	-0.40	-47.90	1712.00	0.996
2018.00	Jan,18	-0.80	-48.20	1801.00	0.992
2018.00	Feb,18	69.80	6.50	1802.00	1.698
2018.00	Mar,18	-28.90	-36.60	1803.00	0.711
2018.00	Apr,18	40.90	-22.20	1804.00	1.409
2018.00	May,18	118.30	-20.70	1805.00	2.183
2018.00	Jun,18	-9.80	-27.20	1806.00	0.902
2018.00	Jul,18	15.80	-28.40	1807.00	1.158
2018.00	Aug,18	67.20	-15.40	1808.00	1.672
2018.00	Sep,18	188.40	0.00	1809.00	2.884
2018.00	Oct,18	20.90	-8.00	1810.00	1.209
2018.00	Nov,18	-10.20	-12.80	1811.00	0.898
2018.00	Dec,18	16.20	-28.40	1812.00	1.162

I want to know the % growth for all months put together. The answer should be ((0.996 X 1,698 X 0.711 X 1.409 X 2,183 X 0.902 X 1.158 X 1.672 X 2.884 X 1.209 X 0.898 X 1.162) -1) X 100 = 2,243%

How do I do it in MS Access? Kindly help.

Regards.

Sarma

[June7]

Looks you included Dec 17 instead of Jan 18 in the example.

I am not familiar with "% growth" calculation. Regardless, not aware of an intrinsic function in Access to accomplish this. Build a custom VBA function that opens a recordset and loops through records. Something like:

Function Growth(MonthStart, MonthEnd)
Dim rs As DAO.Recordset, dblPct As Double
Set rs = CurrentDb.OpenRecordset("SELECT MonthlyGrFactor FROM tablename WHERE [Month] Between " & MonthStart & " AND " & MonthEnd)
dblPct = rs!MonthlyGrFactor
While Not rs.EOF
    dblPct = rs!MonthlyGrFactor * dblPct
    rs.MoveNext
Wend
Growth = (dblPct - 1) * 100
End Function

Call the function in query or textbox.

Sorting on month names is not practical. How did you get that data to sort chronologically?

BTW, Year and Month are reserved words (intrinsic functions) and should not use reserved words as names for anything. Also should not use punctuation and special characters (underscore only exception).

[vsssarma]

Thanks, my friend.

Kindly write the code more appropriately. I know more Access than VBA. You have the exact query.

Data starts from Dec,17 and ends with Dec,18.

Regards.

[ArviLaanemets]

=(SUMPRODUCT(TheRangeOfMonthlyGrFactorForYear)-1)*100

OR

=(SUMPRODUCT(0,992; 1,698; 0.711; 1.409; 2,183; 0.902; 1.158; 1.672; 2.884; 1.209; 0.898; 1.162)-1)*100

[vsssarma]

ArviLaanemets:

Thanks.

Did not work out either way.

Regards

[orange]

I want to know the % growth for all months put together. The answer should be ((0.996 X 1,698 X 0.711 X 1.409 X 2,183 X 0.902 X 1.158 X 1.672 X 2.884 X 1.209 X 0.898 X 1.162) -1) X 100 = 2,243%

I think you have some commas where they should be decimal points.

Here is a mockup of the calculation

Sub a()
    '(0.996 X 1,698 X 0.711 X 1.409 X 2,183 X 0.902 X 1.158 X 1.672 X 2.884 X 1.209 X 0.898 X 1.162) -1) X 100
    
    Dim num(11) As Single, sRes As Single, i As Integer
    num(0) = 0.996
    num(1) = 1.698
    num(2) = 0.711
    num(3) = 1.409
    num(4) = 2.183
    num(5) = 0.902
    num(6) = 1.158
    num(7) = 1.672
    num(8) = 2.884
    num(9) = 1.209
    num(10) = 0.898
    num(11) = 1.162

    sRes = 1
    For i = 0 To 11
        sRes = sRes * num(i)
    Next i
    'Debug.Print sRes
    sRes = sRes - 1
    'Debug.Print sRes
    sRes = sRes * 100
    Debug.Print "Final value is " & sRes
End Sub

which gives as result

Final value is 2250.103

[vsssarma]

orange

Code should not contain the actual numbers such as 0.996.

Rgds

[orange]

No comment on the commas vs decimal points?
I provided a mockup to show the value based on your expression, except I corrected the commas but missed 1 value.

Here it is using a recordset

Sub a1()
    Dim i As Single: i = 1
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("v")
    Do While Not rs.EOF
        Debug.Print rs!Monthzz; rs!MonthlyGrFactor
        i = i * rs!MonthlyGrFactor
        rs.MoveNext
    Loop
    i = (i - 1) * 100
    Debug.Print "Final value is " & i
End Sub

Output is
1712 0.996
1801 0.992
1802 1.698
1803 0.711
1804 1.409
1805 2.183
1806 0.902
1807 1.158
1808 1.672
1809 2.884
1810 1.209
1811 0.898
1812 1.162
Final value is 2231.301

[ArviLaanemets]

Did not work out either way.

Of course it didn't

The picture in your starting post was too Excel-like. This formula works - in Excel!

[ArviLaanemets]

No comment on the commas vs decimal points?

OP's regional settings are comma as decimals delimiter, and semicolon as parameter or array list delimiter.

[June7]

Why is Jan,18 0.992 not included - a typo?

@vsssmra, What do you mean by "Kindly write the code more appropriately."? I don't know your table or query name. Adjust code to use your table name. I did modify previous post to adjust the WHERE clause in the SQL and added more info. Might review again.

Procedure goes in a general module. Then call the function from a query or in a textbox.

[CJ_London]

if you want to do it in sql you could use a cross tab to get the data in one row then another query based on that to do the multiplication. for the crosstab use year as row heading, month as column heading and first MonthlyGrFactor as value

then a second query to multiply jan x feb x mar etc.

[Micron]

if you want to do it in sql you could use a cross tab to get the data in one row then another query based on that to do the multiplication. for the crosstab use year as row heading, month as column heading and first MonthlyGrFactor as value
then a second query to multiply jan x feb x mar etc.

Don't think so because there isn't one field that has a single value. You will get 2 rows because there's 2 years

[orange]

OP's regional settings are comma as decimals delimiter, and semicolon as parameter or array list delimiter.

Yes,I agree it should be consistent. I looked specifically at what the OP said should be the correct expression and answer.
The answer should be ((0.996 X 1,698 X 0.711 X 1.409 X 2,183 X 0.902 X 1.158 X 1.672 X 2.884 X 1.209 X 0.898 X 1.162) -1) X 100 = 2,243%

[CJ_London]

You will get 2 rows because there's 2 years

so filter by the year