Query Calculations

[jamesb]

I am trying to get a query to give me a running total/balance of all the
numbers in a specific field, and carry it over to a new field. What is the
expression to get the totals of several entries in the same field??? I tried
sum, but it only carries the actual number over, it doesn't keep a running
total if there are more than one entry.


HELP!

 

[Allen Browne]

The crucial thing is *not* to store the running total in any field of the
table (unless it's merely a temporary table.) If you do, your data will be
all wrong if someone inserts another record later.

It's really easy to do this in a report. Use the Running Sum property of the
text box on the report. That's the best way to get a running sum or bank
balance type of listing.

If you must do it in the query, use a subquery or DSum() expression to
calculate the sum of values so far. However, this calculation makes some
assumptions about the way the data is sorted (results will be wrong if the
user right-clicks on another field and sorts on it) or filtered (results
will be wrong if the user filters the query on another field.)

If you can ignore that, the expression will be something like this:
(SELECT Sum([Amount]) AS AmountRS
FROM Table1 AS Dupe
WHERE Dupe.ID <= Table1.ID)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[tsoi]

help help i need somone to tell me how to creat calculations on access, mine
always turn up invalid

 

[Allen Browne]

help help i need somone to tell me how to creat calculations on access,
mine always turn up invalid

Here's a basic example:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

Hint: start with a small calculation, and gradually build it up (checking
that it works at each stage) until you get it doing everything you need.