[AccessD] Only a "Superstar" Access Developer CanSolve ThisPr oblem

Heenan, Lambert Lambert.Heenan at AIG.com
Thu Feb 17 15:44:19 CST 2005


No. In the query just pass some field (any field, as long as it has a
compatible data type) to the function. The function does not even need to do
anything with the parameter, it just has to be passed.

So in your query add a calculated field like this

RunningSum:YourRunningSumFunction([Some Field From The Query])

This will be faster than looping through the records in code.

Lambert


> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Lawhon, Alan C
> Contractor/Morgan Research
> Sent:	Thursday, February 17, 2005 4:01 PM
> To:	Access Developers discussion and problem solving
> Subject:	RE: [AccessD] Only a "Superstar" Access Developer CanSolve
> ThisProblem
> 
> 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



More information about the AccessD mailing list