Get Average of Averages

[thawstone]

I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1

First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.

If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages.
HOWEVER, if I use the expression:

AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))

…to get a more accurate average by throwing out the high and low values, I get a:

“…expression too complicated to be evaluated”

when I try to average the averages in the second query using the AVG function.

I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
Does anyone have experience with this?

 

[Galaxiom]

I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.

Queries are not run like that. An execution plan that combines the queries is generated and run as a single query.

You do have two missing closing parenthesis on your expression but I would have expected that specific error rather than "too complicated". Confirm that you have this correct.

 

[plog]

SQL aside, this is a bad idea mathematically.

Color, Quantity
Red, 2
Red, 6
Blue, 110
Blue, 116
Blue, 112
Blue, 118
Blue, 114
Green, 1
Green, 3

Based on the above data, this is the average by color:

Red, 4
Blue, 114
Green, 2

Average the averages by color and you get 40 (120/3). However, the correct average is 64.67 (582/9).

 

[thawstone]

Galaxiom - the parenthesis are there (I corrected the post), they got left out when I did the cut and paste. Read below. I think I'm going to make an adjustment, although it may still be too "complicated" for Access.

plog - your argument doesn't hold up in this case. The base repair price for all the branch part numbers is the same, however, every now and then, a customer will spend an extraordinary amount of money for a repair that doesn't reflect the market value of the product, and it unduly affects the average. Now that I think about it, rather than throw out the high and low, I should just have an upper limit that excludes all extraordinary repairs. I'll try that instead. Thanks.

 

[thawstone]

On second thought... I can't do a high limit due to the wide variety of repair prices. What's reasonable for one product may be unreasonable for another. I think tossing out the high and low is the best I can do. So, I'm back to square one. Is there a way to uncomplicated the solution?

 

[plog]

The base repair price for all the branch part numbers is the same

Then why calculate the average?

There's about seven billion people in the world all with two eyes (just like your example, let's throw out those with 0 or 1 and those Chernobyl/Fukishima babies with 3). Instead of using a computer to add up 2 for every person in the world and then divide by 7,000,000,000 to get the average number of eyes, why not just say 2 and skip the math altogether?

If the base price is all the same, then no need for math, the average is the base price.

 

[thawstone]

I simplified the problem for posting. There's actually labor + parts cost. There's a base labor cost shared by all the sub part numbers, but it can go up or down if the repair is unusually easy or difficult. It's the parts cost that usually varies greatly. Customers call up and ask what our average repair cost is for a particular part number. It helps to be able to break it down to average labor and average parts to give them a better picture, and using the root part number gives a better statistical pool to draw from. Now quit conjecturing and help me with the problem.

 

[plog]

I don't think I'm conjecturing--your post title is 'Get Average of Averages'. I explained why that's not a good idea.

I'd be happy to help you achieve any results you ask for (even if I don't think those results are what you actually want). Can you post some sample starting data (include field and table names) and then post what data should result from that sample data? Use this format:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 16, 12/1/2012
Jim, 14, 1/31/2012
Sue, 27, 2/5/2013

 

[MarkK]

Lol, Chernobyl/Fukishima babies. That's so wrong.