If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
median
How are you calling the function?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#22
|
|||
|
|||
median
as the function called in the code : Dmedian
-- תודה רבה "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#23
|
|||
|
|||
median
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... as the function called in the code : Dmedian -- ???? ??? "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#24
|
|||
|
|||
median
DMedian("mahkam","sheet1","m130040") Function DMedian( _ Expr As String, _ Domain As String, _ Optional Criteria As String = "" _ ) As Variant ' This code was originally written by ' Doug Steele, MVP ' http://I.Am/DougSteele ' 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 -- תודה רבה "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... as the function called in the code : Dmedian -- ???? ??? "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#25
|
|||
|
|||
median
If you're always calling the function as
DMedian("mahkam","sheet1","m130040"), then it's no wonder you're always getting the same value back. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... DMedian("mahkam","sheet1","m130040") Function DMedian( _ Expr As String, _ Domain As String, _ Optional Criteria As String = "" _ ) As Variant ' This code was originally written by ' Doug Steele, MVP ' http://I.Am/DougSteele ' 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 -- ???? ??? "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... as the function called in the code : Dmedian -- ???? ??? "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#26
|
|||
|
|||
median
thank you Douglas J. Steele
yes, i calling the function as DMedian("mahkam","sheet1","m130040) from query (group by) because i want to get resulte like that: unit median 130040 850 130050 135 130060 400 and so on. . . -- תודה רבה "Douglas J. Steele" wrote: If you're always calling the function as DMedian("mahkam","sheet1","m130040"), then it's no wonder you're always getting the same value back. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... DMedian("mahkam","sheet1","m130040") Function DMedian( _ Expr As String, _ Domain As String, _ Optional Criteria As String = "" _ ) As Variant ' This code was originally written by ' Doug Steele, MVP ' http://I.Am/DougSteele ' 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 -- ???? ??? "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... as the function called in the code : Dmedian -- ???? ??? "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#27
|
|||
|
|||
median
Your solution work perfect
is it posible to implement it on a query insted of table ? in that case, what about the id (autonumber) ? -- תודה רבה "James A. Fortune" wrote: יריב החביב wrote: 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....2f7b4979f359ef I cobbled together a non-VBA SQL method for computing a median. In: [Median of GROUP BY values] http://groups.google.com/group/comp....b6eb508f22fa9d I extended it to be able to calculate medians of grouped data. James A. Fortune |
#28
|
|||
|
|||
median
Assuming that the "m130040" is due to the unit number, try something like:
SELECT Unit, DMedian("mahkam","sheet1","m" & [Unit]) AS Median FROM Sheet1 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... thank you Douglas J. Steele yes, i calling the function as DMedian("mahkam","sheet1","m130040) from query (group by) because i want to get resulte like that: unit median 130040 850 130050 135 130060 400 and so on. . . -- ???? ??? "Douglas J. Steele" wrote: If you're always calling the function as DMedian("mahkam","sheet1","m130040"), then it's no wonder you're always getting the same value back. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... DMedian("mahkam","sheet1","m130040") Function DMedian( _ Expr As String, _ Domain As String, _ Optional Criteria As String = "" _ ) As Variant ' This code was originally written by ' Doug Steele, MVP ' http://I.Am/DougSteele ' 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 -- ???? ??? "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... as the function called in the code : Dmedian -- ???? ??? "Douglas J. Steele" wrote: How are you calling the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "???? ?????" wrote in message ... OK NOW I USE A TABLE AND YOUR CODE. i want to get the spesific median for every unit but it give me the same resulte (median) for all of the units -- ???? ??? "Douglas J. Steele" wrote: If you're trying to use a query that includes parameters (as opposed to a table), you'll need to include the code from the previous model that resolves the parameters. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... THANK YOU I implement your code i get run time error 3061 - too few parameter. expected 3 -- ???? ??? "Douglas J. Steele" wrote: Rather than me trying to debug that code, see whether the code from my October, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access" works for you. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "???? ?????" wrote in message ... thank you i combine your solution with the code and it is dont work can you see what i am missing ? |
#29
|
|||
|
|||
median
יריב החביב wrote:
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 DVIR - Jewish nickname for the Holy of Holies -- from an explanation about the meaning of the name of the Dvir Hotel in Haifa. |
#30
|
|||
|
|||
median
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 ? -- תודה רבה "James A. Fortune" wrote: יריב החביב wrote: 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 DVIR - Jewish nickname for the Holy of Holies -- from an explanation about the meaning of the name of the Dvir Hotel in Haifa. |
Thread Tools | |
Display Modes | |
|
|