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