Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms/access 2013 how to cumulate values in a query

Posted on 2014-12-26
12
Medium Priority
?
155 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:João serras-pereira
[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:João serras-pereira
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:João serras-pereira
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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:João serras-pereira
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:João serras-pereira
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:João serras-pereira
ID: 40519861
there is no sum(dsum(...))... just dsum(...)
0
 
LVL 39

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:João serras-pereira
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 2000 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:João serras-pereira
ID: 40520320
thanks a lot for excellent help!!!!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 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