G
Guest
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
value of numeric data in several fields.
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.
S
StCyrM
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
G
Gary Walter
navman9 said:I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
Hi navman,
One SQL method is to find the count
and rank of each field, then if count is
even, return field value where rank is
count\2 +1; if count is odd, return the 2 field
values on each side of count\2 and find
the average of them.
table M:
pk f1 f2 f3
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5
6 6 5
7 7 6
qryMRank:
SELECT
M.pk,
M.f1,
M.f2,
M.f3,
(SELECT COUNT([f1]) FROM M) AS f1Cnt,
(SELECT COUNT([f2]) FROM M) AS f2Cnt,
(SELECT COUNT([f3]) FROM M) AS f3Cnt,
(SELECT COUNT([f1])+1 FROM M As f1M
WHERE f1M.f1<M.f1 OR (f1M.f1=M.f1 AND f1M.pk<M.pk)) AS f1Rank,
(SELECT COUNT([f2])+1 FROM M As f2M
WHERE f2M.f1<M.f2 OR (f2M.f2=M.f2 AND f2M.pk<M.pk)) AS f2Rank,
(SELECT COUNT([f3])+1 FROM M As f3M
WHERE f3M.f3<M.f3 OR (f3M.f3=M.f3 AND f3M.pk<M.pk)) AS f3Rank
FROM M;
pk f1 f2 f3 f1Cnt f2Cnt f3Cnt f1Rank f2Rank f3Rank
1 1 1 1 7 7 4 1 1 1
2 2 2 2 7 7 4 2 2 2
3 3 3 3 7 7 4 3 3 3
4 4 4 4 7 7 4 4 4 4
5 5 5 7 7 4 5 5 1
6 6 5 7 7 4 6 6 1
7 7 6 7 7 4 7 6 1
qryMedian:
SELECT "f1" AS FldNam, Avg(qryMRank.f1) AS Median
FROM qryMRank
WHERE
Switch([f1Cnt] Mod 2<>0,(qryMRank.f1Rank) =[f1Cnt]\2+1,
True,(qryMRank.f1Rank) In ([f1Cnt]\2,[f1Cnt]\2+1))
GROUP BY "f1"
UNION ALL
SELECT "f2" AS FldNam, Avg(qryMRank.f2)
FROM qryMRank
WHERE Switch([f2Cnt] Mod 2<>0,(qryMRank.f2Rank) =[f2Cnt]\2+1,
True,(qryMRank.f2Rank) In ([f2Cnt]\2,[f2Cnt]\2+1))
GROUP BY "f2"
UNION ALL SELECT "f3" AS FldNam, Avg(qryMRank.f3)
FROM qryMRank
WHERE Switch([f3Cnt] Mod 2<>0,(qryMRank.f3Rank) =[f3Cnt]\2+1,
True,(qryMRank.f3Rank) In ([f3Cnt]\2,[f3Cnt]\2+1))
GROUP BY "f3";
FldNam Median
f1 4
f2 4
f3 2.5
I'm sorry to rush the explanation but I need
to get going into work. If this is a method you
might wish to pursue, I will check back after
work if you have any questions.
Good luck,
Gary Walter
G
Guest
I tried this and I'm getting an error at NOT NULL ORDER BY [" & fldName$ &
"];")
that says "Compile error: List seperator or ) expected". I've tried several
changes and can't seem to get it to work. Please help! Thanks.
"];")
that says "Compile error: List seperator or ) expected". I've tried several
changes and can't seem to get it to work. Please help! Thanks.
StCyrM said:Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
G
Guest
Hi-
I am new to VBA/modules and I must be missing a step in creating the median
function. Please spell out a few things for me.
I created a module with the code listed below. Then I went to use the median
function in a query. I got the message "Undefined function 'Median' in
expression". What am I missing?
Any help is appreciated!
I am new to VBA/modules and I must be missing a step in creating the median
function. Please spell out a few things for me.
I created a module with the code listed below. Then I went to use the median
function in a query. I got the message "Undefined function 'Median' in
expression". What am I missing?
Any help is appreciated!
StCyrM said:Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
D
Douglas J. Steele
You didn't by any chance name the module containing that function Median.
did you? You can't reuse names in Access. If that's what you did, simply
rename the module, and you should be fine.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
did you? You can't reuse names in Access. If that's what you did, simply
rename the module, and you should be fine.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
MaryW said:Hi-
I am new to VBA/modules and I must be missing a step in creating the median
function. Please spell out a few things for me.
I created a module with the code listed below. Then I went to use the median
function in a query. I got the message "Undefined function 'Median' in
expression". What am I missing?
Any help is appreciated!
StCyrM said:Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
J
John Spencer (MVP)
I think you have a small problem in this function as posted. You have a line
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
G
Guest
I hope I'm not being dense here, but I'm still having trouble using the
median in a query. Now I get the message "Wrong number of arguments used with
function in query expression"
Please help...
median in a query. Now I get the message "Wrong number of arguments used with
function in query expression"
Please help...
John Spencer (MVP) said:I think you have a small problem in this function as posted. You have a line
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
J
John Spencer (MVP)
Well, assuming that the function actually works (which I won't vouch for since
it is not my code), did you pass it two strings? One the name of the table,
and secondly the name of the field you want to generate the median on.
You should have a column something like:
Field: GetMedian: Median("MyTableName","MyFieldName")
Now the problem I see with this entire function is that it basically gets the
median of the field for ALL the records in the table or a query. In other
words, it does not get the median of a group or the median of a query that is
returns a limited set of records using a where clause.
You could still use it by creating a query that returns the desired record and
then use that query as the basis of what you need to generate the median values.
it is not my code), did you pass it two strings? One the name of the table,
and secondly the name of the field you want to generate the median on.
You should have a column something like:
Field: GetMedian: Median("MyTableName","MyFieldName")
Now the problem I see with this entire function is that it basically gets the
median of the field for ALL the records in the table or a query. In other
words, it does not get the median of a group or the median of a query that is
returns a limited set of records using a where clause.
You could still use it by creating a query that returns the desired record and
then use that query as the basis of what you need to generate the median values.
I hope I'm not being dense here, but I'm still having trouble using the
median in a query. Now I get the message "Wrong number of arguments used with
function in query expression"
Please help...
John Spencer (MVP) said:I think you have a small problem in this function as posted. You have a line
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
G
Guest
Hi-
Thanks for helping through my first attempt at VBA. I corrected the query
and defined the two arguements as the table and the field names. The NEW
error message that I get is "Compile Error: User-define type is not defined".
In the module, the second line of code is highlighted
Dim MedianDB As Database
Any more suggestions?
Thanks for helping through my first attempt at VBA. I corrected the query
and defined the two arguements as the table and the field names. The NEW
error message that I get is "Compile Error: User-define type is not defined".
In the module, the second line of code is highlighted
Dim MedianDB As Database
Any more suggestions?
John Spencer (MVP) said:Well, assuming that the function actually works (which I won't vouch for since
it is not my code), did you pass it two strings? One the name of the table,
and secondly the name of the field you want to generate the median on.
You should have a column something like:
Field: GetMedian: Median("MyTableName","MyFieldName")
Now the problem I see with this entire function is that it basically gets the
median of the field for ALL the records in the table or a query. In other
words, it does not get the median of a group or the median of a query that is
returns a limited set of records using a where clause.
You could still use it by creating a query that returns the desired record and
then use that query as the basis of what you need to generate the median values.
I hope I'm not being dense here, but I'm still having trouble using the
median in a query. Now I get the message "Wrong number of arguments used with
function in query expression"
Please help...
John Spencer (MVP) said:I think you have a small problem in this function as posted. You have a line
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
StCyrM wrote:
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
D
Douglas J. Steele
You must be using Access 2000 or 2002. Database is a DAO object, and, by
default, Access 2000 and 2002 only have references to ADO.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library
If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)
The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
default, Access 2000 and 2002 only have references to ADO.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library
If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)
The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
MaryW said:Hi-
Thanks for helping through my first attempt at VBA. I corrected the query
and defined the two arguements as the table and the field names. The NEW
error message that I get is "Compile Error: User-define type is not defined".
In the module, the second line of code is highlighted
Dim MedianDB As Database
Any more suggestions?
John Spencer (MVP) said:Well, assuming that the function actually works (which I won't vouch for since
it is not my code), did you pass it two strings? One the name of the table,
and secondly the name of the field you want to generate the median on.
You should have a column something like:
Field: GetMedian: Median("MyTableName","MyFieldName")
Now the problem I see with this entire function is that it basically gets the
median of the field for ALL the records in the table or a query. In other
words, it does not get the median of a group or the median of a query that is
returns a limited set of records using a where clause.
You could still use it by creating a query that returns the desired record and
then use that query as the basis of what you need to generate the median values.
I hope I'm not being dense here, but I'm still having trouble using the
median in a query. Now I get the message "Wrong number of arguments used with
function in query expression"
Please help...
:
I think you have a small problem in this function as posted. You have a line
continuation inside the quotes.
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
I would rewrite it for clarity as
Set ssMedian = MedianDB.Openrecordset ( _
"SELECT [" & fldName$ & "] " & _
"FROM [" & tName$ & "] " & _
"WHERE [" & fldName$ & "] IS NOT NULL " & _
"ORDER BY [" & fldName$ & "]")
StCyrM wrote:
Good afternoon
The following function will calculate the Median for you. Simply copy and
paste this into a new module.
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
I have a large database in which I'd like to find the median, not average,
value of numeric data in several fields.
J
John Spencer (MVP)
Change code to read
DIM MedianDB as DAO.Database
Dim ssMedian As DAO.Recordset
You will also need a reference to the DAO library.
Select Tools: References from the menu
Scroll down and find rhw Microsoft DAO 3.6 Object Library (or higher version
depending on your version of Access).
If your database is not a .mdb database but is an .adp database this will still fail.
DIM MedianDB as DAO.Database
Dim ssMedian As DAO.Recordset
You will also need a reference to the DAO library.
Select Tools: References from the menu
Scroll down and find rhw Microsoft DAO 3.6 Object Library (or higher version
depending on your version of Access).
If your database is not a .mdb database but is an .adp database this will still fail.
G
Guest
Thanks so much! It worked!
John Spencer (MVP) said:Change code to read
DIM MedianDB as DAO.Database
Dim ssMedian As DAO.Recordset
You will also need a reference to the DAO library.
Select Tools: References from the menu
Scroll down and find rhw Microsoft DAO 3.6 Object Library (or higher version
depending on your version of Access).
If your database is not a .mdb database but is an .adp database this will still fail.
G
Guest
Thank you for having this discussion. I have been able to follow up until
this point. I get a "Run-time error '3061: Too few parameters. Expected 1."
message.
Perhaps the reason is that I am trying to get a median for values in a query?
Background> MSAccess 2000
data is water quality testing with some values reported as less than the
testing method is able to detect. Not 0 but say <2. It could be 1.9999 for
all we know. Anyway this results in having to store < in a separate field
and calculate fields treating the < value as 0. I did not want to store this
false data in the table so I created a calculated field in a query. I need
to run the query anyway for the user input month and year. I am doing
monthly reporting for daily results but need to store all of the historical
data in a single table.
Does this median module only work if the data is directly from a table?
I thought perhaps it did not like a query calculated value field so I did
store the "false" data in a field in the database. It did not change
anything.
The query name and field are passed to the module from a textbox in my report.
Control source =Median("Sample_Data_Single_Month","E1_colival")
Your patience and assistance is appreciated.
this point. I get a "Run-time error '3061: Too few parameters. Expected 1."
message.
Perhaps the reason is that I am trying to get a median for values in a query?
Background> MSAccess 2000
data is water quality testing with some values reported as less than the
testing method is able to detect. Not 0 but say <2. It could be 1.9999 for
all we know. Anyway this results in having to store < in a separate field
and calculate fields treating the < value as 0. I did not want to store this
false data in the table so I created a calculated field in a query. I need
to run the query anyway for the user input month and year. I am doing
monthly reporting for daily results but need to store all of the historical
data in a single table.
Does this median module only work if the data is directly from a table?
I thought perhaps it did not like a query calculated value field so I did
store the "false" data in a field in the database. It did not change
anything.
The query name and field are passed to the module from a textbox in my report.
Control source =Median("Sample_Data_Single_Month","E1_colival")
Your patience and assistance is appreciated.