Solved

ms/access 2013 how to cumulate values in a query

Posted on 2014-12-26
12
152 Views
Last Modified: 2014-12-28
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
Comment
Question by:jirdeaid
  • 7
  • 4
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40519462
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
 

Author Comment

by:jirdeaid
ID: 40519664
From the samples, I'll try this one and let you know:

DSum("UnitPrice * Quantity", "Order Details", "OrderID = 10248")
0
 

Author Comment

by:jirdeaid
ID: 40519748
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40519826
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
 

Author Comment

by:jirdeaid
ID: 40519853
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40519858
Why does it not operate as expected?
0
 

Author Comment

by:jirdeaid
ID: 40519860
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
 

Author Comment

by:jirdeaid
ID: 40519861
there is no sum(dsum(...))... just dsum(...)
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40519945
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
 

Author Comment

by:jirdeaid
ID: 40520228
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40520280
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
 

Author Closing Comment

by:jirdeaid
ID: 40520320
thanks a lot for excellent help!!!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question