Imagine this. Your database contains customer data, and you want to group your customers by number of employees or company revenues.
Perhaps you have a table of employee data and you want to group employees by age group or salary.
How would you accomplish this?
Allow me to introduce you to the little-known, but very useful, partition function. The partition function, which shows how often values occur within a range, is used within a totals query.
About the Example
I will use a very simple example in this post. Once you know about the partition function, however, I am sure you will find other ways to use it.
The example in this blog post assumes you are an intermediate user and therefore know how to create queries and add fields to your query design grid.
We’ll start with a table that has 3,897 records and four fields: ID, Date of Birth, Employee ID and salary
We can use the partition function to see the number of employees in each salary bracket, shown in $25,000 increments.
Step 1- Create a query and add the table to it.
Click the Create tab, then Query Design. Add your table.
Step 2 – Change the query to a totals query.
Click the Design tab, and in the Show/Hide group click Totals.
Step 3 – Create a calculated field (Employee_Num) that shows the number of employees.
Add the Employee ID field to the table and change the totals type to Count. You may also want to rename the new field Employee_Count.
Step 4 – Create another calculated field (Salary_Field) using the partition function.
The partition function has 4 required arguments:
Number: Usually the name of the field containing the values you want to measure (ex., Salary).
Start: A whole number measuring the start of the range (ex., $0)
Stop: Another whole number measuring the end of the range (ex., $300,000)
Interval: The size of the partition (ex., 25,000).
Here’s how the query design grid will look after you’re done. (Click the image to expand it.)
Here are the results of the query.
About the author.
Jackie Kiadii has 20 years of Access database development experience, and teaches Basic through Advanced Access courses, including an in-depth 4-day Access immersion course. Contact her at 770.498.7333.
Need more hands-on Access tips provided by an expert?
Need more MS Access help? Here are your options.
- Access Training in Atlanta, GA and Online
- Subscribe to our email newsletter.
- Need onsite group training? Our courses are affordable and customized to your needs. Available for groups of 4 or more students.
