In Microsoft Access, GROUP BY is a clause you can use to combine records with identical values in a specific field in one record. If you include an SQL aggregate function in the SELECT statement, such as AVG, COUNT, or SUM, Access creates a summary value for each record.

Using GROUP BY

You can find and use the GROUP BY function using an SQL query in the SQL View. It’s one of the simplest and most direct ways to access and control your data.

Instructions in this article apply to Access for Microsoft 365, Access 2019, 2016, 2013, and 2010.

  • Start Access and open your database.
  • This example uses the Northwind Sample Database.
  • Select the Create tab.
  • In the Queries group, select Query Design.
  • In the Add Tables list, select the table you want to work with.
  • Select View in the Results group and choose SQL View.
  • The main body will switch to a query terminal window. Here, you can enter any query you like.
  • To get a basic grouping from SQL, you’d enter something like this:
  • SELECT * FROM tablename WHERE column/category LIKE ‘entry’;
  • Substitute the actual name of the table, the category or column heading, and the actual entry value that you’re looking for.

Breaking Down the Query

Consider, for example, an order data table consisting of the attributes below:

Start Access and open your database.

This example uses the Northwind Sample Database.

Select the Create tab.

In the Queries group, select Query Design.

In the Add Tables list, select the table you want to work with.

Select View in the Results group and choose SQL View.

The main body will switch to a query terminal window. Here, you can enter any query you like.

To get a basic grouping from SQL, you’d enter something like this:

SELECT * FROM tablename WHERE column/category LIKE ‘entry’;

Substitute the actual name of the table, the category or column heading, and the actual entry value that you’re looking for.

  • OrderID: A numeric value uniquely identifying each order. This field is the primary key for the database.
  • Salesperson: A text value providing the name of the salesperson who sold the products. This field is a foreign key to another table containing personnel information.
  • CustomerID: A numeric value corresponding to a customer account number. This field is also a foreign key, referencing a table containing customer account information.
  • Revenue: A numeric value corresponding to the dollar amount of the sale.

When it comes time to conduct performance reviews for salespeople, the Orders table contains valuable information that may be used for that review. When evaluating Jim, you could, for example, write a simple query that retrieves all of Jim’s sales records:

SELECT * FROM Orders WHERE Salesperson LIKE ‘Jim’;

This would retrieve all records from the database corresponding to sales made by Jim:

OrderID Salesperson CustomerID Revenue12482 Jim 182 4000012488 Jim 219 2500012519 Jim 137 8500012602 Jim 182 1000012741 Jim 155 90000

You could review this information and perform some manual calculations to develop performance statistics, but this would be a tedious task that you would have to repeat for each salesperson in the company. Instead, you can replace this work with a single GROUP BY query that calculates each salesperson’s statistics in the company. You write the query and specify that the database should group the results based upon the Salesperson field. You may then use any of the SQL aggregate functions to perform calculations on the results.

Here’s an example. If you executed the following SQL statement:

SELECT Salesperson, SUM(Revenue) AS ‘Total’, MIN(Revenue) AS ‘Smallest’, MAX(Revenue) AS ‘Largest’, AVG(Revenue) AS ‘Average’, COUNT(Revenue) AS ‘Number’ FROM Orders GROUP BY Salesperson;

You would get the following results:

Salesperson Total Smallest Largest Average NumberJim 250000 10000 90000 50000 5Mary 342000 24000 102000 57000 6Bob 118000 4000 36000 39333 3

As you can see, this powerful function allows you to generate brief reports from within a SQL query, providing valuable business intelligence to the manager conducting the performance reviews. The GROUP BY clause is often used in databases for this purpose and is a valuable tool in the DBA’s bag of tricks.

Get the Latest Tech News Delivered Every Day