Access Totals Query Tip – Partition Function

Untitled Document


Partition function - Access Totals Queries

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.

Access partition function by age


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

Access partition query

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.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>