Thread: running average

    #1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3

    running average


    I have a database based on the template Desktop Product inventory database. It allows me to document purchases of products. I'd like to store the average price paid for each product.

    something like:

    If Price is updated
    SumPrice = SumPrice + Price
    TimesAveraged = TimesAveraged + 1
    Average = SumPrice/TimesAveraged

    SumPrice would be the sum of all the previous prices

    I think access is having a problem with self reference? Any suggestions on another way?
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Is this supposed to be VBA code?? where are you trying to do this? Query? Form? Table?
    Saving a calculated value is frowned upon. Calculated values may change if the values used to create them are altered, which would not be reflected in the stored value. Calculated values should be recalculated everytime you want to display them for the end user.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    this would be in a web form (no design mode), I was trying to use macro tools. Properties->Event->After Update (Embedded macro) ....
    It doesn't necessarily have to be done that way. I would like for the average to be recalculated every time the stored value for Price is changed

    Example
    12/10 Apples $0.20

    Average price would be 0.20

    new purchase:
    12/13 Apples $0.50

    Average price would now be 0.20+0.50/2
    SumPrice= 0.70, TimesAveraged=2

    12/14 Apples 0.30

    SumPrice= 0.70+0.30=1.00, TimesAveraged=3
    Average price is now 1.00/3=0.33

    would I just need to use a temp variable?
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Changing the value everytime a new purchase is made is not a good way to go about it. It would be easier to store each new purchase as a new record in a table. Then use a simple aggregate(sp?) query to do your calculations.
    Given the table [Purchases]:
    ID ItemID Price PurchaseDate
    1 1 .25 12/18/2012
    2 1 .50 12/17/2012
    3 1 .65 12/25/2012

    the query :
    Code:
    SELECT Purchases.ItemID, Sum(Purchases.Price) AS SumOfPrice, Avg(Purchases.Price) AS AvgOfPrice, Count(Purchases.Price) AS CountOfPrice
    FROM Purchases
    GROUP BY Purchases.ItemID;
    would yeild results like
    ItemID / SumOfPrice / AvgOfPrice / CountOfPrice
    1 / 1.4 / 0.466666666666667 / 3
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    So my current set up is:
    Open up (form) Product Detail, click a (subform) tab and the purchase history for that product is listed

    subform: ProductPurchasesDatasheet
    Limit master fields: ID
    Limit child fields: ProductID

    that is from the template desktop Product inventory database, then in that same window I'd like to average the prices paid for those purchases, I wasn't sure how to do that so I made a new query (which I'm sure isn't necessary, since like I said it already lists them), limited to the product that is selected, and made a report based on that, which has the averaged price. It works but is pretty redundant. Can't I do something based on what the template already has?

Similar Threads

  1. Getting the average
    By spiderjolly in forum Microsoft Access Help
    Replies: 9
    Last Post: February 12th, 2010, 03:23 PM
  2. Vedio file not running on webserver but running in local host
    By godbless us in forum .NET Development
    Replies: 0
    Last Post: June 25th, 2008, 11:25 PM
  3. Button - detect function that is running and is done running?
    By fletchsod in forum .NET Development
    Replies: 3
    Last Post: January 11th, 2008, 02:19 PM
  4. Running Balance As Opposed To Running Total
    By OSULLIPA1 in forum Microsoft Access Help
    Replies: 2
    Last Post: March 14th, 2005, 11:07 AM
  5. age and average
    By klur2002 in forum Microsoft Access Help
    Replies: 6
    Last Post: February 25th, 2005, 09:10 AM

IMN logo majestic logo threadwatch logo seochat tools logo