Re: How do I retrieve the median value of a field?

From: MaryW (MaryW_at_discussions.microsoft.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 11:45:01 -0800

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)" wrote:

> 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.
>
> MaryW wrote:
> >
> > 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)" wrote:
> >
> > > 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.
> > > > >
> > >
>

Relevant Pages

  • Re: Calculat Median for each data group?
    ... when I run the query I get the following error: ... And just to be clear - I'm looking for a different median rate to be ... Function MedianF(pTable As String, ... Dim sglHold As Single ...
    (microsoft.public.access.queries)
  • Median from a Query
    ... sorts by original post date and not by activity date so this is a repost. ... module code posted in the Knowlege Base regarding median calculation. ... Perhaps the reason is that I am trying to get a median for values in a query? ... Anyway this results in having to store < in a separate field ...
    (microsoft.public.access.queries)
  • Re: How do I retrieve the median value of a field?
    ... assuming that the function actually works (which I won't vouch for since ... did you pass it two strings? ... and secondly the name of the field you want to generate the median on. ... median of the field for ALL the records in the table or a query. ...
    (microsoft.public.access.queries)
  • Re: calculating a median within a query
    ... only be found in a query field I previously made, ... I can't find a median function in the ... SELECT TOP 1 splunge FROM ... FROM blat ORDER BY splunge ...
    (comp.databases.ms-access)
  • Re: calculating a median within a query
    ... using an individual queries to sort out each store's revenue by year ... and month, and then use another query to classify gainers v. decliners, ... I can't find a median function in the ... I use SQL for finding medians. ...
    (comp.databases.ms-access)