VBA - median of a recordset

[Duritz]

Hi all - still a bit of a VBA dunce here but am learning, could someone take a look at this?

If I have large recordset read from Access (around 1.2 million records), how do I calculate the median of that recordset in VBA?

So, I would have:

Sub get_median()

Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)

Set dbsfile = wrkjet.OpenDatabase("c:\research.mdb")

sql_st = "Select * FROM data Where rate_exp < 50 "

Set rstmeet = dbsfile.OpenRecordset(sql_st, dbOpenDynaset)

What would go next?

 

[SydneyGeek]

Hi Duritz, you would need to move to the end of the recordset, to determine the size of the recordset, then move to the halfway point.
Also, the recordset needs to be sorted so that the middle record represents the middle of the range.

Here are 2 links which may help you:
http://www.fabalou.com/Access/Modules/recordset_weighted_median.asp
http://www.theofficeexperts.com/forum/showthread.php?t=6591

Denis

 

[acw]

Hi

From what I can see, there is no Median function available in Access. If this is so, then you will have to calculate your own median.

So, instead of select * in your query, change it to select field where field is the name of the field that you want to get the median (rate_exp???). Also, sort your results (select field from data where rate_exp < 50 group by field asc).

Then move to the first record
rstmeet.movefirst

You now have to get the count of records returned and work out if it is odd or even (rstmeet.recordcount). If it is odd, the you have to take the middle value (say 15 records, then take record 8 as there are then 7 numbers above and 7 numbers below). If it is even, then you have to get the 2 middle numbers and get their average (in this case average record 7 and record 8).

You can use rstmeet.move i where i is the record number you want. Have to fiddle this a bit, as the move will move you that number of records forward, not to a record.

The code below has a table access_table1 with a field col_2 that I've used as an example.

Sub get_median()

  Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)

  Set dbsfile = wrkjet.OpenDatabase("c:\temp\test2.mdb")

  Range("A:A").ClearContents

  sql_st = "Select col_2 FROM access_table1 order by col_2 asc"


  Set rstmeet = dbsfile.OpenRecordset(sql_st, dbOpenDynaset)
  rstmeet.MoveFirst
  If rstmeet.RecordCount Mod 2 = 0 Then 'even
    rstmeet.Move rstmeet.RecordCount / 2 - 1
    holder = rstmeet.col_2
    rstmeet.MoveNext
    holder = holder + rstmeet.col_2
    holder = holder / 2
  Else
    rstmeet.Move rstmeet.RecordCount / 2 - 0.5
    holder = rstmeet.col_2
  End If
  MsgBox holder
  rstmeet.MoveFirst
  Range("A1").CopyFromRecordset rstmeet
  rstmeet.Close
  dbsfile.Close
  Set rstmeet = Nothing
  Set dbsfile = Nothing
End Sub

Hope that gets you going.

Tony

 

[Duritz]

Sorry it's taken me 2 years to reply but thanks Tony, that's perfect. Easily able to transfer it to what I wanted.