Expand|Select|Wrap|Line Numbers
- 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
=Median("<TableName>", "<FieldName>")
My problem is that within my db there are only 2 fields, [gdate] and [reading]
gdate = date the reading was taken
reading = actual reading taken
I need the database to derive the median from the month the reading was taken without considereing the day or year that the reading was taken (i have readings from the last 120 years)
When i filter the data (by right clicking and choosing one of the automatic date filters to filter by month) the median doesnt change because it is choosing the median from the entire table.
Does anybody know how to get the median () fuction to work on the filtered data, and not the whole table. If i have to i know that i could just create 12 tables, but i was hoping not to do that.