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(numbe r, 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:
   [CODE=text]
   [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[/CODE]
   [CODE=sql]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);[/CODE]
   [CODE=text]
   [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[/CODE]

[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.