running total in access

[Benoit]

Hi,

suppose you have a table like this

date qty
jan 1
feb 3
mar 1
april 2

and I want to have a running total, i.e.
jan 1
feb 4
mar 5
apr 7

how can it be done in access ?


you can reply to (e-mail address removed)

 

[John Vinson]

Hi,

suppose you have a table like this

date qty
jan 1
feb 3
mar 1
april 2

and I want to have a running total, i.e.
jan 1
feb 4
mar 5
apr 7

how can it be done in access ?

It's very easy on a Report: use a textbox for the qty field and set
its Running Sum property to True.

It can be done on a Form but requires a bit more work; see
http://www.mvps.org/access and search for "running sum" for sample
code.

you can reply to (e-mail address removed)

Done. but be aware...

This request is considered impolite. The people who answer questions
here (even the Microsoft employees) are volunteers doing so on our own
time. If it's worth my time to come to the newsgroup to answer
questions, it should be worth yours to come back to the newsgroup for
the answer.

Also, you have now exposed your real email address to innumerable
spambots who harvest this (and all other) newsgroups for addresses.
You can expect a lot of viruses and spam to follow; most of us use
munged addresses (see the headers of this message).

And please reply to the newsgroup; if you un-spamtrap my address
you're welcome to EMail me, but know that I'll start billing at my
usual consulting rates when I open it.

 

[Guest]

Hi,

thanks for the info.

But I need to bring back this info in Excel. So my
question should be: can I do a running total in a query?
I don't want to do it in Excel. I have query on top of
query in access and they require this running total to go
on.

thanks,
Benoit

 

[John Vinson]

Hi,

thanks for the info.

But I need to bring back this info in Excel. So my
question should be: can I do a running total in a query?
I don't want to do it in Excel. I have query on top of
query in access and they require this running total to go
on.

You'll need a bit more information, then. You can put in a calculated
field

=DSum("[qty]", "[queryname]", "[sortfield] <= " & [sortfield])

where sortfield is some field within the query which you can count on
to be strictly ascending. ("jan" comes after "feb" alphabetically so
that field won't work, but a Date/Time field should).