Temat: Liczenie mediany
Proponuje wersję ze strony MS:
Option Compare Database
Option Explicit
Public Function DMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant
' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.
Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer
Const errAppTypeError = 3169
On Error GoTo HandleErr
Set db = CurrentDb()
' Initialize return value
varMedian = Null
' Build SQL string for recordset
strSQL = "SELECT " & strField & " FROM " & strDomain
' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If
strSQL = strSQL & " ORDER BY " & strField
Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst
If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise errAppTypeError
End Select
DMedian = varMedian
ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function
HandleErr:
' Return an error value
DMedian = CVErr(Err.Number)
Resume ExitHere
End Function
Który pozwala dodawać opcjonalne warunki do pobieranego RecordSet'u.
PS.: Pamiętaj o włączeniu Microsoft DAO do referencji.
пропозиції роботи
