Calculating arrays in queries

[junetiong]

Hi all,

I'm new to Access and need a lot of help.

I am trying to calculate Year-to-Date (YTD) returns using monthly returns data. In Excel, I use an array formula - is there something similar in Access?

Also, how do I create a dynamic range, such that each time I enter new monthly data, the formula automatically takes into account the new data when I refresh it?

Thank you in advance for taking the time to help!

 

[arnelgp]

create a query and use the Sum() function.

select Sum(field) As YTD From your table.

 

[junetiong]

Thank you for the swift reply arnelgp.

Sorry that I wasn't being clear in my earlier post.

If my Excel formula is {=PRODUCT(1+C:174:C178/100)-1} whereby the array C:174:178 are part of a larger set of data (from C:10), how would I use the Sum() function - what do I type to get the same calculation in Access query since Access does not have cell numbers (C:174, A2, etc)?

Also, since I will have future monthly data, how would I go about fixing C:174 but moving C:178 to C:179, C:180 etc to accommodate future data?

Thank you again.

 

[arnelgp]

sorry, are we in access or excel? if excel cant help.

 

[junetiong]

I am looking for an Access equivalent of the formula I have in Excel. I am trying to migrate from Excel to Access.

Thank you.

 

[arnelgp]

create first your database and import your worksheet to the new database.
when your done, create a query against your table. include the field you want to Sum.
the query will return the results for all records or for a subset of records if you specify a criteria on your query.

try to play with it first.

 

[junetiong]

I have already imported the data that I need from Excel into Access with no problems. I am trying to create a query to calculate the returns I need but I am not familiar with Access as the formula is different from Excel.

I have attached my Excel data set with an example (in yellow) of a calculation I require query to do. I have also attached sceenshots of the database and what my query looks like thus far.

The start and end dates are the same as I would like to build a report in future whereby I can select the start and end dates and the returns data will appear. Please let me know if this is the wrong way to go about it and appreciate if you could kindly point me in the right direction.

Thank you so much in advance! Sorry I'm so bad with Access..

 

[arnelgp]

you already have it in access_2.jpg, remove all other fields except the [returns] field, then on the ribbon click the zigma sign (summation). after hitting the sigma sign it will automatically be Group By, changed this to Sum.

 

[junetiong]

But, I am not looking to add the numbers up. If you look at the Excel file and click on the yellow highlighted box, you would be able to see the formula, which is what I am trying to replicate in Access.

It'll be great if you could help me with the formula..

 

[Guus2005]

It puzzles me how the array was determined in your sample but you can use the Excel PRODUCT function in Access.

https://msdn.microsoft.com/en-us/library/dd789430(v=office.12).aspx

Let me know if you need help with that.

HTH