Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: calculated field based on query

Results 1 to 7 of 7
Share This Thread →
  1. #1
    samela101 is offline Contributing User
    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
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    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.

  3. #3
    samela101 is offline Contributing User
    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

  4. #4
    samela101 is offline Contributing User
    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3

  5. #5
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    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.

  6. #6
    samela101 is offline Contributing User
    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.

  7. #7
    samela101 is offline Contributing User
    Join Date
    Feb 2012
    Posts
    43
    Rep Power
    3
    bump

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates