Running cumulative - how to create?

[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

 

[Ken Snell \(MVP\)]

In a table? in a query? in a form? in a report?

 

[Guest]

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

 

[Guest]

"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;

 

[Stefan Hoffmann]

hi,

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

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 <--

 

[Guest]

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

hi,

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

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 <--

 

[Stefan Hoffmann]

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

As far as I understand you:

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

Take a look at the online help.


mfG
--> stefan <--

 

[Guest]

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.

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

As far as I understand you:

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

Take a look at the online help.


mfG
--> stefan <--

 

[Guest]

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),closingbalencesum(onhandbalence+actualquantity))-(sum(fuelissues+miscellaniosissues)) In one report
dear sir is it possible how to do it please

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.

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

As far as I understand you:

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

Take a look at the online help.


mfG
--> stefan <--