Help with accumulating totals

  • Thread starter Thread starter Alan Nicoll
  • Start date Start date
A

Alan Nicoll

Hi. I want to have a field that accumulates amounts in
another field, then resets to zero when a third field
changes value. That is:

Date Amount Accumulated
1/4/04 25 25
1/4/04 5 30
1/4/04 10 40
1/5/04 15 15

I can't see how to do this except by going to Visual
Basic, which I'd much prefer to avoid.

Thanks.

Alan Nicoll
 
Do you have any field (maybe your primary key, maybe a
time field) that follows the order of your records (i.e.
always increasing for each successive record?). If you
do, you could build a query with a calculated field using
DSum - to illustrate I will call your table "TABLE1" and
assume it has an autonumber primary key called "TableKey"
that follows the order of your records; then use this
expression as a query column:

Accumulated:DSum("[Amount]","TABLE1","[Date]=#"&[Date]&"#
And [TableKey]<="&[TableKey])

Interpretation: You are asking for the sum of Amounts
where the date is the same as your current record and the
key value is less than or equal to the current record - so
it will accumulate the total in the Amount field for any
records up to the current one that have the same date as
the current one - and will reset when the date changes.
 
Looks promising, I'll give it a try. Thanks!

Alan
-----Original Message-----
Do you have any field (maybe your primary key, maybe a
time field) that follows the order of your records (i.e.
always increasing for each successive record?). If you
do, you could build a query with a calculated field using
DSum - to illustrate I will call your table "TABLE1" and
assume it has an autonumber primary key called "TableKey"
that follows the order of your records; then use this
expression as a query column:

Accumulated:DSum("[Amount]","TABLE1","[Date]=#"&[Date]&"#
And [TableKey]<="&[TableKey])

Interpretation: You are asking for the sum of Amounts
where the date is the same as your current record and the
key value is less than or equal to the current record - so
it will accumulate the total in the Amount field for any
records up to the current one that have the same date as
the current one - and will reset when the date changes.
-----Original Message-----
Hi. I want to have a field that accumulates amounts in
another field, then resets to zero when a third field
changes value. That is:

Date Amount Accumulated
1/4/04 25 25
1/4/04 5 30
1/4/04 10 40
1/5/04 15 15

I can't see how to do this except by going to Visual
Basic, which I'd much prefer to avoid.

Thanks.

Alan Nicoll
.
Click to expand...
.
Click to expand...
 
Back
Top