Lawhon, Alan C Contractor/Morgan Research
alan.lawhon at us.army.mil
Thu Feb 17 15:00:41 CST 2005
Jim:
I've got a more basic problem ...
I execute the query and it returns #Error in the RunSum [calculated
field]
column for EVERY RECORD - except one! (Is this directly related to your
number 1. point below?)
Now that I think about it (Thanks for reminding me!) functions with
parameters must have arguments passed to them when they're called. Duh!
Therefore, I guess I need to write a macro (or VBA module) that will
open
the table, set the record pointer to the first record, set up a Do Loop
that executes [all records] to EOF, and call the query for each
iteration
of the Do loop passing the [Field A] and [Field B] arguments to the
function.
Yea, that should do it!
Alan C. Lawhon
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, February 17, 2005 1:28 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Only a "Superstar" Access Developer CanSolve
ThisProblem
Alan,
Just watch out for the two gotcha's that occur when calling a function
from a query:
1. You must pass a parameter to the function to force it to execute for
every row. If you don't, the function will only be called once at the
start
of the query. Not really a problem in this case, but can be if your
just
trying to number the rows ie. the increment is in the function, and you
make a call like this:
GetRowCount()
2. If there is criteria on the column where the function is called, it
will
be executed two times for each row.
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 12:29 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Only a "Superstar" Access Developer CanSolve
ThisProblem
Charlotte:
Prior to running a query, we do a "descending" sort [manually] on
[Field B] to get the records with the highest "counts" to the top.
In the example I show, the fourth record (where [Field B] = 100),
would sort to the top and the "first record" (where [Field B] = 20)
would sort to the bottom. Once we have the records in that order,
then we will execute this "calculated field" summation query.
As far as a "unique key" field (like an AutoNumber PK) is concerned,
I don't think such a field is necessary since the text "values" stored
in [Field A] are all "unique" anyway ... I'm not really interested in
keys and referential integrity for a 2-field table - I just want a
"Running Total" updated (and displayed) onscreen for each record when
this query executes.
You may be right - it may not be possible to execute such a query due
to the fact that one of the operands you need to calculate the
[Running Total] sum for the current record depends on the [Running
Total] sum from the previous record. To solve this problem, there has
to be a
way to "save" the Running_Total [from the previous record] for use with
the current record. In datasheet view, I am aware of a keystroke
sequence,
(i.e. CTRL-Apostrophe) which copies and pastes the value from the
previous
record into the same field of the current record. If this keystroke
sequence could somehow be duplicated within the query, then the
algorithm
for solving this problem would be to "copy" the [Running Total] from the
previous record into the [Running Total] of the current record and then
add the [Field B] value to the [Running Total] of the current record -
and repeat this process for all records to the end of file. What I was
hoping is that somebody knew (or knows) how to code this "copy and
paste" behavior into a summing expression that can be inserted into a
calculated field cell in the query - and that it will work!
I'm going to (closely) examine this Q205183 Knowledge Base article that
Lambert Heenan and Jim Dettman have referred me to. This may be the
only
technique that will work for the particular problem that I have
postulated.
Thanks (all) for your comments and suggestions.
Alan C. Lawhon
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Thursday, February 17, 2005 10:05 AM
To: Access Developers discussion and problem solving
Subject: RE: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com