Solved

ms/access 2013 how to cumulate values in a query

Posted on 2014-12-26
12
154 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 38

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

630 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