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

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3

    calculated field based on query


    My setup:
    Tables: Products, PurchaseDetails
    I have a PurchaseOrder form which allows me to select products and add them to the purchaseDetails table. I would like to be able to view the average purchase price when adding them however I am not sure how to do this
    My PurchaseOrder form is a multiple items form so I can add multiple products. When I try to add a query to display the average price paid it averages all the products prices, not the prices for each product paid previously

    Example:
    1/1/13 purchase
    Apple Price Paid today: $1
    Orange $1.5

    1/2/13
    Apple $2
    Orange $2

    what I'd like to do is:
    New purchase
    Apple avgPrice:1.50 Price Paid today: $1

    Then next time the avgPrice would be (1+1.5+1)/3
    I've tried to add a query to the form, but it shows the average price for the orange and apple

    I was thinking I could add a calculated field in the product table based on the query of avgPrice paid for that ProductID, not sure if that is possible
    If not perhaps I could do an afterUpdate Event on the purchase Table, I would want to run a query and set that result in the product table. I don't know how to run a query from there though, I am using macro tools, I don't really understand VBA so I'd rather steer away from that incase I need to tweek it in the future

    Thanks for any help
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    Why not add a subform that is based upon an aggregate query that is filtered (criteria) on the particular item you have selected in the main form.

    Here is a link to building an aggregate query

    calculated field based on query
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    I tried that but once I added the aggregate query to the source, the form no longer allows me to add records
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
  8. #5
  9. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Try allen's suggestion and create a subform and use the aggrate query as the record source for that subform. You are correct, once you add an aggragate query to a recordsource it becomes non-updateable, but a subform would allow the main form to still be updateable.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    That kind of works. I made two separate subforms, one updateable, one for the average. The one with the average does not update as I add new products though. The average is meant to be a reference so it needs to be able to update in real time otherwise its not very useful in that form.
    I suppose I could do a refresh? or Requery? Not sure how to use either of those, i've been just calling the command close form then open form
    Also, assuming I can get it to refresh each time, im having difficulty getting them to scroll together

    Thanks for the help!
    Last edited by samela101; February 15th, 2013 at 09:55 AM.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    bump

Similar Threads

  1. Replies: 7
    Last Post: October 29th, 2012, 09:13 PM
  2. Calculated Field based on Other Fields In Same Table
    By nmrosen in forum Microsoft Access Help
    Replies: 3
    Last Post: July 15th, 2008, 06:27 PM
  3. Calculated Field in a Query
    By tuktuk in forum Microsoft Access Help
    Replies: 1
    Last Post: April 24th, 2006, 01:22 PM
  4. Question on query to flag a field based on calculated data
    By Cruisemate in forum Microsoft Access Help
    Replies: 4
    Last Post: April 26th, 2005, 01:30 PM
  5. A calculated field in a query based on 2 DB fields
    By JimK in forum Microsoft Access Help
    Replies: 1
    Last Post: June 23rd, 2004, 04:28 PM

IMN logo majestic logo threadwatch logo seochat tools logo