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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 11/01/04 

Date: Sun, 31 Oct 2004 20:00:54 -0500

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: 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)
  • Re: execute append query
    ... copied sql from query into strings in vba in form, ... Dim qdfAppend as querydef ... > yes i did declare the parameter in the query and yes there was a suitable ...
    (microsoft.public.access.modulesdaovba)