Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Reporting the Median Value of a Group of Records

ACC2000: Reporting the Median Value of a Group of Records

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


This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).


↑ Back to the top

Summary

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.

↑ Back to the top

More Information

The following sample report is based on the Orders table in the sample database Northwind.mdb. The report groups records by the CustomerID field and sorts records within each group by the Freight field.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  2. Create a new report based on the Orders table.
  3. On the View menu, click Sorting and Grouping. Select CustomerID as the first field. Set GroupHeader and GroupFooter to Yes.
  4. Select Freight as the second field. Select Ascending for the Sort Order.
  5. If it is not displayed, click Field List on the View menu. Drag the
    CustomerID field from the field list to the CustomerID header on the report.
  6. Drag the Freight field to the detail section.
  7. Add the following unbound text boxes to the CustomerID header:

       Name         Control Source
       -------------------------------
       IDCount      =Count(*)
       Half         =Int([IDCount]/2)
       Odd          =([IDCount] Mod 2)
  8. Add the following unbound text boxes 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.
  9. Add the following unbound text box to the CustomerID Footer:

       Name         Control Source
       -----------------------------------------------
       Median    =IIF([Odd]=0,([Arg1]+[Arg2])/2,[Arg1])
  10. Save and run the report.

↑ Back to the top

Keywords: kbhowto, kbusage, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 209244
Revision : 1
Created on : 1/7/2017
Published on : 6/28/2004
Exists online : False
Views : 303