[AccessD] Only a "Superstar" Access Developer Can Solve ThisProblem

Since the order of records in a query is not fixed, a "running total"
would involve a sum of values where the unique key was less than or
equal to the value of the unique key for the current record.  In your
example, you don't show a unique key, unless that's what the Field_A
items are supposed to be, so it wouldn't work.  

Charlotte Foust


-----Original Message-----
From: Lawhon, Alan C Contractor/Morgan Research
[mailto:alan.lawhon at us.army.mil] 
Sent: Thursday, February 17, 2005 7:41 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Only a "Superstar" Access Developer Can Solve
ThisProblem 


At the risk of showing my [Access] incompetence, I'm going to give you
folks an "opportunity to excel" (pun intended) by showing me a creative
solution to this "simple" query problem.

 

I'm rooting around in the Access 2003 "Help" files (as well as one of
Susan's Access books) and I discover the term calculated field.  A
"calculated field" is defined as: "A field, defined in a query, that
displays the result of an expression rather than displaying stored data.
The value is recalculated each time a value in the expression changes."
I think a "calculated field" is exactly what I want, but I'm having
trouble figuring out how to write the expression, so I'm throwing this
"simple problem" at you guys and gals.

 

I have a table with two fields.  One of the fields [Field A] contains
text data and the second field [Field B] contains short integers.  (The
integer value stored in [Field B] represents the number of items on hand
for the entity described in [Field A] for each record in the table.)
What I want to create is a select query which will generate a
"Running_Total" [calculated field] that performs a record-by-record
summing action like this:

 

         [Field A]                [Field B]       [Running Total] 

 

   Small_Do_Hicky                20                   20

   Smaller_Do_Hicky             32                   52

   Large_Do_Hicky                69                  121

   Larger_Do_Hicky             100                  221

   Very_Large_Do_Hicky       48                  269

   Largest_Do_Hicky             31                  300

 

All the "Do Hickys" in Field A represent the same type of physical
object, the only difference being variations in [physical] dimensions
between the objects.  I have summarized six records (for six objects) as
an example, but there are literally hundreds (and in some cases
thousands) of records in some of these tables.  It's fairly obvious what
I'm looking for here - a query containing a "calculated field"
expression that takes the value in [Field B], adds it to the [Running
Total] value from the previous record, and prints the sum in the
[Running Total] field of the current record.  The end result is a "Grand
Total" of all the "Do_Hicky" objects (for all the records) displayed in
the [Running Total] calculated field's final record when the query
executes.

 

I've figured out a way to get the final total using a SQL aggregate
function, but I would like to be able to run a query that shows the
running total on a record-by-record basis from the first record all the
way to the last record.  Anyone who can solve this (simple?) Access
query problem will be declared an official Access "guru" and be blessed
by the pope.  (This "reward" ought to get Martin Reid interested ...)
:-))))

 

Alan C. Lawhon

 

    

 

    

 

 

 

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com