[AccessD] Only a "Superstar" Access Developer Can Solve This Problem

This article should set you on the way to superstardom...

http://support.microsoft.com/default.aspx?scid=kb;en-us;205183

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 10:41 AM
> To:	accessd at databaseadvisors.com
> Subject:	[AccessD] Only a "Superstar" Access Developer Can Solve This
> Problem 
> 
> 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