Calculate Monthly Aggregated Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LCK - 30 Mar 2006 22:45 GMT

Hi,
I'd appreciate if you could help me to perform the following
calculations:

I Begin with two Tables:

Table1
Period InitialStock
12/2005 100

Table2
Period Consumption
01/2006 10
02/2006 30
03/2006 10
05/2006 20

Which operations do I have to perform between those two tables to get
to the Following table:

Period EndingStock
01/2006 100-10 = 90
02/2006 90-30 = 60
03/2006 60-10 = 50
04/2006 50-0 = 50 (There was no consumption in Apr-2006)
05/2006 50-20 = 50

Regards
LC

Reply to this Message

tommaso.gastaldi@uniroma1.it - 31 Mar 2006 17:46 GMT

Hi, LCK

I'd suggest (but beware I am not an expert at all in Access: just
trying to use common sense) that you add a missing piece of information
(the dates where you want to do the calculation).
For instance You can add a small table with 1 field (Period) and the
following data (it holds in general if you have different time
occasions):

------ Table DatesMo --------
01/01/2006
01/02/2006
01/03/2006
01/04/2006
01/05/2006
---------------------------------------

Then it should be simple to make a join which yelds your result (you
probably need a right outer join followed by an inner join). The
EndingStock can be obtained, I guess, by subtraction from the
InitialStock of the cumulative Consumption. The latter can be obtained
by a simple subselect.

Clearly there are many other ways to do that, and I am sure the many
experts that are here will be able to advise you regarding the very
best way...

-tom

LCK ha scritto:

> Hi,
> I'd appreciate if you could help me to perform the following
[quoted text clipped - 25 lines]
> Regards
> LC

Reply to this Message