cumulative multiplication in the same field

[Micron]

so filter by the year

Then the value listed for 2017, which is part of the calculation op posted, won't be included.

[vsssarma]

ArviLaanemets

The commas should not have been there, they are full stops.

These two buttons are next to each other; and look similar for a 63 year-young man.

RGDS

[vsssarma]

Micron

As more & more data is taken into the Access Database, more months will appear. As of now, data in respect of 13 months has been kept in the database.

RGDS

[CJ_London]

Then the value listed for 2017, which is part of the calculation op posted, won't be included.

OK so have a fixed value of '"XXX" and make that the row header. And with regards the missing January data in the calculation, I'm assuming that is down to a poor example, and if it is not, it can easily be excluded with criteria


However if this is going to go on indefinitely, then a vba solution is probably better since a) the number of columns is limited to 255 (so circa 21 years) and b) the second query would need to be rewritten each month to include the extra month - or use vba

[Micron]

Sure, you can "SELECT "yrYear" AS Yr" or whatever, but I think you hit the nail on the head now; eventually the query will max out on fields.
As for vba, that was offered in post 2, which would negate having to transpose records into fields. However, I thought a function that's used in a query had to be of the nature "Function SomeFunction() As SomeType" and the example isn't.

This particular thread has been quite muddled what with commas or not, include all values or not, regional settings...
I played around with my own version, but not sure I see the point of posting it seeing how OP didn't even comment on post 2. I think we're making the assumption OP can use a query that calls such a function but I fear that the next issue would be having the calculation appear on every record because of some query it's used in.

[vsssarma]

VBA or SQL is the right way forward because more & more data will get in.

RGDS

[June7]

@Micron, declaring function as specific data type is not mandatory, same for the function arguments. They will default to Variant. If type is explicitly declared, better make sure the data passed and function result will be the correct type. Leaving as Variant allows more flexibility although may be best practice to declare type.

[vsssarma]

I think that the VBA solution you offered is the best but I do not know how it is to be taken into the MS Access file. So full code with Syntax is needed. For example, the SQL starts with SELECT.

[June7]

Yes, the SQL starts with SELECT. What else would it start with?

"Take it into MS Access file" by opening VBA editor, creating a general module and a procedure in that module. If you know nothing about writing VBA code, time to learn. Find an online tutorial or buy a book.

Decide how the date range criteria will be determined. Suggest a form where user can enter values.

[vsssarma]

Thanks. I will try. I did VBA very long time ago. Rgds.

[accesstos]

Hi to all!

vsssarma,

This is a user defined VBA function that returns the growth of the desired year.

Function TotalGr(ByVal intYearGr As Integer) As Single
    Dim rs As DAO.Recordset
    Dim sngRes As Single

    Set rs = CurrentDb.OpenRecordset( _
             "SELECT MonthlyGr FROM tblMonthlyGr " _
             & "WHERE DateGr BETWEEN  DateSerial(" & intYearGr & ",0,17) " _
             & "AND DateSerial(" & intYearGr & ",12,18);", dbOpenForwardOnly)

    With rs
        sngRes = 1
        Do Until .EOF
            sngRes = sngRes * (!MonthlyGr / 100 + 1)
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    TotalGr = (sngRes - 1) * 100
End Function

And in attachment, you will find a sample database with several ways of use of this function (as ControlSource in a Textbox, as expression in a row of a Union query and as Row Heading in a Crosstab query).

Regards,
John

[vsssarma]

Grateful. Your work is appreciated.

Can you attempt an SQL also? I am comfortable with MS Access.

Rgds

(PS: The percent growths are obtained in an actual forex trading system that I am analyzing. Over 2000% in an year)

[accesstos]

I am glad I was able to help!

I don't know if it possible to do loops through records using pure SQL definition and there isn't a SUMPRODUCT() function in SQL aggregate functions. In this case, VBA code it seems to be necessary before you resort to Excel spreadsheets.

Best regards,
John