Help finding the Median using MS Access

[Dave Smith]

I’ve spend the last day researching online how to make access calculate the Median.

I really hoping someone can help me find what I need to do to find the median Number of Loans w/Ovg Pts for each name.

I have also attached my sample database so you can see what I’m working with.

I have found code, but not know VBA I don’t know how it would work

Any help would be greatly appreciated

Function Median (tName As String, fldName As String) As Single
  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim RCount As Integer, i As Integer, x As Double, y As Double, _
      OffSet As Integer
  Set MedianDB = CurrentDB()
  Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _ 
            "] IS NOT NULL ORDER BY [" & fldName  & "];")
  'NOTE: To include nulls when calculating the median value, omit
  'WHERE [" & fldName & "] IS NOT NULL from the example.
  ssMedian.MoveLast
  RCount% = ssMedian.RecordCount
  x = RCount Mod 2
  If x <> 0 Then
     OffSet = ((RCount + 1) / 2) - 2
     For i% = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     Median = ssMedian(fldName)
  Else
     OffSet = (RCount / 2) - 2
     For i = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     x = ssMedian(fldName)
     ssMedian.MovePrevious
     y = ssMedian(fldName)
     Median = (x + y) / 2
  End If
  If Not ssMedian Is Nothing Then
     ssMedian.Close
     Set ssMedian = Nothing
  End If
  Set MedianDB = Nothing
End Function 

Back to the top
How to Use the Median() Function
Create a form and add a text box control where you want to display the median values of a data set. Set the ControlSource property of the text box control to the following:
=Median("<TableName>", "<FieldName>")


The value of this control is the median of the data set. Another way to use this function is to call it from within another function that compares the median from different data sets. For example:

 Function CompareMedians()
      Dim MyDB as Database
      .
      .
      .
      X = Median("<TableName>", "<FieldName>")
      Y = Median("<TableName>", "<FieldName>")
      If X > Y Then Debug.Print "The median for X is greatest."
   End Function

[NeoPa]

From what I can see of your post you have everything you need. The Median function appears to do a pretty decent (It doesn't handle even numbered record counts very well but that's just being picky) and you include code that illustrates you know how to call it.

What is your question?

[Dave Smith]

Thanks NeoPa, could you help my understand what I need to do after I paste the VBA. Thank you soon much

[NeoPa]

Pretend I don't already know what you're thinking, and explain again what you want.

Third time lucky.

[Dave Smith]

NeoPa, you must be a teacher or one in your past life.:0)

I want it to return the median of all records in the field, but the median for all records for a given LoanName

I would guess the formula looks something like this

Median("MyTestTable","w_Ovg_Points","LoanName = ",LoanName)

[NeoPa]

Dave Smith:
NeoPa, you must be a teacher or one in your past life.:0)

There's certainly a deal of similarity answering questions on a forum. If only more people had the experience of reading the questions they write down, they'd see how often they make barely discernable sense. Not a problem. One gets used to it, but one still can't help much until the question makes sense, so we sometimes have to prompt for clarification. All in a good cause at the end of the day of course.

On to the question.

What you require is not actually (directly) supported by the procedure you've posted, which leaves us two options :

1. Update the procedure to accept another parameter to include in the WHERE clause (Essentially specify extra filtering). This is possible, but more work than option #2.
2. Be a bit clever and pass a SQL SELECT string as the tName parameter enclosed in parentheses () and pre-filtered to match your [LoanName] so that the function uses that instead of a table.
   

   Dim strSQL As String
    
   strSQL = "(SELECT [w_Ovg_Points]" & _
            " FROM   [MyTestTable]" & _
            " WHERE  ([LoanName]='" & Me.LoanName & "')"
   BlahBlah = Median(strSQL, "w_Ovg_Points")

[Dave Smith]

Please forgive my ignorance. Would this code go in conjunction with all the code above?

[NeoPa]

Yes it would. It calls the function Median(), included in your first post.

[Dave Smith]

Hi NeoPa, I know you;ve been a huge help already, but now I'm getting the error below?

There was an error compiling this function.
The Visual Basic module contains a syntax error
Check the code, and then recompile it

[NeoPa]

What did it tell you when you compiled it? It should give an error message and a line number. This information is quite important if I'm to have a clue what's going on.

[ADezii]

Function Median(tName As String, fldName As String, strLoanName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
    OffSet As Integer
Dim strSQL As String
 
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
strSQL = "SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & _
         "] IS NOT NULL AND [LoanName] = '" & strLoanName & _
         "' ORDER BY [" & fldName & "];"
 
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset(strSQL)
 
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
 
If x <> 0 Then
  OffSet = ((RCount + 1) / 2) - 2
    For i% = 0 To OffSet
      ssMedian.MovePrevious
    Next i
    Median = ssMedian(fldName)
Else
  OffSet = (RCount / 2) - 2
    For i = 0 To OffSet
      ssMedian.MovePrevious
    Next i
    x = ssMedian(fldName)
    ssMedian.MovePrevious
    y = ssMedian(fldName)
      Median = (x + y) / 2
End If
 
If Not ssMedian Is Nothing Then
  ssMedian.Close
    Set ssMedian = Nothing
End If
  Set MedianDB = Nothing
End Function

[Dave Smith]

How would I call this function Median([Feildname])?

[ADezii]

You cannot supply a Field Name only without modifying the Code. The Syntax would be:

Dim sngMedian as Single
sngmedian = Median("<Table Name>","<Field Name>","<Loan Name>") 
 
'Supply the appropriate Values between the <...>