R
Ranzi
table: event, event date, result (integer1 to 20)
i need to find the median result by event
i need to find the median result by event
J
Jack Leach
dim ret as integer, rs as recordset, i as integer, icount as integer
set rs = currentdb.openrecordset("select * from table order by result")
rs.movelast
i = round(rs.recordcount / 2): icount = 1
while icount < i
rs.movenext
wend
ret = rs.fields("result")
rs.close
set rs = nothing
set rs = currentdb.openrecordset("select * from table order by result")
rs.movelast
i = round(rs.recordcount / 2): icount = 1
while icount < i
rs.movenext
wend
ret = rs.fields("result")
rs.close
set rs = nothing
J
Jack Leach
dim ret as integer, rs as recordset, i as integer, icount as integer
set rs = currentdb.openrecordset("select * from table order by result")
rs.movelast
i = round(rs.recordcount / 2): icount = 1
while icount < i
rs.movenext
wend
ret = rs.fields("result")
rs.close
set rs = nothing
set rs = currentdb.openrecordset("select * from table order by result")
rs.movelast
i = round(rs.recordcount / 2): icount = 1
while icount < i
rs.movenext
wend
ret = rs.fields("result")
rs.close
set rs = nothing
T
Tom van Stiphout
On Mon, 11 May 2009 15:32:01 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:
You're on the right track, but this code is terribly inefficient:
there is no need for repeatedly calling MoveNext if you can use the
Move method.
-Tom.
Microsoft Access MVP
dot com> wrote:
You're on the right track, but this code is terribly inefficient:
there is no need for repeatedly calling MoveNext if you can use the
Move method.
-Tom.
Microsoft Access MVP
T
Tom van Stiphout
On Mon, 11 May 2009 15:32:01 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:
You're on the right track, but this code is terribly inefficient:
there is no need for repeatedly calling MoveNext if you can use the
Move method.
-Tom.
Microsoft Access MVP
dot com> wrote:
You're on the right track, but this code is terribly inefficient:
there is no need for repeatedly calling MoveNext if you can use the
Move method.
-Tom.
Microsoft Access MVP
J
Jack Leach
Well I didn't put a whole lot of effort into it 
Also, this doesn't calculate a real median on an even number of records,
which would actually be the average between the two middle records.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
Also, this doesn't calculate a real median on an even number of records,
which would actually be the average between the two middle records.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
J
Jack Leach
Well I didn't put a whole lot of effort into it
Also, this doesn't calculate a real median on an even number of records,
which would actually be the average between the two middle records.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
Also, this doesn't calculate a real median on an even number of records,
which would actually be the average between the two middle records.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
T
Tom van Stiphout
On Tue, 12 May 2009 05:00:06 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:
Granted.
But it's good to point that out, so the OP doesn't blindly adopt your
procedure and then gets into trouble.
Other issues that need to be addressed:
* Current code cannot handle an empty table (MoveLast will fail).
* Current code has possible integer overflow for large tables.
-Tom.
Microsoft Access MVP
dot com> wrote:
Granted.
But it's good to point that out, so the OP doesn't blindly adopt your
procedure and then gets into trouble.
Other issues that need to be addressed:
* Current code cannot handle an empty table (MoveLast will fail).
* Current code has possible integer overflow for large tables.
-Tom.
Microsoft Access MVP
T
Tom van Stiphout
On Tue, 12 May 2009 05:00:06 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:
Granted.
But it's good to point that out, so the OP doesn't blindly adopt your
procedure and then gets into trouble.
Other issues that need to be addressed:
* Current code cannot handle an empty table (MoveLast will fail).
* Current code has possible integer overflow for large tables.
-Tom.
Microsoft Access MVP
dot com> wrote:
Granted.
But it's good to point that out, so the OP doesn't blindly adopt your
procedure and then gets into trouble.
Other issues that need to be addressed:
* Current code cannot handle an empty table (MoveLast will fail).
* Current code has possible integer overflow for large tables.
-Tom.
Microsoft Access MVP
J
Jack Leach
My first attempt was a bare minimum, in accordance with the op's post.
Here's a more concise version.
(untested... the rs.Move command may need to be offset by one to get the
actual middle value...)
Public Function GetMedianValue(sSource As String, _
sField As String, _
Optional sWhere As String _
) As Double
'Returns Null on NonNumeric sField or No Records
'sSource = Source Table or Query
'sField = Field to get median from
'sWhere = Optional Where clause (excluding WHERE keyword)
On Error Goto Err_Proc
Dim Ret As Double, bRSOpen As Boolean
'--------------------
Dim rs As DAO.Recordset
Dim bEvenNumOfRecs As Boolean
Dim dblTemp As Double 'Temp for caluclating average on 2 values
'--------------------
'Make the where clause
sWhere = Iif(IsMissing(sWhere), "", " WHERE " & sWhere & " ")
'Open the rs
Set rs = CurrentDb.OpenRecordset( _
"SELECT " & sField & " FROM " & sSource & sWhere _
& " ORDER BY " & sField)
bRSOpen = True
'Check for records
If rs.RecordCount <> 0 Then Goto Exit_Proc
rs.MoveFirst
'Make sure they're numeric
If Not IsNumeric(rs.Fields(0)) Then Goto Exit_Proc
'Get the recordcount and even/odd num of recs
rs.MoveLast: rs.MoveFirst
bEvenNumberOfRecs = pfIsEvenNumber(rs.Recordcount)
'Get the median
rs.Move Round(rs.Recordcount / 2)
dblTemp = rs.Fields(0)
If bEvenNumberOfRecs = True Then
'Calculate the average
rs.MovePrevious
Ret = (dblTemp + rs.Fields(0)) / 2
Else
Ret = dblTemp
End If
'--------------------
Exit_Proc:
If bRSOopen Then
rs.Close
Set rs = Nothing
End If
GetMedianValue = Ret
Exit Function
Err_Proc:
Msgbox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Proc
Resume
End Function
Private Function pfIsEvenNumber(Num As Double) As Boolean
pfIsEvenNumber = Iif(Round(Num / 2) <> Num, False, True)
End Function
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
Here's a more concise version.
(untested... the rs.Move command may need to be offset by one to get the
actual middle value...)
Public Function GetMedianValue(sSource As String, _
sField As String, _
Optional sWhere As String _
) As Double
'Returns Null on NonNumeric sField or No Records
'sSource = Source Table or Query
'sField = Field to get median from
'sWhere = Optional Where clause (excluding WHERE keyword)
On Error Goto Err_Proc
Dim Ret As Double, bRSOpen As Boolean
'--------------------
Dim rs As DAO.Recordset
Dim bEvenNumOfRecs As Boolean
Dim dblTemp As Double 'Temp for caluclating average on 2 values
'--------------------
'Make the where clause
sWhere = Iif(IsMissing(sWhere), "", " WHERE " & sWhere & " ")
'Open the rs
Set rs = CurrentDb.OpenRecordset( _
"SELECT " & sField & " FROM " & sSource & sWhere _
& " ORDER BY " & sField)
bRSOpen = True
'Check for records
If rs.RecordCount <> 0 Then Goto Exit_Proc
rs.MoveFirst
'Make sure they're numeric
If Not IsNumeric(rs.Fields(0)) Then Goto Exit_Proc
'Get the recordcount and even/odd num of recs
rs.MoveLast: rs.MoveFirst
bEvenNumberOfRecs = pfIsEvenNumber(rs.Recordcount)
'Get the median
rs.Move Round(rs.Recordcount / 2)
dblTemp = rs.Fields(0)
If bEvenNumberOfRecs = True Then
'Calculate the average
rs.MovePrevious
Ret = (dblTemp + rs.Fields(0)) / 2
Else
Ret = dblTemp
End If
'--------------------
Exit_Proc:
If bRSOopen Then
rs.Close
Set rs = Nothing
End If
GetMedianValue = Ret
Exit Function
Err_Proc:
Msgbox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Proc
Resume
End Function
Private Function pfIsEvenNumber(Num As Double) As Boolean
pfIsEvenNumber = Iif(Round(Num / 2) <> Num, False, True)
End Function
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
J
Jack Leach
My first attempt was a bare minimum, in accordance with the op's post.
Here's a more concise version.
(untested... the rs.Move command may need to be offset by one to get the
actual middle value...)
Public Function GetMedianValue(sSource As String, _
sField As String, _
Optional sWhere As String _
) As Double
'Returns Null on NonNumeric sField or No Records
'sSource = Source Table or Query
'sField = Field to get median from
'sWhere = Optional Where clause (excluding WHERE keyword)
On Error Goto Err_Proc
Dim Ret As Double, bRSOpen As Boolean
'--------------------
Dim rs As DAO.Recordset
Dim bEvenNumOfRecs As Boolean
Dim dblTemp As Double 'Temp for caluclating average on 2 values
'--------------------
'Make the where clause
sWhere = Iif(IsMissing(sWhere), "", " WHERE " & sWhere & " ")
'Open the rs
Set rs = CurrentDb.OpenRecordset( _
"SELECT " & sField & " FROM " & sSource & sWhere _
& " ORDER BY " & sField)
bRSOpen = True
'Check for records
If rs.RecordCount <> 0 Then Goto Exit_Proc
rs.MoveFirst
'Make sure they're numeric
If Not IsNumeric(rs.Fields(0)) Then Goto Exit_Proc
'Get the recordcount and even/odd num of recs
rs.MoveLast: rs.MoveFirst
bEvenNumberOfRecs = pfIsEvenNumber(rs.Recordcount)
'Get the median
rs.Move Round(rs.Recordcount / 2)
dblTemp = rs.Fields(0)
If bEvenNumberOfRecs = True Then
'Calculate the average
rs.MovePrevious
Ret = (dblTemp + rs.Fields(0)) / 2
Else
Ret = dblTemp
End If
'--------------------
Exit_Proc:
If bRSOopen Then
rs.Close
Set rs = Nothing
End If
GetMedianValue = Ret
Exit Function
Err_Proc:
Msgbox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Proc
Resume
End Function
Private Function pfIsEvenNumber(Num As Double) As Boolean
pfIsEvenNumber = Iif(Round(Num / 2) <> Num, False, True)
End Function
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
Here's a more concise version.
(untested... the rs.Move command may need to be offset by one to get the
actual middle value...)
Public Function GetMedianValue(sSource As String, _
sField As String, _
Optional sWhere As String _
) As Double
'Returns Null on NonNumeric sField or No Records
'sSource = Source Table or Query
'sField = Field to get median from
'sWhere = Optional Where clause (excluding WHERE keyword)
On Error Goto Err_Proc
Dim Ret As Double, bRSOpen As Boolean
'--------------------
Dim rs As DAO.Recordset
Dim bEvenNumOfRecs As Boolean
Dim dblTemp As Double 'Temp for caluclating average on 2 values
'--------------------
'Make the where clause
sWhere = Iif(IsMissing(sWhere), "", " WHERE " & sWhere & " ")
'Open the rs
Set rs = CurrentDb.OpenRecordset( _
"SELECT " & sField & " FROM " & sSource & sWhere _
& " ORDER BY " & sField)
bRSOpen = True
'Check for records
If rs.RecordCount <> 0 Then Goto Exit_Proc
rs.MoveFirst
'Make sure they're numeric
If Not IsNumeric(rs.Fields(0)) Then Goto Exit_Proc
'Get the recordcount and even/odd num of recs
rs.MoveLast: rs.MoveFirst
bEvenNumberOfRecs = pfIsEvenNumber(rs.Recordcount)
'Get the median
rs.Move Round(rs.Recordcount / 2)
dblTemp = rs.Fields(0)
If bEvenNumberOfRecs = True Then
'Calculate the average
rs.MovePrevious
Ret = (dblTemp + rs.Fields(0)) / 2
Else
Ret = dblTemp
End If
'--------------------
Exit_Proc:
If bRSOopen Then
rs.Close
Set rs = Nothing
End If
GetMedianValue = Ret
Exit Function
Err_Proc:
Msgbox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Proc
Resume
End Function
Private Function pfIsEvenNumber(Num As Double) As Boolean
pfIsEvenNumber = Iif(Round(Num / 2) <> Num, False, True)
End Function
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
J
Jack Leach
Furthermore, it should be noted that pfIsEvenNumber is only accurate when
passing a value that has only an integer portion (to the left of the
decimal). Double datatypes with numbers other than 0 on the right side of
the decimal may return a false value.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
passing a value that has only an integer portion (to the left of the
decimal). Double datatypes with numbers other than 0 on the right side of
the decimal may return a false value.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
J
Jack Leach
Furthermore, it should be noted that pfIsEvenNumber is only accurate when
passing a value that has only an integer portion (to the left of the
decimal). Double datatypes with numbers other than 0 on the right side of
the decimal may return a false value.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
passing a value that has only an integer portion (to the left of the
decimal). Double datatypes with numbers other than 0 on the right side of
the decimal may return a false value.
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)