Query Calculations

  • Thread starter Thread starter jamesb
  • Start date Start date
J

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! :(
 
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
 
help help i need somone to tell me how to creat calculations on access, mine
always turn up invalid
 
tsoi said:
help help i need somone to tell me how to creat calculations on access,
mine always turn up invalid
Click to expand...

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.
 
Back
Top