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

Alan,

  Depending on what your using the output for, you can use this technique:

 http://support.microsoft.com/?kbid=205183

  Same technique (calling a function) can be used for numbering the rows as
well.

  However you can't use it if your using the query output as a recordsource
for a form.  As you scroll in the form, the query re-executes throwing off
the totals.

  Can use it for anything else though.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Lawhon, Alan C
Contractor/Morgan Research
Sent: Thursday, February 17, 2005 10: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