The Partition() Function

[ADezii]

The inspiration for this Tip was an initial reply by one of our resident Experts to a specific Post. In this Post, the User wanted to know how he could calculate the total number of test results (Satisfactory/Unsatisfactory) for specific age group intervals such as: 20-24, 25-29, etc. The use of the Partition Function was suggested, I followed up with a reply of my own, and it seemed as though some interest was generated in this little know Function. I decided to make this into a Tip, because even though it is a little known and rarely used Function, it is actually very handy when you want to calculate how many times something occurs within a calculated series of ranges or if you want to know if a number occurs within a calculated series of ranges. The syntax for the Partition Function is:

1. Partition(number, start, stop, interval)
   1. number - whole number to be evaluated against the ranges.
   2. start - whole number that is the start of the overall range of number.
   3. stop - whole number that is the end of the overall range of numbers.
   4. interval - difference between range elements.
2. The best method of explaining this Function is to show some test data, then apply the Function against the data with specific Arguments. The sample data, SQL Statement, and subsequent output are listed below:
   

   [Age]      [Testrslt] (Fields in tblTest)
    
    73        Satisfactory
    19        Satisfactory
    19        Unsatisfactory
    20        Satisfactory
    24        Unsatisfactory
    21        Satisfactory
    16        Satisfactory
    45        Unsatisfactory
    50        Satisfactory
    55        Unsatisfactory
    75        Satisfactory
    80        Unsatisfactory
    62        Satisfactory
    64        Unsatisfactory
    46        Unsatisfactory
    47        Unsatisfactory
    49        Unsatisfactory
    48        Unsatisfactory
    56        Unsatisfactory
    58        Satisfactory
    72        Satisfactory
    67        Unsatisfactory
    15        Satisfactory
    17        Satisfactory
    63        Unsatisfactory
    66        Unsatisfactory
    61        Unsatisfactory
    33        Satisfactory
    50        Satisfactory
    51        Satisfactory
    54        Satisfactory
    32        Unsatisfactory

   SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
   FROM tblTest
   GROUP BY [Testrslt], Partition([Age],0,100,5);

   [Testrslt]       [Age_Range]  [Range_Count]
   Satisfactory     15: 19             4
   Satisfactory     20: 24             2
   Satisfactory     30: 34             1
   Satisfactory     50: 54             4
   Satisfactory     55: 59             1
   Satisfactory     60: 64             1
   Satisfactory     70: 74             2
   Satisfactory     75: 79             1
   Unsatisfactory     15: 19           1
   Unsatisfactory     20: 24           1
   Unsatisfactory     30: 34           1
   Unsatisfactory     45: 49           5
   Unsatisfactory     55: 59           2
   Unsatisfactory     60: 64           3
   Unsatisfactory     65: 69           2
   Unsatisfactory     80: 84           1

[nico5038]

Hi ADezii, thanks for teaching me a new function :)

Personally I prefer however to use the "table approach" as this will allow me to have the ranges specified by the user, thus placing the responsibility in his (her) hands. You know how often users can change their mind :-)

Nic;o).

[ADezii]

Point well taken, Nico.

[MMcCarthy]

Although I agree with Nico I appreciate learning a new function. Never know when it might come in useful. :D

[DataAnalyzer]

Here's a frequency distribution approach that uses a table with the group definitions: Frequency Distributions

[ADezii]

Thanks for Posting this informational Link.

[jimatqsi]

Does this work in Access? Isn't DistinctRow SQL-only?

[ADezii]

The Partition() Function will work in Access.

[MMcCarthy]

Not sure if DISTINCTROW does anything Jim but it doesn't throw an error.