• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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?
0
João serras-pereira
Asked:
João serras-pereira
  • 7
  • 4
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
There are two approaches.

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.
0
 
João serras-pereiraAuthor Commented:
From the samples, I'll try this one and let you know:

DSum("UnitPrice * Quantity", "Order Details", "OrderID = 10248")
0
 
João serras-pereiraAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
 
João serras-pereiraAuthor Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Why does it not operate as expected?
0
 
João serras-pereiraAuthor Commented:
Ok. Here is the query:

screen  shot

sreen shot 2
code:

PopAnual: (DSum("PopPNUD_2014 * TaxaPopulacao ^anoInx";"SustentEconFin";[SustentEconFin]![anoInx]<=[anoinx] And [SustentEconFin]![RefTabanca]=[Reftabanca] And [SustentEconFin]![ano]=[ano]))
0
 
João serras-pereiraAuthor Commented:
there is no sum(dsum(...))... just dsum(...)
0
 
PatHartmanCommented:
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.
0
 
João serras-pereiraAuthor Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Going back to your screenshots, your third parameter needs to be partially in quotation marks, and partly outside.

Have a look at my example to show you what should be in what:

"MyYear < = "  & [MyYear}
0
 
João serras-pereiraAuthor Commented:
thanks a lot for excellent help!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now