Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Question Unanswered: Average Price...without making recordset not updatable...

    A Deal can consist of x Purchase Orders and y Sales Orders. All PO:s and SOs have a unit price. And each PO and SO have their own record, with their own POID and SOID. They are related by a common Deal ID.

    Later on I want to calculate margins etc, but for now Id like to start with a field on my Dealform that takes an average of all the Purchase and Salesprices per unit.

    In other words: price1+price2+price3...priceX / X
    I know there is an average function in Queries but cant get that to work. It will only take an average of PO1 which of course is = PO1. Furthermore I end making the recordset not updatable all the time when i mess with that. I havent quite been able to figure out what makes this happen, but guess it is incompatible conditions or something. So Id rather do an expression in my query:

    Something like... AvgPrice: sum[unitprice] in all records / recordcount. Cant get the syntax right. But it should be doable, shouldnt it?

    Could it be done directly on an unbound control? I have the PO:s and the SO:s in Subforms on my DEALform.

    Johnny

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The avg function works for the situation you have described. Post your query that you tried using the avg function.

  3. #3
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Thanks!
    Cant seem to understand this recordset not updatable either... Very annoying.

    Jo

    mdb attached
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •