average of averages 1

[drkhelmt]

Greetings.

I have an order form in which the sales force can see their open order and see the production done so far. The production is listed in a list box which simply lists what machine, the feet, and the labor hours, then avg(feet/hours).

This is simple and works fine. At the bottom I have a text box which simply does an avg(sum(feet)/sum(hours)). What also want is an average of the averages in the list bos- avg(avg()).

Any ideas on this?? I tried davg("avg(...)" but came up with #Error. I want to shy away from davg due to possible performance concerns, but if thats the way it has to be, then so be it.

Thanks in advance for help.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[MajP]

If I understand right, you currently have a grand average which is the total feet of all machines divided by the total hours for all records. Each record also has a rate which is feet/hours. I could not tell if those are labels are functions. You have avg(sum(feet)/sum(hours)). Does that mean you are taking the avg of sum(feet)/sum(hours) or the average = sum(feet)/sum(hours). I thought you meant the latter. If that is the case this works but not sure if performance wise it helps. I made an aggregate query with one calculated field which was your averages. Each records feet/hours. Then I aggregated the average. Since there is only one record in this query (the avg of rates) you could make it a subform, or use dmax,dmin or any method to return this value. You could even make a function that returns this value and stick the function write on your form.
I tried to do like you did, and do an aggregate function on a aggregate funtion. Not only did the aggregate of the aggregate return #name, but the original control did also. Do not understand that.

 

[drkhelmt]

Salutations MajP,

Thank you for your response. Let me see if I can clarify... my table are:
clProduction(_clProductionID_,orderID,machineID,feet,hours)
clOrders(_orderID_,employeeID,feetOrdered,...)

I have a clOrderForm in which the sales people can see their open order. Now, this form has an unbound list box with the records of clProduction. The list box has 4 columns- machineID,feet,hours,average. Average is a calculated feild of the average feet/hour for that run. The accounting staff like two averages- the average avg(sum(feet)/sum(hours)) and average of the averages avg(avg(sum(feet)/sum(hours))).

The question is what is the best way to get these two averages on the form.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[MajP]

Same idea I think. Make query one an average aggregate query where you group on order ID and take the average of feet/hours. You are already doing this on the form I think. Then take this query, and aggregate on the average field. This would give the averages of averages for each Order. I would build these aggregate queries and then link to the order ID, instead of trying to calculate on the form. It will be faster if you do the calculations on the form, but probably easier to figure out with the aggregate queries.

 

[drkhelmt]

Thanks MajP. I think I always knew thats what I'd have to do and just needed to be told.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?