Data Management

How do I... Group an Access report by the results of an expression?


Basing a report's group on natural data is simple, and most of the time it's adequate. For instance, you might group customers by their names or ZIP codes. Or you could group orders by their order dates or shipped dates. Initially, groups might seem similar to sorting, but the main difference is that the report isolates each group. That special treatment allows you to display subtotals and other analytical values for each group, as opposed to the entire report.

Note: This information is also available as a PDF download.

Simple grouping

The report in Figure A groups customer orders by weeks. An expression in the group footer returns an order count for each week (group). This simple report groups records from the Orders table in Northwind, the sample database that comes with Access. Everything you need to group this report is built-in:

  1. With the report in Design view, right-click on the Detail section title bar and choose Sorting And Grouping from the submenu.
  2. In the resulting dialog box, choose OrderDate from the Field/Expression column's drop-down list. Choose Ascending from the Sort Order column's drop-down list.
  3. In the Group Properties section, set Group Footer to Yes.
  4. In the Group Properties section, choose Week from the Group On setting, as shown in Figure B. Close the Sorting And Grouping dialog box.
  5. In the new group's footer, insert a text box. Use the following expression to count the number of orders (records) in each group: =Count(OrderDate).
  6. View the report in Print Preview.

Figure A: It's easy to group a report by a date component.

Figure B: Choose group settings in the Sorting And Grouping dialog box.


Note

You might have noticed that the report doesn't sort the records within each group by the OrderDate values. That's because the report's Sorting And Grouping feature usurps other sort settings. Even if the underlying query sorts the records, the report's settings take precedent. You're not stuck though. The first row in the Sorting And Grouping dialog box determines the group. To sort records within that group, add a second row that specifies the soft field and order.


Group by an expression

The built-in options work well, as far as they go. But sometimes, they're not adequate. For instance, suppose you need a report that groups orders by the number of weeks between the order date and the shipped date, as shown in Figure C. There's no built-in feature to group in this manner. Fortunately, grouping by the result of an expression will meet this need. In fact, you can group by most any expression, as long as it references the report's data. The results might not make any sense though. For instance, you wouldn't want to group a report by the Count() function in the previous example. In fact, if you try, Access will return an error.

Figure C: An expression groups records by the number of weeks that passed between the order's order and shipped dates.

In the previous example, you used data to create groups. To group by an expression, simply enter the expression in the Sorting And Grouping dialog box instead of choosing a field, as follows:

1. With the report in Design view, right-click on the Detail section title bar and choose Sorting And Grouping from the submenu.

2. In the resulting dialog box, enter the following expression in the Field/Expression column, as shown in Figure D: =DateDiff("w",OrderDate,ShippedDate).

3. Choose Ascending from the Sort Order column's drop-down list.

4. In the Group Properties section, set Group Header to Yes and close the Sorting And Grouping dialog box.

5. In the new group's header, insert a text box. Insert the same expression as the control's Control Source property.

6. Review the report in Print Preview.

Figure D: Enter an expression in the Sorting And Grouping dialog box.

Adding the grouping expression to the header isn't essential to the group. The expression simply displays the group value -- the number of weeks between the order date and the shipped date -- in the header. It helps the reader understand the report, but doesn't change the way the report groups the data.

Group for impact

Grouped reports are easy to create, but sometimes you need more than the built-in settings. Grouping by an expression that references the report's data does more than group the data in a unique manner. It provides additional information that's not part of the natural data.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
bmm1363
bmm1363

I am trying to group my data by grouping every Comment that has "no" in it in one list and every comment with "yes" in it into another list. In my query, I have "Like no* OR Like yes*" in the criteria under my comment field. I do not know how to get this to translate over to my report. Right now, I have a report with all the no comments, then another list of the "no / another statement" and so on with the no's and yes's. How can I group so my report has two lists: a list with all "no" comments, titling that list Comment: no, and a list with all "yes" comments, titling that list Comment: yes?

bhushanladdad
bhushanladdad

I want to sort my data in group which is ascending order as.0,1,2,3,4.. but I want to display it as 1,2,3,4,0.... Can anyone provide me the suitable expression for this requirement?How can I do it.

Tony Hopkinson
Tony Hopkinson

Like No* or Like Yes* will give you one group, with all those with comments which start with No or Yes. That's a where not a group No idea how you do it from the front end of access. But what you need is two columns one for YesOrNo, and another for comment, then you group by YesOrNo and print out the comments.

Editor's Picks