dmcafee at pacbell.net
dmcafee at pacbell.net
Thu Feb 17 17:16:47 CST 2005
You should just add the PK to the table and do it using a sub query as Charlotte replied. I've done this for adding line numbers to queries for export purposes. You do need this in a query, right? This would be very easy to do in a report. David McAfee -----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 3:07 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Only a "Superstar" Access DeveloperCanSolve ThisPr oblem Lambert: I added a calculated field to the query just as you directed. In fact, I have followed the instructions in KB article Q205183 explicitly and I'm still getting the same error when the query executes. I think there may be a bit of confusion between myself and you guys as to exactly what it is that I'm trying to accomplish here. The records are already grouped in my table with the grouped totals in [Field B]. What I'm wanting to do, (please carefully re-read my original post), is to sum all of the grouped totals into one "Grand Total" to get a final count of all the values stored in [Field B] for all the records in the table. (Maybe I didn't explain this very well.) I've modified the function "fncRunSum" (slightly) changing the variable declarations from Long (to Short) integers because the [Field B] values are "short" integers in my table. After making this change, when I attempted to execute the query, I got this error message: Microsoft Office Access Compile error. in query expression 'fncRunSum([REF_DOC],[REF_DOC_Total])'. NOTE: In "my" table, identifier "[REF_DOC]" corresponds to [Field A] and identifier "[REF_DOC_Total]" is [Field B]. It's getting late in the afternoon here and I'm fixing to leave. I will take another crack at this in the morning, but before I do I am going to execute the RunSum function [verbatim] against the [Northwind] "Products" table and follow the instructions exactly as specified in Knowledge Base article Q205183. If the function works against the Northwind "Products" table, (as described in the article), then I'll know that I've made some kind of "translation error" (or something) either in the query or in the function code. Alan C. Lawhon -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Thursday, February 17, 2005 3:44 PM To: 'Access Developers discussion and problem solving'; Lawhon, Alan C Contractor/Morgan Research Subject: RE: [AccessD] Only a "Superstar" Access Developer CanSolve ThisPr oblem 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 -- 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