Running Totals

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

I have a table which holds stock transactions and would like to produce a
query to show the results along with a running total. Is there an easy way
in which you can create the running total rather that have to hold it in the
table?

Thanks in advance
 
Dear MJ:

A running total can be calculated using a subquery. The subquery sums
all the values from the preceding rows. It can restart the sum at
certain breaks if that is desirable.

The problem in using this is usually the definition of preceding rows.
This must be based on a key (one or more columns on which to sort).

This key must be unique if the rows are to be added in one at a time.
When a set is ordered by a non-unique key, there will commonly be
multiple rows that are equal in key value. While you will always see
these rows as though they were in a specific order, they aren't. Rows
with equal key values in an ordering of a set form a "clump" that
logically appears simultaneously in the ordering of the set. As a
clump, these rows will add into the running sum simultaneously, as
their order is indistinguishable. Often, you may need to use an
identity / autonumber column to "break the ties" and make the ordering
of the set unique. While this is arbitrary, it destroys the clumping
and allows a repeatable way of ordering the rows that is then capable
of supporting a running sum that is more "human presentable."

"Computers are technically picky, people are full of assumptions."

For help more specific to your situation, please post a query that
provides everything BUT the running sum you want. Specify if there
are any breaks in the sequence at which to start the running sum over
again. I'll try to add the subquery for your running sum to that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi MJ,

See: ACC2000: How to Create Running Totals in a Query
http://support.microsoft.com/default.aspx?scid=kb;en-us;208714

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| From: "MJ" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Running Totals
| Date: Tue, 3 Feb 2004 13:47:40 -0000
| Organization: Posted via Supernews, http://www.supernews.com
| Message-ID: <[email protected]>
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| X-Complaints-To: (e-mail address removed)
| Lines: 8
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix
.com!newsfeed.cwix.com!tdsnet-transit!newspeer.tds.net!sn-xit-02!sn-xit-01!s
n-post-01!supernews.com!corp.supernews.com!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188536
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have a table which holds stock transactions and would like to produce a
| query to show the results along with a running total. Is there an easy way
| in which you can create the running total rather that have to hold it in
the
| table?
|
| Thanks in advance
|
|
|
 
Back
Top