How do I find the smallest of 4 field values in the same record o.

[Guest]

I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it in ACCESS.

 

[Van T. Dinh]

There is no inbuilt function in Access like this as in databases, the normal
requirement is to compare values of the _same_ Field of different Records.

You will need to write your own expression or custom function to do this.
Example of a cutom function follows (if you want to use this, you need to
modify if the first Field value can be Null).

********
Public Function fnMin(ParamArray varValue() As Variant)
Dim varMin As Variant
Dim intIndex As Integer


On Error GoTo fnMin_Err
varMin = varValue(0)
For intIndex = 1 To UBound(varValue())
If varValue(intIndex) < varMin Then
varMin = varValue(intIndex)
End If
Next intIndex

fnMin = varMin

fnMin_Exit:
Exit Function

fnMin_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: vbaMathematics.fnMin)" & vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMin_Exit
End Function
********
--
HTH
Van T. Dinh
MVP (Access)

I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it in

ACCESS.

 

[Chris2]

I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it

in ACCESS.


TehraniCHS,


Sample Table:
CREATE TABLE DenormalizedGrades
(GradeID AUTOINCREMENT
,StudentID LONG
,Grade1 DOUBLE
,Grade2 DOUBLE
,Grade3 DOUBLE
,Grade4 DOUBLE
,CONSTRAINT pk_DenormalizedGrades PRIMARY KEY (GradeID)
)

Sample Data
StudentID Grade1 Grade2 Grade3 Grade4
1 4.0 4.0 4.0 4.0
2 0.0 0.0 0.0 0.0
3 4.0 3.0 2.0 1.0
4 3.0 1.0 3.0 3.0
5 1.0 2.0 3.0 4.0
6 3.0 2.5 2.1 2.7


Note: There is no Foreign Key for StudentID because I skipped making
that table.


Query:

SELECT StudentID
,MultipleColumns_LowestGrade(T1.StudentID, T1.Grade1, T1.Grade2,
T1.Grade3, T1.Grade4) AS MinGrade
FROM DenormalizedGrades AS T1


The following VBA code:

Public Function MultipleColumns_LowestGrade _
(StudentID As Long _
, Grade1 As Double _
, grade2 As Double _
, grade3 As Double _
, grade4 As Double) As Double

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim dblMinimumGrade As Double
Dim bytOuterLoop As Byte
Dim bytInnerLoop As Byte

bytOuterLoop = 0
bytInnerLoop = 0
dblMinimumGrade = 0

strSQL = strSQL & "SELECT * FROM DenormalizedGrades AS DG1 "
strSQL = strSQL & "WHERE DG1.StudentID = " & StudentID & ";"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

With rs
dblMinimumGrade = .Fields.Item(2).Value
For bytOuterLoop = 2 To 5
For bytInnerLoop = 2 To 5
If (.Fields.Item(bytOuterLoop).Value <=
..Fields.Item(bytInnerLoop).Value) And _
(.Fields.Item(bytOuterLoop).Value <= dblMinimumGrade) Then
dblMinimumGrade = .Fields.Item(bytOuterLoop).Value
End If
Next
Next
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

MultipleColumns_LowestGrade = dblMinimumGrade

End Function


Results of Query:
StudentID MinGrade
1, 4
2, 0
3, 1
4, 1
5, 1
6, 2.1



Note: The above VBA code freezes the table. Any change to the Table
breaks this VBA code (badly). Any change to the VBA Code breaks the
Query. This defeats the entire purpose of SQL & Relational Databases.
The above is a stopgap useable to solve the current problem, and allow
time to successful normalize the database.

Note: This was tested on 6 rows for about ten minutes. Please
investigate further.


Sincerely,

Chris O.

 

[Chris2]

each how
to use
in ACCESS.


TehraniCHS,

D'oh!

I forgot to delete some unused parts of the Query and VBA Code.

Please use:

SELECT T1.StudentID, MultipleColumns_LowestGrade(T1.StudentID) AS
MinGrade
FROM DenormalizedGrades AS T1;

Please use:

Public Function MultipleColumns_LowestGrade _
(StudentID As Long) As Double

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim dblMinimumGrade As Double
Dim bytOuterLoop As Byte
Dim bytInnerLoop As Byte

bytOuterLoop = 0
bytInnerLoop = 0
dblMinimumGrade = 0

strSQL = strSQL & "SELECT * FROM DenormalizedGrades AS DG1 "
strSQL = strSQL & "WHERE DG1.StudentID = " & StudentID & ";"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

With rs
dblMinimumGrade = .Fields.Item(2).Value
For bytOuterLoop = 2 To 5
For bytInnerLoop = 2 To 5
If (.Fields.Item(bytOuterLoop).Value <=
..Fields.Item(bytInnerLoop).Value) And _
(.Fields.Item(bytOuterLoop).Value <= dblMinimumGrade) Then
dblMinimumGrade = .Fields.Item(bytOuterLoop).Value
End If
Next
Next
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

MultipleColumns_LowestGrade = dblMinimumGrade

End Function

 

[Chris2]

There is no inbuilt function in Access like this as in databases, the normal
requirement is to compare values of the _same_ Field of different Records.

You will need to write your own expression or custom function to do this.
Example of a cutom function follows (if you want to use this, you need to
modify if the first Field value can be Null).

********
Public Function fnMin(ParamArray varValue() As Variant)
Dim varMin As Variant
Dim intIndex As Integer


On Error GoTo fnMin_Err
varMin = varValue(0)
For intIndex = 1 To UBound(varValue())
If varValue(intIndex) < varMin Then
varMin = varValue(intIndex)
End If
Next intIndex

fnMin = varMin

fnMin_Exit:
Exit Function

fnMin_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: vbaMathematics.fnMin)" & vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMin_Exit
End Function
********

Mr. Dinh,

Well, that was a lot simpler than what I did. Thanks.


Sincerely,

Chris O.

 

[Guest]

Thank you; I have never used custom functions in Access. I am familiar with
Visual Basic and know how to pass paramters (my four grades) to the argument
list in the function you have coded. Also, I am not sure how to invoke this
function in a query? Would you please help me with that?

Thanks

TehraniCHS

 

[Van T. Dinh]

The SQL should be something like:

SELECT RecordID, fnMin(Field1, Field2, Field4, Field4) As YourMinOf4
FROM YourTable