VBA - median of a recordset

Duritz

Board Regular
Joined
Apr 28, 2005
Messages
226
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:

Code:
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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.

Code:
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

Board Regular
Joined
Apr 28, 2005
Messages
226
Sorry it's taken me 2 years to reply but thanks Tony, that's perfect. Easily able to transfer it to what I wanted.
 

Forum statistics

Threads
1,136,420
Messages
5,675,742
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top