Group By Clause in Microsoft Access Query

In this tutorial, we will show you how to use a Group By clause in Microsoft Access. Groups in access can assist you in organizing the objects in your database. Group by Clause combines records with identical values in the specified field list into a single record. A rundown value is fashioned for each record if you take in an SQL aggregate function, such as Sum or Count, in the selected statement.

The Access group by clause comes after the where clause of a query and before the optional having clause in the query.


Step 1 – Creating query

Consider this sample database of products. What if you want to group the results according to a specific condition? For that, we will apply Group By clause to some of the fields of this table.
To create the ms access query, open the Create Tab and then click on the Query Design option in the “other” Section.

Now let’s group the products according to the categories. For that, we will select the products table from the show table window.

making a query

Step 2 – Selecting fields

From the products table, let’s select the suppliers ID filed, Product Name field and the Category field. After that, we will click on the total icon on the menu bar above.

Selecting category

Step 3 – Running the query

Now let’s run the query in ms access and a table would be displayed with the Products table grouped according to the suppliers and displaying the product name and category along with them.

This means that we have successfully created a group by clause, displaying the data according to a specific condition, which in this case is according to supplier ID arranged in the ascending order.

And in this way you can group data in ms access.

Working with query