João serras-pereira
asked on
ms/access 2013 how to cumulate values in a query
I have a query on which I need to compute the growth of a population. On the query I have a value [BaseYearPop] that is the population for the base year. On the record I do also have [GrowthRate] that is the population growth rate for that specific year. I need to compute [CurrentYearPop] that is the anticipated population for that year, which is a cumulative computation (all previous years growth plus that specific years).
How do I do it?
How do I do it?
ASKER
From the samples, I'll try this one and let you know:
DSum("UnitPrice * Quantity", "Order Details", "OrderID = 10248")
DSum("UnitPrice * Quantity", "Order Details", "OrderID = 10248")
ASKER
well ...
I am not getting there...
I am sending you the database; the issue is on query "_QQ" and it is the last statement. I am getting always the same number....
sireshDB-11.accdb
I am not getting there...
I am sending you the database; the issue is on query "_QQ" and it is the last statement. I am getting always the same number....
sireshDB-11.accdb
I'm on holiday so I can't open the database on my iPad.
However, if myYear is the year, then the code should be something like:
DSum("GrowthRate", "myTable", "myYear <= " & [myYear]) + BaseYearPop
However, if myYear is the year, then the code should be something like:
DSum("GrowthRate", "myTable", "myYear <= " & [myYear]) + BaseYearPop
ASKER
although it still does not operate as expected, I have updated the _QQ query to what I thought could resolve the situation; bit it did not. I am uploading the novel file.
sireshDB-11.accdb
sireshDB-11.accdb
Why does it not operate as expected?
ASKER
ASKER
there is no sum(dsum(...))... just dsum(...)
I don't know if this is your problem but:
PopPNUD_2014 * TaxaPopulacao ^anoInx -- interpreted as PopPNUD_2014 * (TaxaPopulacao ^anoInx)
Is different from
(PopPNUD_2014 * TaxaPopulacao) ^anoInx
Whenever you write an expression that contains operators of unequal precedence, it is best to be explicit by using parentheses to specify the order of operation.
PopPNUD_2014 * TaxaPopulacao ^anoInx -- interpreted as PopPNUD_2014 * (TaxaPopulacao ^anoInx)
Is different from
(PopPNUD_2014 * TaxaPopulacao) ^anoInx
Whenever you write an expression that contains operators of unequal precedence, it is best to be explicit by using parentheses to specify the order of operation.
ASKER
nope... that was not the problem and I an aware of the operator precedence (actually, the formula is wrong and I am correcting it.
the problem is a bit different, let me try to put it in clearer (hopefully) words.
I have a query (_QQ) with a Population for a village. so the relevant columns are [RefTabanca] [PopPNUD_2014] [ano] [anoinx] and [TaxaPopulacao].
[ano] is the Year (2014, 2015, ...)
[anoinx] is the ano sequence number (1 for 2014, 2 fr 2015....)
[RefTabanca] is the key code for a village
[PopPNUD_2014] is the value of the population for 2014
Now I need to compute, for each [RefTabanca] the new value of the population based on the previous year total and adding the [TaxaPopulacao] value, that varies from year to year.
The direct formula should be something like:
[ActualPopulation] :
iif( is the first record of [RefTabanca] then
[PopPNUD_2014];
[ActualPopulation of previous record] * (1 + [TaxaPopulacao])
)
so it accumulates the population over the years. I have this problem for a number of fields, this is why it is important for me to sort it. In the end, I should have one record per year and village.
I would really appreciate any help
the problem is a bit different, let me try to put it in clearer (hopefully) words.
I have a query (_QQ) with a Population for a village. so the relevant columns are [RefTabanca] [PopPNUD_2014] [ano] [anoinx] and [TaxaPopulacao].
[ano] is the Year (2014, 2015, ...)
[anoinx] is the ano sequence number (1 for 2014, 2 fr 2015....)
[RefTabanca] is the key code for a village
[PopPNUD_2014] is the value of the population for 2014
Now I need to compute, for each [RefTabanca] the new value of the population based on the previous year total and adding the [TaxaPopulacao] value, that varies from year to year.
The direct formula should be something like:
[ActualPopulation] :
iif( is the first record of [RefTabanca] then
[PopPNUD_2014];
[ActualPopulation of previous record] * (1 + [TaxaPopulacao])
)
so it accumulates the population over the years. I have this problem for a number of fields, this is why it is important for me to sort it. In the end, I should have one record per year and village.
I would really appreciate any help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot for excellent help!!!!
1. You can use a DSUM function, so you have the field name, table, and criteria (I.e. =< the current year), or
2. You can use a self-join, and instead of using the = operator on the join, use a =<
approach 1 is simpler to do, but 2 is faster if you have a lot of data. See http://www.techonthenet.com/access/functions/domain/dsum.php for infomation on approach 1.