ACC: Reporting the Median Value of a Group of Records

Last reviewed: April 2, 1997
Article ID: Q103271
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article demonstrates how to create a report that shows the median value for a set of records. Note that a median value is the midpoint in an ordered set of values (the value above and below which there is an equal set of values), or the arithmetic mean of the two middle numbers if there is no one middle number.

MORE INFORMATION

The following sample report is based on the Orders table in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0). The report will group records by the CustomerID field, and will sort records within each group by the Freight field.

NOTE: In Microsoft Access 1.x and 2.0, there is a space in Customer ID.

  1. Create a new report based on the Orders table in Northwind.mdb.

  2. On the View menu, click Sorting And Grouping. Select CustomerID as the first field. Set GroupHeader and GroupFooter to Yes.

  3. Select Freight as the second field. Select Ascending for the Sort Order.

  4. If it is not displayed, choose Field List from the View menu. Drag the CustomerID field from the field list to the CustomerID header on the report.

  5. Drag the Freight field to the detail section.

  6. Add the following unbound controls to the CustomerID header:

          Name         Control Source
          -------------------------------
          IDCount      =Count(*)
          Half         =Int([IDCount]/2)
          Odd          =([IDCount] Mod 2)
    
          NOTE: The Name property is called ControlName in version 1.x.
    
    

  7. Add the following unbound controls to the detail section:

    NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.

          Name         Control Source
          -------------------------------------------------------------
          Position     =1
          Arg1         =Val(IIf([Position]=([Half]+[Odd]),[Freight],0))
          Arg2:        =Val(IIf([Odd]=0,IIf([Position]=([Half]+_
                          [Odd]+1),[Freight],0),0))
    
       Set the RunningSum property to Over Group for each of the text box
       controls in this section except for the Freight text box control.
    
    

  8. Add the following unbound control to the CustomerID Footer:

        Name         Control Source
        --------------------------------------------------
        Median    =IIF([Odd]=0,([Arg1]+[Arg2])/2,[Arg1])
    
    

  9. Save and run the report.


Keywords : kbusage RptOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.