Calculating means for marks in a row

[smig]

I need some help calculating mean for marks but only for the ones that are over a specific criteria in a row.

Here is some data example and expected result
Id. Marklist mark
1 1 8
1 2 9
1 3 7
2 1 9
2 2 9
2 3 8
3 1 8
3 2 7
3 3 9

Here are the expacted results for each id when the criteria is 8:
For id 1 - mean of 8 + 9 - the 3rd. One is not in criteria
For id 2 - mean of 9 + 9 + 8 - all are over criteria
For id 3 - only 8 - though the last one is 9 they are not in a row

Thank you
Tal

 

[plog]

There might be a more elegant solution, but I can do it with 2 sub-queries. In all the below code, I used 'YourTableNameHere' for the name of your table, so replace all instances of it in my code with the actual name of your table. Also, I had to hard-code the criteria value into a calculated field of the first sub-query, its the only way it would work. So to change the criteria value you will have to edit the 'Valid' field of the first sub-query. This is that SQL:

SELECT YourTableNameHere.ID, YourTableNameHere.Marklist, YourTableNameHere.mark, IIf([mark]>=8,1,0) AS Valid
FROM YourTableNameHere
ORDER BY YourTableNameHere.ID, YourTableNameHere.Marklist;

Save that and name it 'MarkMean_sub1'. Then name this next code 'MarkMean_sub2':

SELECT MarkMean_sub1.ID, IIf(DCount("[Marklist]","MarkMean_sub1","[ID]=" & [ID] & " AND  [Valid]=0")>0,DMin("[Marklist]","MarkMean_sub1","[ID]=" & [ID] & " AND [Valid]=0")-1,DMax("[Marklist]","MarkMean_sub1","[ID]=" & [ID])) AS LastValid
FROM MarkMean_sub1
GROUP BY MarkMean_sub1.ID, IIf(DCount("[Marklist]","MarkMean_sub1","[ID]=" & [ID] & " AND  [Valid]=0")>0,DMin("[Marklist]","MarkMean_sub1","[ID]=" & [ID] & " AND [Valid]=0")-1,DMax("[Marklist]","MarkMean_sub1","[ID]=" & [ID]));

And finally the query that will do the calculation you want with the rules you laid out (excellent job explaning them by the way):

SELECT YourTableNameHere.ID, Avg(YourTableNameHere.mark) AS AvgOfmark
FROM YourTableNameHere INNER JOIN MarkMean_sub2 ON YourTableNameHere.ID = MarkMean_sub2.ID
WHERE (((IIf([Marklist]<=[LastValid],1,0))=1))
GROUP BY YourTableNameHere.ID;

The first sub-query determines which records should be included (Valid=1 means include, Valid=0 means do not include). The second sub-query finds the first Valid=0 of every ID in the first sub-query and sets the prior Marklist as the last value to use in the Average calculation in the main query.

The only issue I can think of is if the first MarkList value of an ID doesn't meet the criteria--thus meaning no records meet the criteria to be in the average. Don't know what results you would get, but it could cause an error.

 

[smig]

Thank you plog

Tal