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 I´d 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.