By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,684 Members | 1,395 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,684 IT Pros & Developers. It's quick & easy.

Finding Median - Gouped by field

P: n/a
I have been looking at the code for MedianFind(pDte As String) from the
following thread from "Finding Median average grouped
by field"

I have been able to get it to run using Northwind no problem. I am
having some trouble though converting it to my specific purpose which
may be less complicated than the solution Bob (raskew) provided in the

Here are my specifics:

1) I only have one table (FactorTable) with two fields (neighborhood,
2) I am looking for the Median of the 'ratio' field for EACH

For Example:
Hood Ratio
501 1
501 2
501 3
601 2
601 4
601 6

I need a resulting table to spit out:

Hood Median
501 2
601 4

....or at least:

Hood Median
501 2
501 2
501 2
601 4
601 4
601 4

I have seen this questions posted in several forums (and groups) but
have yet to see an answer.
Can this be done????

Thanks alot in advance!


Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
Bob, the following function will do what you are requesting. Before
running it you will need to create a query called MedianQuery, whose
SQL is:
SELECT FactorTable.neighbourhood, FactorTable.ratio
FROM FactorTable
WHERE ((Not (FactorTable.neighbourhood) Is Null) AND (Not
(FactorTable.ratio) Is Null))
ORDER BY FactorTable.neighbourhood, FactorTable.ratio;

Public Function CreateMedianTable()

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field,
fld2 As DAO.Field
On Error GoTo Err_median
Set dbs = CurrentDb
dbs.TableDefs.Delete "Median Table"

Set tdf = dbs.CreateTableDef("Median Table")
Set fld = tdf.CreateField("Category", dbText, 40)
Set fld2 = tdf.CreateField("Median value", dbSingle, 40)
tdf.Fields.Append fld
tdf.Fields.Append fld2
dbs.TableDefs.Append tdf

Dim rst As DAO.Recordset
Dim rstM As DAO.Recordset
Dim myarray(2000) As Variant
Dim title1 As String
Dim fieldname As String
Dim medianresult As Variant
Dim A As Integer
Dim B As Integer
title1 = "MedianQuery"
Dim appXL As New Excel.Application

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(title1)
Set rstM = dbs.OpenRecordset("Median Table")
Do While Not rst.EOF
fieldname = rst.Fields(0).Value
B = 1
Do While rst.Fields(0) = fieldname
myarray(B) = rst.Fields(1)
If rst.EOF Then Exit Do
B = B + 1
medianresult = appXL.Median(myarray)
rstM.Fields("Category") = fieldname
rstM.Fields("Median value") = medianresult
Erase myarray 'sets each element to empty

DoCmd.OpenTable ("Median Table")

Exit Function

If Err.Number = 3265 Then
Resume Next
MsgBox Error$
Resume Exit_median
End If

End Function

Nov 13 '05 #2

P: n/a
I forgot to add that you will need Microsoft Excel on your computer as
well as a reference to Microsoft Excel in your access database.


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.