Code for calculating grouped records - clarification

[Coding]

If you have some 150 records in a database table and want to do calculations of the groups of records. For example 4 records per each group and to calculate averages for each group. Then, you should get 150 / 4 = 37. That 37 new records with remaining 2 records should be grouped and calculated. I need to get a code that will group and calculate the records for each group and for an eventually remaining records.
Thank you

 

[MichaelRed]

Coding,

If there is some criteria to do the grouping, Then
[tab]you can do this via query (or queries), depending on the actual function.
Else
[tab]you will need a nested loop
[tab][tab]While not Rs.EOF
[tab][tab]tab]For Idx = 1 to 4
[tab][tab][tab][tab] Code here to average four things and use it. Also need to take care of the EOF thing
[tab][tab][tab]Next Idx
[tab][tab]Wend


MichaelRed
There is never enough time to do it right but there is always time to do it over

 

[Coding]

Michael,

I will need a loop:
Do While Not rs.EOF
For Idx = 1 to 4
Code for calculation
Next Idx
Loop
because there is no criteria for grouping,
but I need more code after the first For... Next loop. In fact how to automate the loop to do next four records, and next four, an so on until the end of file, and how to calculate possible remaining records (in this case 2).
Thank you.

 

[MichaelRed]

Coding,

? please clarify. Why do you need 'more code' after the first For loop? The inner loop will repeat untill hte EOF is encountered. I agree that you need "code" in the inner loop to trap the EOF (which will occur)

Public Function AvgVal() as single
Dim Val() as Single 'Could be double?

rs.MoveFirst
'Here I am Assuming the Field to be averaged is 'MyVal'
Do While Not rs.EOF
ReDim Val(4) 'Lazy way to clear the array
For Idx = 1 to 4
Val(Idx) = rs!MyVal
'Capture Values for Calculation Here
rs.MoveNext
If (rs.Eof) Then
GoTo CalcNow
End If
Next Idx
'Code for calculation
CalcNow:
Jdx = Idx - 1
For Idx = 1 to Jdx
Val(0) = Val(0) + Val(Idx)
Next Idx

AvgVal = Val(0) / Jdx 'Here, You need to use
'the averaged value
'
rs.MoveNext
Loop

End Function

 

[Snaggs]

I belive you can do this in the SQL statement using Aggregate functions in SQL. How about something like this:

SELECT Student, Avg(Grade) AS AvgGrade FROM Students GROUP BY Student

This should give you something like this:

Steve 85
John 97
Sarah 84
Kelly 91

Then just use the VB code below to read the data.

lblStudent.Caption = rsGrades!Student
lblAvgGrade.Caption = rsGrades!AvgGrade

Of course this is assuming that your data is already grouped in sets of 4 as you mentioned in your original post. The example provided above will work for any number of sets in a given group, weather it's 4 or 2 as you mentioned. You just have to determine the field that you want to group on.

Let the SQL engine do the work for you. It'll be much faster.