471,066 Members | 1,258 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Finding Median - Gouped by field

I have been looking at the code for MedianFind(pDte As String) from the
following thread from UtterAccess.com: "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
2 2385
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
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.

Similar topics

4 posts views Thread by Ross Contino | last post: by
2 posts views Thread by michael way | last post: by
8 posts views Thread by nick.vitone | last post: by
3 posts views Thread by Scott | last post: by
7 posts views Thread by Bhadan | last post: by
3 posts views Thread by mehwishobaid | last post: by
6 posts views Thread by rrstudio2 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.