Hi

I am writing a small DB to record and report class information for a school.
I have a table called Student_info. This table contains all of the students information (one record per student).

Included on this tables is a series of module scores. All i want to do is average those scores when the last one has been entered.

I have created a form called Student_info1, this contains all of the entry text boxes bound to the fields in the table. The average field is not on the form.
To make this calculation easier I am happy to make it happen as an "On enter" event on the last text box of scores.

Also I am happy to "fudge" the average by using something like:
ave = (sc1 + sc2 + sc3 + sc4 + sc5 + sc6)/6

I just don't know know how to call the right fields and output to the average field on the table. The average field is called Average_1st_term; the table is called Student_info ...

Help would be most appreciated! ;)

Rob :confused:

You may run into a few issues here. First there could be missing values between 1 and 6 so the fact that there is something in the last textbox does not imply that the remainder have been completed. My thoughts would be to create a function and test this function each time a value is entered in to any of the fields.


Public Function GetAverages(Arg1 As Integer, Arg2 As Integer, Arg3 As Integer, Arg4 As Integer, Arg5 As Integer, Arg6 As Integer) As Double

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim X As Integer
Dim Z As Integer

'Apply a factor to average
If Arg1 > 0 Then A = 1
If Arg2 > 0 Then B = 1
If Arg3 > 0 Then C = 1
If Arg4 > 0 Then D = 1
If Arg5 > 0 Then E = 1
If Arg6 > 0 Then F = 1

Z = A+B+C+D+E+F

'Add the scores together
X = Arg1+Arg2+Arg3+Arg4+Arg5+Arg6

GetAverages = X/Z

End Function


Then on you form on the AfterUpdate Event of each textbox call the function

Me.TxtAverages = GetAverages(Nz(Me.Score1),Nz(Me.Score2),Nz(Me.Scor e3),Nz(Me.Score4),Nz(Me.Score5),Nz(Me.Score6))

So if at any time any one of the values are entered or updated then the average will be automatically calculated based on the number of scores entered.

David

David

Wow, that is a neat solution ... I can clearly see what you have done.

How can I get this to update my field "Average_1st_term" please?
I am thinking about putting an invisible bound text box on the form ..? The text box being called "TxtAverages" ....

Thanks a lot for your help
Rob :D

Your thinking is correct have a textbox on your form that is invisble that holds the calculated average. Then on your control that is linked to the table set the control source to this field. However as this is a calculation you should not be storing this figure in a table. Your query can use this function to automatically calculate the average in much the same way as it does on the form.

So in a query you would have:

Avg:GetAverages([Field1],[Field2],etc)