I have a dataset of accounting data (trial balance of all ledger accounts) produced by another select query which has several text fields (account name, entity, etc) and 18 separate number fields (named 0, 1, 2 ....18) containing the monthly movement of each account, where "0" is opening balance, "1" is period 1, etc..
What I want to be able to do is create a calculated field which sums a dynamic range of fields 0 - 18 (but always starting at 0) depending on the current period No. ascertained from user input. That is, the calculated field will produce a year-to-date sum by adding field "0" and each of the periodic movements (fields "1" onwards) up to and including the period/field nominated by the user. However, I can't work out how to create the conditional expression in Access functions to sum a variable range of multiple fields that works like this:
=sum(fieldrange([0]:[user input])
PS: I can achieve this in Excel by exporting all 18 fields into Excel and then using a combination of If, Choose, and Sum functions, but workbook size and lack of memory prevents this, as the file produced is in excess of 18Meg!!
Any assistance will be greatly appreicated.
Thanks in advance
What I want to be able to do is create a calculated field which sums a dynamic range of fields 0 - 18 (but always starting at 0) depending on the current period No. ascertained from user input. That is, the calculated field will produce a year-to-date sum by adding field "0" and each of the periodic movements (fields "1" onwards) up to and including the period/field nominated by the user. However, I can't work out how to create the conditional expression in Access functions to sum a variable range of multiple fields that works like this:
=sum(fieldrange([0]:[user input])
PS: I can achieve this in Excel by exporting all 18 fields into Excel and then using a combination of If, Choose, and Sum functions, but workbook size and lack of memory prevents this, as the file produced is in excess of 18Meg!!
Any assistance will be greatly appreicated.
Thanks in advance