Weighted Averages In a query

[Ybbor]

I have developed the following query to summarise a blast design at a mine site.

Pattern Depth m Subdrill Burden Spacing BCM/hole #Holes BCM
1.00 7.00 1.00 3.00 3.00 54 10 540
1.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 4.00 3.00 72 10 720

Now there will be many patterns, the above however only contains one (called 1). Now what i need is the average Depth, Subdrill, Burden, Spacing, bcm/h. In MS Excel I would have following:

Average BCM per hole = Sum of BCM / Sum of Holes

How to do this in Access? Then just to make it more difficult I want to have the average BCM for each different pattern.

 

[Ybbor]

A solution

Ok, Well I have found a way to do this, although it required two qry's.
Firstly:
I can calculate my bcm and m (bcm=Depth*Burden*Spacing*Num Holes & m=Depth*Num Holes) This is the total of the information i wanted weighted and averaged. I had to remove all the other information out of the qry's so that I could get the group by to "group by" my Pattern Field.

Pattern Number BCM M
1.00 1170 120
2.00 1252.5 100

Then in the second qry i could divide bcm by m and get a bcm per m. As bcm and m were calculated using the number of occurances (number of holes) I have a weighted average (weight being num holes) of bcm/h

Pattern Number BCM M BCM/M
1.00 1170 120 9.75
2.00 1252.5 100 12.525