Solved

Calculate Median in Totals Query

Posted on 2006-06-19
15
1,167 Views
Last Modified: 2009-01-02
Hi,

This sounds like a fairly hard question which I hope someone can help me on.  I have a "Totals" query that groups by certain fields and calculates the Avg.  The following is the SQL :

SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportation"))
GROUP BY REGION, INDUSTRY;

Now instead of Avg(Mean) I want the median of the means.  How can i do this?  Do I need to write a public function and reference it in the query?  That I have never done before.  Any help is appreciated.  Thanks

D
0
Comment
Question by:lebeau26
  • 3
  • 3
  • 3
  • +3
15 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16935059
U want a avg of all?

u could try not grouping with the fields
did that work?

SELECT Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportation"))
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16935066
or perhaps

SELECT Avg(AvgOfMEAN)
FROM (SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
           FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
           WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportation"))
           GROUP BY REGION, INDUSTRY) AS qryAvg
0
 

Author Comment

by:lebeau26
ID: 16935160
Sorry, I basically want the Median of the means instead of the Avg of the means.  I do want to group by the region and industry though.

So basically it should look like:

Region       Industry        Median
America      Autos          100
England      Autos           50
America      Food           90
England       Food          10

Does this make more sense?
D
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16935241
Hi lebeau26,

You will need to use a UDF to get a median, unfortunately...

Regards,

Patrick
0
 

Author Comment

by:lebeau26
ID: 16935323
Ok, so how would I do this?  Again I have never used a UDF in a query before.  I usually just reference them in VBA.

Also, any ideas on how to code the median UDF ?

Thanks
D
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16935754
Not sure if you'll be able to get a UDF for an aggregate function of course.
But there are many examples of calculating medians out there..
Something like
http://support.microsoft.com/?kbid=210581

Also if on a report - then just this might be useful.
http://support.microsoft.com/?kbid=209244

Lots of different takes on it - like
http://www.aspfaq.com/show.asp?id=2506

And one method from right here that might work for you within a query scenario
http://www.experts-exchange.com/Databases/MS_Access/Q_21631892.html
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:lebeau26
ID: 16935966
"Not sure if you'll be able to get a UDF for an aggregate function of course."

Really?  Thats not good because thats what I need.  I need it to group by region and then industry and then calculate the median in that.

Is anyone absolutely sure that median can't be done in an aggregate function?

D
0
 
LVL 58

Accepted Solution

by:
harfang earned 64 total points
ID: 16936066
Hello lebeau26

Here is one way to do it. It uses the same calling conventions as the other domain aggregate functions:

Function DMedian(Expr As String, Domain As String, _
    Optional Criteria = Null, _
    Optional Delim As String = ", ")
'
' Similar to DAvg(), but returning the median
'
    DMedian = Null
    With New ADODB.Recordset
        .Open "Select " & Expr & " From " & Domain _
            & " Where " + Criteria & " Order By " & Expr _
        , CurrentProject.Connection, adOpenStatic, adLockReadOnly
        If Not .EOF Then
            .MoveLast
            If .RecordCount Mod 2 Then
                .AbsolutePosition = .RecordCount \ 2 + 1
                DMedian = .Fields(0)
            Else
                .AbsolutePosition = .RecordCount \ 2
                DMedian = .Fields(0) / 2
                .MoveNext
                DMedian = DMedian + .Fields(0) / 2
            End If
        End If
    End With
   
End Function

(°v°)
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 62 total points
ID: 16936112
Well in Group By you could happily let a UDF do the work - just create and pass in the relevant parameters to calculate across only certain groupings.
An ammendment of Markus or any previous code should be able to do that easily.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16936124
This time I should have refreshed. Many things can happen during two phone calls...

I like the SQL solutions in one of Leigh's links, but I'm not quite sure they work in Access as is. Top 1 is not guaranteed to return a single record, which is a problem for subqueries...

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16936297
One more thing:

There are two types of aggregate functions in Access. SQL aggregate functions like Avg(), First(), etc., which cannot be user-defined, and regular VB "domain aggregate" functions like DAvg(), DLookup(), of which you can create new ones.

Note that other major variants of SQL allow the creation of new aggregate functions, but not JetSQL, as far as I know.

If your case, use DMedian( "AvgOfMEAN", "<your query name>" )

(°v°)
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 62 total points
ID: 16937828
I found this, if this is any help 4 u

http://experts.about.com/q/Using-MS-Access-1440/Calculate-Median.htm

it is vba code Im afraid

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 62 total points
ID: 17038651
lebeau26,

If you like, you can try to use Excel's "Median" function in Access:

http://www.fabalou.com/Access/Queries/excel_functions.asp
http://support.microsoft.com/?kbid=198571

Hope this helps as well
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now