I am trying to get the Median for Groups within a Table

[Eric branton]

The database is set up with Mission and within each mission are sets. Each set contains aproxmently 150 records that date/time temp, depth. I want to find the median for both temp and depth for each set. I have used the function provided by the MS Access support page but it calculates the median for the whole data base not each set. How would I get it to sort the data before finding the median.
thanks
-Eric

[NeoPa]

Not very clear Eric (You should fix the typos before posting). What function are you using? What SQL have you tried?

I would expect you would need to GROUP BY whatever the sets are, but you haven't explained that properly so I have very little to work with.

I'm sure if you take the time to present the question fully and without errors we can help you to a solution.

[Eric branton]

Ok thanks

The database is of depth and temperature records.

The columns are named

Mission, Set#, DateAndTime, Temp, Depth

Each Mission contains many sets. Each set contains about 150 different records from every 12 seconds between the start and end time. I have created a module with the code from the MS Access help page…

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 & "];")
    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

When I run this it gives me the median of the either database. I was hoping to be able to create a module that would allow me to find the median of each set.

[TheSmileyCoder]

The basic principle is to modify your SQL statement within the function.

You would need to make 2 modifications to your described function. First make room in the function calling to accept the Set Nr, and mission NR (assuming its a nr)

Function Median(tName As String, fldName As String,lngMissionNr as long, lngSetNr as long) As Single

Then modify the SQL's where clause.

SELECT [" & fldName & "]" & _
            " FROM [" & tName & "] " & _
            " WHERE [" & fldName & "] IS NOT NULL " & _
            " AND Mission=" & lngMissionNr & _
            " AND [SET#]=" & lngSetNr & _
            "ORDER BY [" & fldName & "];"

[ADezii]

Wouldn't you have to GROUP BY Mission and Set#, then Calculate the Medians for both Temp and Depth?

[Eric branton]

I tried to use the
[SELECT [" & fldName & "]" & _]
but the module did not like it and marked it as red.

[TheSmileyCoder]

An answer like that gives NOTHING to work forward from. You have not shown HOW you use it, you only post a fraction of the code, so I have no idea whether its because you simply do not grasp the concept, or was to lazy to post it. You enclose it in square brackets which makes no sense to me as a reader. Come back and try again.

[NeoPa]

I think the confusion here Smiley (and Eric) is that post #4 has code that starts in the middle of a line of VBA. Most of us that do this a lot would probably have recognised that as a simple oversight, but I expect Eric was confused by it and tried to make some sense out of it, but with little experience of the subject failed to see what was going on.

Smiley's code should probably have started something like :

   strSQL = "SELECT [" & fldName & "]" & _
            " FROM [" & tName & "] " & _
            ...

NB. In Access one often has to work with SQL strings, and this gets particularly complicated when creating SQL strings from within VBA code (as in this instance). I hope this clarifies the situation somewhat :-)