D
Douglas J. Steele
How are you calling the function?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
×
יריב החביב
as the function called in the code : Dmedian
D
Douglas J. Steele
I meant what parameters are you passing? If you're using the DMedian
function in a query, show me the SQL of that query. If you're using the
DMedian function in VBA, show me typical code where you're calling the
function.
function in a query, show me the SQL of that query. If you're using the
DMedian function in VBA, show me typical code where you're calling the
function.
×
יריב החביב
DMedian("mahkam","sheet1","m>130040")
Function DMedian( _
Expr As String, _
Domain As String, _
Optional Criteria As String = "" _
) As Variant
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: Calculates the Median of a set of values in a
' specified set of records (a domain)
' Records containing Null values aren't included in the
' calculation of the Median.
'
' Expr An expression that identifies the field
' containing the numeric data for which you want
' the median.
' It can be a string expression identifying
' a field in a table or query, or it can be an
' expression that performs a calculation on data
' in that field.
' In Expr, you can include the name of a field in
a table,
' a control on a form, a constant, or a function.
If Expr
' includes a function, it can be either built-in
or user-defined,
' but not another domain aggregate or SQL
aggregate function.
' Domain A string expression identifying the set of
records that
' constitutes the domain.
' It can be a table name or a query name.
' Criteria An optional string expression used to restrict
the range of data
' on which the DMedian function is performed.
' For example, Criteria is often equivalent to
the WHERE clause in
' an SQL expression, without the word WHERE. If
criteria is omitted,
' the DMedian function evaluates Expr against the
entire domain.
' Any field that is included in criteria must
also be a field in Domain
' otherwise the DMedian function returns a Null.
'
' Returns: The Median value for the relevant set of Expr in Domain.
' The Median is the value of the middle item when the data are
arranged
' from lowest to highest. If there is an even number of
values, the
' Median is the mean of the two middle observations.
On Error GoTo Err_DMedian
Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim lngOffset As Long
Dim lngRecCount As Long
Dim strSQL As String
strSQL = "SELECT " & Expr & " AS DataValue FROM " & Domain & " "
' NOTE: To include nulls when calculating the median value,
' replace the following 4 lines with
' If Len(Criteria) > 0 Then
' strSQL = strSQL & "WHERE " & Criteria & " "
' End If
strSQL = strSQL & "WHERE " & Expr & " IS NOT NULL "
If Len(Criteria) > 0 Then
strSQL = strSQL & "AND (" & Criteria & ") "
End If
strSQL = strSQL & "ORDER BY " & Expr
Set dbMedian = CurrentDb()
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.BOF = False And rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffset = ((lngRecCount + 1) / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
DMedian = rsMedian("DataValue")
Else
lngOffset = (lngRecCount / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
dblTemp1 = rsMedian("DataValue")
rsMedian.MovePrevious
dblTemp2 = rsMedian("DataValue")
DMedian = (dblTemp1 + dblTemp2) / 2
End If
Else
DMedian = Null
End If
End_DMedian:
On Error Resume Next
rsMedian.Close
Set rsMedian = Nothing
Set dbMedian = Nothing
Exit Function
Err_DMedian:
DMedian = Null
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian
End Function
Function DMedian( _
Expr As String, _
Domain As String, _
Optional Criteria As String = "" _
) As Variant
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: Calculates the Median of a set of values in a
' specified set of records (a domain)
' Records containing Null values aren't included in the
' calculation of the Median.
'
' Expr An expression that identifies the field
' containing the numeric data for which you want
' the median.
' It can be a string expression identifying
' a field in a table or query, or it can be an
' expression that performs a calculation on data
' in that field.
' In Expr, you can include the name of a field in
a table,
' a control on a form, a constant, or a function.
If Expr
' includes a function, it can be either built-in
or user-defined,
' but not another domain aggregate or SQL
aggregate function.
' Domain A string expression identifying the set of
records that
' constitutes the domain.
' It can be a table name or a query name.
' Criteria An optional string expression used to restrict
the range of data
' on which the DMedian function is performed.
' For example, Criteria is often equivalent to
the WHERE clause in
' an SQL expression, without the word WHERE. If
criteria is omitted,
' the DMedian function evaluates Expr against the
entire domain.
' Any field that is included in criteria must
also be a field in Domain
' otherwise the DMedian function returns a Null.
'
' Returns: The Median value for the relevant set of Expr in Domain.
' The Median is the value of the middle item when the data are
arranged
' from lowest to highest. If there is an even number of
values, the
' Median is the mean of the two middle observations.
On Error GoTo Err_DMedian
Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim lngOffset As Long
Dim lngRecCount As Long
Dim strSQL As String
strSQL = "SELECT " & Expr & " AS DataValue FROM " & Domain & " "
' NOTE: To include nulls when calculating the median value,
' replace the following 4 lines with
' If Len(Criteria) > 0 Then
' strSQL = strSQL & "WHERE " & Criteria & " "
' End If
strSQL = strSQL & "WHERE " & Expr & " IS NOT NULL "
If Len(Criteria) > 0 Then
strSQL = strSQL & "AND (" & Criteria & ") "
End If
strSQL = strSQL & "ORDER BY " & Expr
Set dbMedian = CurrentDb()
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.BOF = False And rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffset = ((lngRecCount + 1) / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
DMedian = rsMedian("DataValue")
Else
lngOffset = (lngRecCount / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
dblTemp1 = rsMedian("DataValue")
rsMedian.MovePrevious
dblTemp2 = rsMedian("DataValue")
DMedian = (dblTemp1 + dblTemp2) / 2
End If
Else
DMedian = Null
End If
End_DMedian:
On Error Resume Next
rsMedian.Close
Set rsMedian = Nothing
Set dbMedian = Nothing
Exit Function
Err_DMedian:
DMedian = Null
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian
End Function
D
Douglas J. Steele
If you're always calling the function as
DMedian("mahkam","sheet1","m>130040"), then it's no wonder you're always
getting the same value back.
DMedian("mahkam","sheet1","m>130040"), then it's no wonder you're always
getting the same value back.
×
יריב החביב
thank you Douglas J. Steele
yes, i calling the function as
DMedian("mahkam","sheet1","m>130040) from query (group by)
because i want to get resulte like that:
unit median
130040 850
130050 135
130060 400
and so on. . .
yes, i calling the function as
DMedian("mahkam","sheet1","m>130040) from query (group by)
because i want to get resulte like that:
unit median
130040 850
130050 135
130060 400
and so on. . .
×
יריב החביב
Your solution work perfect
is it posible to implement it on a query insted of table ?
in that case, what about the id (autonumber) ?
--
תודה רבה
is it posible to implement it on a query insted of table ?
in that case, what about the id (autonumber) ?
--
תודה רבה
James A. Fortune said:יריב החביב said:Hello,
When i use this code on a table it is work,
but when i use it on a query it do'nt.
what should i do for using this code on query ? .....
Function Median (tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDB()
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function
THANKS A LOT
I offer the following as an alternative method:
In:
http://groups.google.com/group/comp.databases.ms-access/msg/1f2f7b4979f359ef
I cobbled together a non-VBA SQL method for computing a median.
In:
[Median of GROUP BY values]
http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d
I extended it to be able to calculate medians of grouped data.
James A. Fortune
(e-mail address removed)
D
Douglas J. Steele
Assuming that the "m>130040" is due to the unit number, try something like:
SELECT Unit, DMedian("mahkam","sheet1","m>" & [Unit]) AS Median
FROM Sheet1
SELECT Unit, DMedian("mahkam","sheet1","m>" & [Unit]) AS Median
FROM Sheet1
J
James A. Fortune
יריב החביב said:Your solution work perfect
I'm glad you liked it.
is it posible to implement it on a query insted of table ?
in that case, what about the id (autonumber) ?
That's a great question.
Suppose I have the following table:
tblValues
ID AutoNumber
theValue Long
GpName Text
ID theValue GpName
1 738.1 A
2 655.2 A
3 43.4 A
4 8301.9 A
5 644.5 A
6 46.7 A
7 734.1 A
8 93296 B
9 601.8 B
10 59.5 B
11 3.4 B
12 0.08 B
13 609.9 B
14 0.04 C
15 2.8 C
16 55.7 C
17 0.2 C
18 53.5 C
19 2.6 C
20 3.4 C
21 8408.8 C
22 612.5 C
23 56.3 C
24 44.6 C
25 798.2 C
26 740.7 C
27 0.2 C
Then I can create a query to order the information by GpName, not caring
in what order the values or ID numbers are within a GpName:
qryValues:
SELECT ID, theValue, GpName
FROM tblValues
ORDER BY GpName;
!qryValues:
ID theValue GpName
2 655.2 A
3 43.4 A
4 8301.9 A
5 644.5 A
6 46.7 A
7 734.1 A
1 738.1 A
13 609.9 B
8 9329.6 B
9 601.8 B
10 59.5 B
11 3.4 B
12 0.08 B
15 2.8 C
27 0.2 C
16 55.7 C
17 0.2 C
18 53.5 C
19 2.6 C
20 3.4 C
21 8408.8 C
22 612.5 C
23 56.3 C
24 44.6 C 25 798.2 C
26 740.7 C
14 0.04 C
Now I can obtain the Ranking and the WantRanking.
qryRankForMedianFromQuery:
SELECT qryValues.GpName, qryValues.theValue, (SELECT Count(A.theValue)
FROM qryValues AS A WHERE A.theValue > qryValues.theValue AND A.GpName
= qryValues.GpName) + 1 AS Ranking, (SELECT Count(*) FROM qryValues AS A
WHERE A.GpName = qryValues.GpName) / 2 + 0.5 AS WantRanking
FROM qryValues;
!qryRankForMedianFromQuery:
GpName theValue Ranking WantRanking
A 655.2 4 4
A 43.4 7 4
A 8301.9 1 4
A 644.5 5 4
A 46.7 6 4
A 734.1 3 4
A 738.1 2 4
B 609.9 2 3.5
B 9329.6 1 3.5
B 601.8 3 3.5
B 59.5 4 3.5
B 3.4 5 3.5
B 0.08 6 3.5
C 2.8 10 7.5
C 0.2 12 7.5
C 55.7 6 7.5
C 0.2 12 7.5
C 53.5 7 7.5
C 2.6 11 7.5
C 3.4 9 7.5
C 8408.8 1 7.5
C 612.5 4 7.5
C 56.3 5 7.5
C 44.6 8 7.5
C 798.2 2 7.5
C 740.7 3 7.5
C 0.04 14 7.5
That shows that using the values alone for the ranking causes a problem
because the ties can cause the ranking value to stray from the desired
ranking. When the ID was used there was not a possibility of a tie. So
eliminate the tie situation by adding the number of duplicate values
after the current one (ID wise) noting that the ID assignments within a
GpName can be arbitrary.
qryRankForMedianFromQuery:
SELECT qryValues.GpName, qryValues.theValue, (SELECT Count(A.theValue)
FROM qryValues AS A WHERE A.theValue > qryValues.theValue AND A.GpName =
qryValues.GpName) + (SELECT Count(A.theValue) FROM qryValues AS A WHERE
A.theValue = qryValues.theValue AND A.ID < qryValues.ID) + 1 AS Ranking,
(SELECT Count(*) FROM qryValues AS A WHERE A.GpName = qryValues.GpName)
/ 2 + 0.5 AS WantRanking
FROM qryValues;
!qryRankForMedianFromQuery:
GpName theValue Ranking WantRanking
A 655.2 4 4
A 43.4 7 4
A 8301.9 1 4
A 644.5 5 4
A 46.7 6 4
A 734.1 3 4
A 738.1 2 4
B 609.9 2 3.5
B 9329.6 1 3.5
B 601.8 3 3.5
B 59.5 4 3.5
B 3.4 5 3.5
B 0.08 6 3.5
C 2.8 10 7.5
C 0.2 13 7.5
C 55.7 6 7.5
C 0.2 12 7.5
C 53.5 7 7.5
C 2.6 11 7.5
C 3.4 10 7.5
C 8408.8 1 7.5
C 612.5 4 7.5
C 56.3 5 7.5
C 44.6 8 7.5
C 798.2 2 7.5
C 740.7 3 7.5
C 0.04 14 7.5
qryGroupMedians:
SELECT Avg(theValue) AS Median, qryRankForMedianFromQuery.GpName FROM
qryRankForMedianFromQuery WHERE (Abs([Ranking] -
[WantRanking]) < 0.6) GROUP BY qryRankForMedianFromQuery.GpName;
!qryGroupMedians:
Median GpName
655.2 A
330.65 B
49.05 C
Note that with effort it may be possible to simplify the second query.
Also note that a group aggregate function such as DCount can be used in
lieu of a subquery.
James A. Fortune
(e-mail address removed)
DVIR - Jewish nickname for the Holy of Holies -- from an explanation
about the meaning of the name of the Dvir Hotel in Haifa.
×
יריב החביב
Thank you Dvir,
you give me appetite to learn sql comprehensivly
The problem is that in my query there isn't id number.
will you recomend to append the query to a table with id number
and from the table to work out the qryRankForMedianFromQuery
and qryGroupMedians ?
--
תודה רבה
you give me appetite to learn sql comprehensivly
The problem is that in my query there isn't id number.
will you recomend to append the query to a table with id number
and from the table to work out the qryRankForMedianFromQuery
and qryGroupMedians ?
--
תודה רבה
James A. Fortune said:יריב החביב said:Your solution work perfect
I'm glad you liked it.
is it posible to implement it on a query insted of table ?
in that case, what about the id (autonumber) ?
That's a great question.
Suppose I have the following table:
tblValues
ID AutoNumber
theValue Long
GpName Text
ID theValue GpName
1 738.1 A
2 655.2 A
3 43.4 A
4 8301.9 A
5 644.5 A
6 46.7 A
7 734.1 A
8 93296 B
9 601.8 B
10 59.5 B
11 3.4 B
12 0.08 B
13 609.9 B
14 0.04 C
15 2.8 C
16 55.7 C
17 0.2 C
18 53.5 C
19 2.6 C
20 3.4 C
21 8408.8 C
22 612.5 C
23 56.3 C
24 44.6 C
25 798.2 C
26 740.7 C
27 0.2 C
Then I can create a query to order the information by GpName, not caring
in what order the values or ID numbers are within a GpName:
qryValues:
SELECT ID, theValue, GpName
FROM tblValues
ORDER BY GpName;
!qryValues:
ID theValue GpName
2 655.2 A
3 43.4 A
4 8301.9 A
5 644.5 A
6 46.7 A
7 734.1 A
1 738.1 A
13 609.9 B
8 9329.6 B
9 601.8 B
10 59.5 B
11 3.4 B
12 0.08 B
15 2.8 C
27 0.2 C
16 55.7 C
17 0.2 C
18 53.5 C
19 2.6 C
20 3.4 C
21 8408.8 C
22 612.5 C
23 56.3 C
24 44.6 C 25 798.2 C
26 740.7 C
14 0.04 C
Now I can obtain the Ranking and the WantRanking.
qryRankForMedianFromQuery:
SELECT qryValues.GpName, qryValues.theValue, (SELECT Count(A.theValue)
FROM qryValues AS A WHERE A.theValue > qryValues.theValue AND A.GpName
= qryValues.GpName) + 1 AS Ranking, (SELECT Count(*) FROM qryValues AS A
WHERE A.GpName = qryValues.GpName) / 2 + 0.5 AS WantRanking
FROM qryValues;
!qryRankForMedianFromQuery:
GpName theValue Ranking WantRanking
A 655.2 4 4
A 43.4 7 4
A 8301.9 1 4
A 644.5 5 4
A 46.7 6 4
A 734.1 3 4
A 738.1 2 4
B 609.9 2 3.5
B 9329.6 1 3.5
B 601.8 3 3.5
B 59.5 4 3.5
B 3.4 5 3.5
B 0.08 6 3.5
C 2.8 10 7.5
C 0.2 12 7.5
C 55.7 6 7.5
C 0.2 12 7.5
C 53.5 7 7.5
C 2.6 11 7.5
C 3.4 9 7.5
C 8408.8 1 7.5
C 612.5 4 7.5
C 56.3 5 7.5
C 44.6 8 7.5
C 798.2 2 7.5
C 740.7 3 7.5
C 0.04 14 7.5
That shows that using the values alone for the ranking causes a problem
because the ties can cause the ranking value to stray from the desired
ranking. When the ID was used there was not a possibility of a tie. So
eliminate the tie situation by adding the number of duplicate values
after the current one (ID wise) noting that the ID assignments within a
GpName can be arbitrary.
qryRankForMedianFromQuery:
SELECT qryValues.GpName, qryValues.theValue, (SELECT Count(A.theValue)
FROM qryValues AS A WHERE A.theValue > qryValues.theValue AND A.GpName =
qryValues.GpName) + (SELECT Count(A.theValue) FROM qryValues AS A WHERE
A.theValue = qryValues.theValue AND A.ID < qryValues.ID) + 1 AS Ranking,
(SELECT Count(*) FROM qryValues AS A WHERE A.GpName = qryValues.GpName)
/ 2 + 0.5 AS WantRanking
FROM qryValues;
!qryRankForMedianFromQuery:
GpName theValue Ranking WantRanking
A 655.2 4 4
A 43.4 7 4
A 8301.9 1 4
A 644.5 5 4
A 46.7 6 4
A 734.1 3 4
A 738.1 2 4
B 609.9 2 3.5
B 9329.6 1 3.5
B 601.8 3 3.5
B 59.5 4 3.5
B 3.4 5 3.5
B 0.08 6 3.5
C 2.8 10 7.5
C 0.2 13 7.5
C 55.7 6 7.5
C 0.2 12 7.5
C 53.5 7 7.5
C 2.6 11 7.5
C 3.4 10 7.5
C 8408.8 1 7.5
C 612.5 4 7.5
C 56.3 5 7.5
C 44.6 8 7.5
C 798.2 2 7.5
C 740.7 3 7.5
C 0.04 14 7.5
qryGroupMedians:
SELECT Avg(theValue) AS Median, qryRankForMedianFromQuery.GpName FROM
qryRankForMedianFromQuery WHERE (Abs([Ranking] -
[WantRanking]) < 0.6) GROUP BY qryRankForMedianFromQuery.GpName;
!qryGroupMedians:
Median GpName
655.2 A
330.65 B
49.05 C
Note that with effort it may be possible to simplify the second query.
Also note that a group aggregate function such as DCount can be used in
lieu of a subquery.
James A. Fortune
(e-mail address removed)
DVIR - Jewish nickname for the Holy of Holies -- from an explanation
about the meaning of the name of the Dvir Hotel in Haifa.
J
James A. Fortune
יריב החביב said:Thank you Dvir,
you give me appetite to learn sql comprehensivly
The problem is that in my query there isn't id number.
will you recomend to append the query to a table with id number
and from the table to work out the qryRankForMedianFromQuery
and qryGroupMedians ?
The ID that the queries use is the one from the table. If your table
doesn't have an AutoNumber field you can add one even if you are using
another field or combination of fields as a primary key.
James A. Fortune
(e-mail address removed)
×
יריב החביב
thank you again
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.