Running cumulative - how to create?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi may I know how to create a new column called RunCum?

Column (a) and (b) is given and (c) is I intend to do

(a) (b) (c)
Date MonthCum RunCum
Jan06 200 200
Feb06 300 500
Mar06 200 700
Apr06 100 800
May06 400 1200

Thanks
 
sorry for not being clear.

(a) and (b) is in a table.

And (c) is something i like to create in a query. A new variable.

Thanks
 
"Date" is a reserved word for Access. I suggest you change it.

The following SQL *should* work. Replace "table1" with your source:

SELECT table1.Date, table1.MonthCum, table1.RunCum, (SELECT Sum(X.monthcum)
FROM table1 As X WHERE X.date <= table1.date) AS Totals
FROM table1;
 
hi,
"Date" is a reserved word for Access. I suggest you change it.
Click to expand...
This is the best way to handle it.

Otherwise you may use the square brackets:

SELECT [table1].[Date], [table1].[MonthCum], [table1].[RunCum], ...
FROM [table1];


mfG
--> stefan <--
 
dearsir,
is it not possible to create a function in VB module to use on querys
like by passing arguments of date and the number to be summed .
kindly give an idea im having the same problom viz,
TABLE name [fueldecanted data]
field decanteddate
" invoicenumber
" openingbalenceoftank
" decantedquantity
now i need in the query BETWEEN [date1] and [date2]
,sum([openingbalence]+[decantedquantity]);and runningsum of each decanting
like this
" openingbalenceoftank" "decantedquantity"
"openingbalenceoftank+decantedquantity"
1000 250 1250
250 1500
how to do it please
your help will help me graetly
thankingyou


Stefan Hoffmann said:
hi,
"Date" is a reserved word for Access. I suggest you change it.
Click to expand...
This is the best way to handle it.

Otherwise you may use the square brackets:

SELECT [table1].[Date], [table1].[MonthCum], [table1].[RunCum], ...
FROM [table1];


mfG
--> stefan <--
Click to expand...
 
hi Balu,
is it not possible to create a function in VB module to use on querys
like by passing arguments of date and the number to be summed .
kindly give an idea im having the same problom viz,
TABLE name [fueldecanted data]
field decanteddate
" invoicenumber
" openingbalenceoftank
" decantedquantity
now i need in the query BETWEEN [date1] and [date2]
,sum([openingbalence]+[decantedquantity]);and runningsum of each decanting
like this
" openingbalenceoftank" "decantedquantity"
"openingbalenceoftank+decantedquantity"
1000 250 1250
250 1500
Click to expand...
As far as I understand you:

DSum("[field1] + [field2]",
"[tableOrQuery]",
"[yourDate] BETWEEN [date1] AND [date2]")

Take a look at the online help.


mfG
--> stefan <--
 
dearsir ,
thanks for your kind replay
exactly my need is like an bank account deposits
in which deposits will be individual(groupby) ,but the sum of the deposits
will be like the (groupby) of progrssive sum of deposits .
for eg,
TABLE 1
account holder
account
address

TABLE 2
accountnumber
deposit
withdrawl

query:
groupby(accountnumber,accountholder,deposit,withdrawl),progressivesum(groupby(deposit)),progressivesum(groupby(withdrawl)),and finally accountbalence.
sorry for im not being clear.
it looks like [deposit] 100 ,200,300
[withdrawl] 50,0,0
[progressivesum deposit] 100 ,300 , 600
[progressvesum withdrawl] 50,50,50
kindly help
withregards.

Stefan Hoffmann said:
hi Balu,
is it not possible to create a function in VB module to use on querys
like by passing arguments of date and the number to be summed .
kindly give an idea im having the same problom viz,
TABLE name [fueldecanted data]
field decanteddate
" invoicenumber
" openingbalenceoftank
" decantedquantity
now i need in the query BETWEEN [date1] and [date2]
,sum([openingbalence]+[decantedquantity]);and runningsum of each decanting
like this
" openingbalenceoftank" "decantedquantity"
"openingbalenceoftank+decantedquantity"
1000 250 1250
250 1500
Click to expand...
As far as I understand you:

DSum("[field1] + [field2]",
"[tableOrQuery]",
"[yourDate] BETWEEN [date1] AND [date2]")

Take a look at the online help.


mfG
--> stefan <--
Click to expand...
 
dearsir,
how to establish relationship and normalise to get the report for the tables
below viz.[fuelinvoces]
field invoiceid 'text' (PK)
" invoceddate 'date/time'
" invoicedquantity 'number'
" actual quantity ' number'
" decanted storagetankname ' text'
table [storagetankdata]

field decantingid 'autonumber'(pk) ,,(i have not found any altarnative id )
" tankname
" tankmeasurement beforedecant 'number'
" tankmeasurementafterdecant 'number'
" invoiceid 'number'
" decanteddate 'date
table [fuelissues]
field issuedate 'date'
" isuueid 'text'(PK)
" enginenuber 'text'
" tankname 'text'
table [miscellaniousissues]
field misissueid 'text(PK)
" quantity 'number'
" purpus 'text ,, (any purpus like foe
gensets)
" tankname 'text'
now i want the report [date],
[tankname],[onhandbalence],groupbyof[fuelinvoices]"all details of that
day",(sum(onhandbalence+actualquantity)),groupbyof(fuelissues),groupbyof(miscellaniousissues),closingbalence:(sum(onhandbalence+actualquantity))-(sum(fuelissues+miscellaniosissues)) In one report
dear sir is it possible how to do it please








balu said:
dearsir ,
thanks for your kind replay
exactly my need is like an bank account deposits
in which deposits will be individual(groupby) ,but the sum of the deposits
will be like the (groupby) of progrssive sum of deposits .
for eg,
TABLE 1
account holder
account
address

TABLE 2
accountnumber
deposit
withdrawl

query:
groupby(accountnumber,accountholder,deposit,withdrawl),progressivesum(groupby(deposit)),progressivesum(groupby(withdrawl)),and finally accountbalence.
sorry for im not being clear.
it looks like [deposit] 100 ,200,300
[withdrawl] 50,0,0
[progressivesum deposit] 100 ,300 , 600
[progressvesum withdrawl] 50,50,50
kindly help
withregards.

Stefan Hoffmann said:
hi Balu,
is it not possible to create a function in VB module to use on querys
like by passing arguments of date and the number to be summed .
kindly give an idea im having the same problom viz,
TABLE name [fueldecanted data]
field decanteddate
" invoicenumber
" openingbalenceoftank
" decantedquantity
now i need in the query BETWEEN [date1] and [date2]
,sum([openingbalence]+[decantedquantity]);and runningsum of each decanting
like this
" openingbalenceoftank" "decantedquantity"
"openingbalenceoftank+decantedquantity"
1000 250 1250
250 1500
Click to expand...
As far as I understand you:

DSum("[field1] + [field2]",
"[tableOrQuery]",
"[yourDate] BETWEEN [date1] AND [date2]")

Take a look at the online help.


mfG
--> stefan <--
Click to expand...
Click to expand...
 
Back
Top