Oops, I just noticed "138-230" as a grouping level in your post. That means that you are not using the "hundreds" portion of the number shown in the first column. I suppose you could add a new column to your table that defines your desired grouping levels. Make this column a number (integer datatype) and assign values to each record according to which group you desire.
tblPercentages Counter PercentOfX PercentOfY PercentOfZ Group
110 5 15 80 1
112 15 15 70 1
138 20 20 60 2
200 60 30 10 2
230 50 50 0 2
340 25 50 25 3
360 25 25 50 3
The new query becomes:
SELECT tblPercentages.Group,
Avg(tblPercentages.PercentOfX) AS [Avg % X],
Avg(tblPercentages.PercentOfY) AS [Avg % Y],
Avg(tblPercentages.PercentOfZ) AS [Avg % Z]
FROM tblPercentages
GROUP BY tblPercentages.Group;
And the formatted result is as follows:
qryStats Group Avg % X Avg % Y Avg % Z
1 10.00 15.00 75.00
2 43.33 33.33 23.33
3 25.00 37.50 37.50
Tom
_______________________________________________
It's not exactly clear what your grouping rule is, but it appears to be based on the "hundreds" portion of the number shown in the first column. If this is the case, you can group by the first digit of this field: Statistics: Left([Counter],1) & "00 Level"
I created a new table named tblPercentages. This table includes four fields, all Number data type, with field size = Long Integer:
Counter <-----Primary Key
PercentOfX
PercentOfY
PercentOfZ
I then filled in some numbers, where the percents of X, Y & Z summed to 100 for each record (not sure if this applies in your case). My table, in datasheet view, appears as follows:
tblPercentages Counter PercentOfX PercentOfY PercentOfZ
110 5 15 80
112 15 15 70
138 20 20 60
200 60 30 10
230 50 50 0
340 25 50 25
360 25 25 50
I then created the following query:
SELECT Left([Counter],1) & "00 Level" AS Statistics,
Avg(tblPercentages.PercentOfX) AS [Avg % X],
Avg(tblPercentages.PercentOfY) AS [Avg % Y],
Avg(tblPercentages.PercentOfZ) AS [Avg % Z]
FROM tblPercentages
GROUP BY Left([Counter],1) & "00 Level";
The results of running this query look like this:
qryStats Statistics Avg % X Avg % Y Avg % Z
100 Level 13.3333333333333 16.6666666666667 70
200 Level 55 40 5
300 Level 25 37.5 37.5
You can apply formats to each field if desired. For example, a Fixed format with 2 decimal places yields the following result:
qryStats Statistics Avg % X Avg % Y Avg % Z
100 Level 13.33 16.67 70.00
200 Level 55.00 40.00 5.00
300 Level 25.00 37.50 37.50
Is this what you are looking for?
Tom
______________________________________
I have a query that looks similar to the one below. I need to combine, for example, 100-112 , 138-230 and 340-360 together and average the percents. What is the easiest way to do this? :
110 Percent of X Pecent of Y Percent of Z
112 "" "" ""
138
200
230
340
360
