Calculating median in a report

[ZikO]

Hello,
It is probably trivial question I have no experience, yet.
I've found a function that calculates a median and I need to apply it in report. How to alter the function I found? Currently, this function takes two parameters: table name and field name. To make it work, should I drop the table name parameter and use something like current Table? How can I refer to current table? Thanks for help

PS> The code has been taken from this address:
http://support.microsoft.com/?id=210581

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

 

[pr2-eugin]

How can I refer to current table?

Could you explain what you actually mean by Current table? Since it involves a Parameters..

* tName - Table Name - It denotes the name of the table that holds the column (field) for which you wish to find the median of.
* fldName - Field Name - It denotes the name of column (field) for which you wish to find the median of.

So say you have a table mathNumbers with column medianData, in whihc case the function will be accessed as

Median(mathNumbers, medianData)

 

[ZikO]

Hi pr2-eugin,

Thanks for the answer.

Could you explain what you actually mean by Current table? Since it involves a Parameters..

I may be wrong here because I am not experience at this. However, by the current table, I mean something that a report is bound to. It could be probably a table or a query. I may unintentionally refer to RecordSource but I don't even know how to handle it. In terms of a Form or a regular table/query, I can understand how this function works. It just takes all records from a field in a table/query and calculates a median.

How does this function work with Reports, if it can? This would be my main question. It confuses me because in reports you can make sort of sections according to field groups, sort them, and calculate sums, averages, counts, etc. For instance, if I have months and sums of expenses categorized by different kind of expenses:

_________________________
Food
          Jan    £200.0
          Feb    £400.0
          ...
          Dec    £360.0
----------------------------
Sum              £960.0
Avg              £320.0
Median           ???
_________________________
Entertainment
          Mar    £50.0
          Aug    £80.0
          ....
Sum              £130.0
Avg              £65.0
Median           ???
----------------------------

how does Median() work along other similar functions such as Sum(), and Avg(), etc. What do I need to be aware of when I write a code for Reports? BTW, in reports, you can create a regular textbox (a control) and tell ACCESS to calculate anything using this one of these functions: "=Sum([Field Name])" etc. I still haven't figure out what to do to make this function work like that!

Thanks

PS.