Calculating median from a recordset!

[sillylady]

I have a recordset which holds data from a query. Data is something like this?

Name---- R1 ---- R2 ---- R3
A-------- 1 ---- 2 ---- 3
B-------- 2 ---- 3 ----- 4
C-------- 3 ----- 4 ----- 5

What I want is to transfer R1, R2 and R3 values for A,B and C to an array and then calculate median of that array and display the median value in a Grid for each Ratios (R1,R2,R3). Below is my code:

Do While Not adoPrimaryRS.EOF
      For i = 0 To adoPrimaryRS.Fields.Count - 1
             
             If adoPrimaryRS.Fields(i).Name = "R1" Then
             .TextMatrix(lngRow, lngCol) = Format(adoPrimaryRS.Fields(lngRow).Value, "0#.00")

             intArray(i) = Format(adoPrimaryRS.Fields(i).Value, "0#.00")
             End If
      Next i
        'move on to next record
      adoPrimaryRS.MoveNext

The problem is that Array contains only last value of R1 not all the value of R1. Any help or any suggestion to solve the problem.


Thanks

[brucevde]

Do you want to sum the values of each column into the array? If yes use

intArray(i) = intArray(i) + adoPrimaryRS.Fields(i).Value

or

Do you want to simply transfer the recordset into an array? For this you will need a two dimensional array or use the Recordset.GetRows method.

[Al42]

Why not just calculate the median from the values in the grid? Do you need the array for anything that the grid can't give you?

BTW, If adoPrimaryRS.Fields(i).Name = "R1" Then will make sure that you're only getting the value for R1, not R2 or R3. That's not what you said you wanted.

[triggernum5]

You just need to sort the set, then take the element at UBound()\2.. Here is the quicksort from the codebank..

' Omit plngLeft & plngRight; they are used internally during recursion
Public Sub QuickSort1(ByRef pvarArray As Variant, Optional ByVal plngLeft As Long, Optional ByVal plngRight As Long)
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim varMid As Variant
    Dim varSwap As Variant
    
    If plngRight = 0 Then
        plngLeft = LBound(pvarArray)
        plngRight = UBound(pvarArray)
    End If
    lngFirst = plngLeft
    lngLast = plngRight
    varMid = pvarArray((plngLeft + plngRight) \ 2)
    Do
        Do While pvarArray(lngFirst) < varMid And lngFirst < plngRight
            lngFirst = lngFirst + 1
        Loop
        Do While varMid < pvarArray(lngLast) And lngLast > plngLeft
            lngLast = lngLast - 1
        Loop
        If lngFirst <= lngLast Then
            varSwap = pvarArray(lngFirst)
            pvarArray(lngFirst) = pvarArray(lngLast)
            pvarArray(lngLast) = varSwap
            lngFirst = lngFirst + 1
            lngLast = lngLast - 1
        End If
    Loop Until lngFirst > lngLast
    If plngLeft < lngLast Then QuickSort1 pvarArray, plngLeft, lngLast
    If lngFirst < plngRight Then QuickSort1 pvarArray, lngFirst, plngRight
End Sub

[Ellis Dee]

The median is the true midpoint, meaning that it depends on whether you have an odd or even number of elements. After sorting (either the array or the grid), calling your first position First and your last position Last:

Odd number of elements:
Index = First + (Last - First) \ 2
Median = Value(Index)

Even number of elements:
Index = First + (Last - First) \ 2)
Median = (Value(Index) + Value(Index + 1)) / 2

[sillylady]

Thanks for all the reply. But it seems you guys did not get what I want. I have to calculate median from a recordset. For this I thought of this:

1. Transfer values of each ratio (R1,R2 etc) into an array.
2. Sort the array and find the median
3. Display it in grid cell
repeat the 1 to 3 steps for each ratio in a loop.

Thanks

[sillylady]

Any help plsssssssssss!!!

[GaryMazzone]

Do wee need the median for each column of the median for all three columns?

[sillylady]

Do wee need the median for each column of the median for all three columns?

Yes I need to calculate median for each column.

[GaryMazzone]

I Query the database 3 time. Once for each column ordered by the column ascending. Get a count of then number of records in the set. If Odd the median is the middle record of the set. If even then calc the middle (say there are 12 records then middle would be six) and add the middle and the one after and divde by 2. (add six and seveth record value together and divide).

[sillylady]

I Query the database 3 time. Once for each column ordered by the column ascending. Get a count of then number of records in the set. If Odd the median is the middle record of the set. If even then calc the middle (say there are 12 records then middle would be six) and add the middle and the one after and divde by 2. (add six and seveth record value together and divide).

Thanks for the idea. I had this before but the problem is that I would have to run the query 40 times because there are 40 such ratios in real data. Will it be wise idea to run query 40 times?

[GaryMazzone]

I don't think you will have much of a choice. Unless you build arrays for each and order the arrays.

[sillylady]

I don't think you will have much of a choice. Unless you build arrays for each and order the arrays.

Yeah correct I have to transfer the record in an array. I tried that in a code in my 1st post, but the array holds the last value only. Though the same code transfers the value in each column of the Grid. I don't know where i am making mistakes.

[GaryMazzone]

I would delcate the array as so:

Dim arOne() as Single
Redmin arOne(0)

Open the recordset
If not BOF then

Dim i as Long
i = -1
Do Until rs.EOF
  i = i + 1
  ReDim Preserve arOne(i)
  arOne(i) = rs.Field(0).Value
  rs.MoveNext
Loop