Rabu, 25 Mei 2011

RE: [MS_AccessPros] How Do I get The Median Number In a Query

 

Cathy-

You need your own DMedian function:

Function DMedian(strFieldName As String, strDomainName As String, Optional
varWhere As Variant) As Variant
' Function to calculate the Median numeric value of a column in a domain
' Input: Name of the field, Name of the domain, Predicate (optional) - all
strings
' Output: Median numeric value or Null if the field is a string or error
encountered
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, lngRecords As Long, intVarType As Integer
Dim varValue As Variant, lngRows As Long, strWhere As String
On Error GoTo DMedianErr
' Set default return of Null
DMedian = Null
' Check for possible "missing" third argument
If Not IsMissing(varWhere) Then
If varType(varWhere) = vbString Then strWhere = varWhere
End If
' Point to the current database
Set db = DBEngine.Workspaces(0).Databases(0)
' Open the domain sorted by the field
strSQL = "SELECT [" & strFieldName & "] FROM [" & strDomainName & "]"
If Len(strWhere) <> 0 Then
strSQL = strSQL & " WHERE " & strWhere
End If
strSQL = strSQL & " Order By [" & strFieldName & "];"
Set rst = db.OpenRecordset(strSQL)
' If no rows, then return Null
If rst.EOF Then
rst.Close
Exit Function
End If
' Get the number of rows
rst.MoveLast
lngRecords = rst.RecordCount
rst.MoveFirst
intVarType = varType(rst(strFieldName))
' If the data type of the field isn't a number, then return Null
If intVarType < 2 Or intVarType > 7 Then
rst.Close
Exit Function
End If
' Calculate the "middle" of the recordset
lngRows = lngRecords \ 2
' If an even number, then do an average
If lngRecords Mod 2 = 0 Then
rst.Move lngRows - 1
varValue = rst(strFieldName)
rst.MoveNext
varValue = (varValue + rst(strFieldName)) / 2
Else
rst.Move lngRows
varValue = rst(strFieldName)
End If
DMedian = varValue
' Be sure to return the proper data type
Select Case intVarType
Case vbInteger
DMedian = CInt(varValue)
Case vbLong
DMedian = CLng(varValue)
Case vbSingle
DMedian = CSng(varValue)
Case vbDouble
DMedian = CDbl(varValue)
Case vbCurrency
DMedian = CCur(varValue)
Case vbDate
DMedian = CDate(varValue)
End Select
rst.Close

DMedianBail:
Exit Function

DMedianErr:
MsgBox "Error in DMedian: " & Err & ", " & Error
Resume DMedianBail

End Function

You call it just like you would any other Domain function - such as DLookup or
DMax. Put the code in a Standard module and compile and save it.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Cathy Davis
Sent: Thursday, May 26, 2011 2:38 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] How Do I get The Median Number In a Query

I have looked in the expression builder as well as the totals row in the query
structure to find a way to get the MEDIAN number of a group of values within a
table, but have not been successful.  Does anyone have an idea of how to query
for that?

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar