473,888 Members | 2,213 Online

# The Partition() Function

8,834 Recognized Expert Expert
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:
Expand|Select|Wrap|Line Numbers
1. [Age]      [Testrslt] (Fields in tblTest)
2.
3.  73        Satisfactory
4.  19        Satisfactory
5.  19        Unsatisfactory
6.  20        Satisfactory
7.  24        Unsatisfactory
8.  21        Satisfactory
9.  16        Satisfactory
10.  45        Unsatisfactory
11.  50        Satisfactory
12.  55        Unsatisfactory
13.  75        Satisfactory
14.  80        Unsatisfactory
15.  62        Satisfactory
16.  64        Unsatisfactory
17.  46        Unsatisfactory
18.  47        Unsatisfactory
19.  49        Unsatisfactory
20.  48        Unsatisfactory
21.  56        Unsatisfactory
22.  58        Satisfactory
23.  72        Satisfactory
24.  67        Unsatisfactory
25.  15        Satisfactory
26.  17        Satisfactory
27.  63        Unsatisfactory
28.  66        Unsatisfactory
29.  61        Unsatisfactory
30.  33        Satisfactory
31.  50        Satisfactory
32.  51        Satisfactory
33.  54        Satisfactory
34.  32        Unsatisfactory
Expand|Select|Wrap|Line Numbers
1. SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
2. FROM tblTest
3. GROUP BY [Testrslt], Partition([Age],0,100,5);
Expand|Select|Wrap|Line Numbers
1. [Testrslt]       [Age_Range]  [Range_Count]
2. Satisfactory     15: 19             4
3. Satisfactory     20: 24             2
4. Satisfactory     30: 34             1
5. Satisfactory     50: 54             4
6. Satisfactory     55: 59             1
7. Satisfactory     60: 64             1
8. Satisfactory     70: 74             2
9. Satisfactory     75: 79             1
10. Unsatisfactory     15: 19           1
11. Unsatisfactory     20: 24           1
12. Unsatisfactory     30: 34           1
13. Unsatisfactory     45: 49           5
14. Unsatisfactory     55: 59           2
15. Unsatisfactory     60: 64           3
16. Unsatisfactory     65: 69           2
17. Unsatisfactory     80: 84           1
Sep 16 '07 #1
8 17348
nico5038
3,080 Recognized Expert Specialist
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).
Jan 19 '10 #2
8,834 Recognized Expert Expert
Point well taken, Nico.
Jan 19 '10 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
Although I agree with Nico I appreciate learning a new function. Never know when it might come in useful. :D
Jan 19 '10 #4
DataAnalyzer
15 New Member
Here's a frequency distribution approach that uses a table with the group definitions: Frequency Distributions
May 9 '10 #5
8,834 Recognized Expert Expert
Thanks for Posting this informational Link.
May 9 '10 #6
jimatqsi
1,278 Recognized Expert Top Contributor
Does this work in Access? Isn't DistinctRow SQL-only?
Jun 12 '10 #7
8,834 Recognized Expert Expert
The Partition() Function will work in Access.
Jun 12 '10 #8
MMcCarthy
14,534 Recognized Expert Moderator MVP
Not sure if DISTINCTROW does anything Jim but it doesn't throw an error.
Jun 12 '10 #9