Solved

ms/access 2013 how to cumulate values in a query

Posted on 2014-12-26
12
153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 37

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 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