Calculation in Form (1 Viewer)

[hansbroekhuizen]

I've got a table with the fields:
Name, test1, test2, test3, test4, test5

Question 1:
I have made a form of the table and I want to make some calculations with test1 until test5. These calculations are the standard deviatoin, the highest input and the lowest input.
* How can I calculate these 3 things?

Question 2:
I've managed (thnx to this form) to calculate the avarage of the tests. I've did it by put this in the expression=(-- Nz ([test1]) + Nz ([test2]) + ... + Nz ([test5]) / 5).
*Could this be done more easy?

 

[aziz rasul]

Q2. Try the Avg function. Alternatively, in a select query, you can calculate the average by pressing the "Totals" button and select Avg in the Total: row.

 

[Pat Hartman]

The only way to resolve your calculation issue is to normalize the table structure. You have a 1-to-many relationship and the many side (test1,test2,...) data needs to be stored as individual rows in a related table. Currently you have what is referred to as a "repeating group". This is common practice in spreadsheets but poor practice once you move to a relational database. Spreadsheet functions work either with rows of a single column or columns of a single row or even descrete cell entries. Database functions ONLY work with rows.

 

[jonnie_c]

if you follow Pat's advice you will be able to use the DMin, DMax, & DStDev functions to Calculate the numbers you want. You Will also be able to use DAvg for Q2.

 

[jonnie_c]

If you wanted to be a rebel, however, you could also write functions to calculate these things.

E.g. Set the Control Source of your 'Minimum' textbox to =GetMin()

Then put this function into your code

Function GetMin() As Long
Dim i As Integer
Dim min As Long

min = Me.test1

For i = 2 to 5
If Me("test" & i) < min Then min = Me("test" & i)
Next
GetMin = min
End Function

If you do this, don't tell Pat, though.