How to fix grouping on a report

Debbie Burnette 0 Reputation points
2026-06-07T00:51:33.4333333+00:00

On a report I made that I run monthly, it is now splitting transactions and I'm not sure why.

I have the report grouped on Category and sorted by a field. The category is set to group Z-A, so that "Sale of Goods" comes before "Booth Rent".

This month, we had a lot more sales transactions. The report is showing Sale of Goods first and in the correct sort, but then it subtotals, does the 2nd group and subtotals, and then below that, it continues with 5 more Sale of Goods records and subtotals again.

How do I get all the Sale of Goods transactions together before the Booth Rent grouping?

Microsoft 365 and Office | Access | For business | Windows

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duane Hookom 26,935 Reputation points Volunteer Moderator
    2026-06-07T17:27:05.3133333+00:00

    I would set the record source to the same sorting as the report and then review the records is datasheet view. Keep in mind the query sorting is overridden by the report’s sorting and grouping.

    Was this answer helpful?

    0 comments
  2. Dora-T 15,980 Reputation points Microsoft External Staff Moderator
    2026-06-07T08:56:41.47+00:00

    Hi Debbie Burnette

    Based on your description, I understand that records with the same Category are appearing in multiple sections of the report instead of being grouped together under a single Category heading.

    The "Do not keep group together on one page" setting only affects how groups are displayed across page breaks and would not normally cause the same Category to appear as multiple groups within the report.

    To better understand your issue, could you please provide the following information:

    • A screenshot of the Group, Sort, and Total pane for the report.
    • A screenshot showing the portion of the report where "Sale of Goods" appears in more than one group.
    • Whether the report is based on a table or a query. If it is based on a query, please share the SQL statement if possible.

    Please send them via private message to avoid exposing any personal or organizational information.

    In the meantime, please verify whether all affected records use exactly the same Category value, as even minor differences in the underlying data can sometimes result in separate groups being displayed.

    Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution.     

    I look forward to your update.     

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

  3. AI answer

    2026-06-07T00:51:46.3433333+00:00

    The issue is caused by the current combination of grouping and sorting levels. “Sale of Goods” is grouped more than once because there is at least one additional grouping or sort level that splits the data after the first subtotal.

    To get all “Sale of Goods” records together before “Booth Rent,” adjust the grouping and sorting so that:

    1. There is only one grouping level on Category.
    2. All other sort levels are below that Category group and do not introduce additional grouping.

    Use the Group, Sort, and Total pane to verify and fix this:

    1. Open the report in Layout View or Design View.
    2. On the ribbon, select Report Design and then Group & Sort to open the Group, Sort, and Total pane.
    3. In the pane:
      • Confirm there is a single Group on line for the Category field.
      • If there is any second Group on or a sort that effectively groups by another field before Category, move Category to the top using the up arrow so it is the first grouping level.
      • Remove any extra grouping levels that are not needed (click the delete button on that row).
    4. Under the Category group, add a Sort by line for the field that should control the order of transactions within each Category.
    5. On the Category group row, click More and set Sort order to Z–A so that “Sale of Goods” appears before “Booth Rent.”
    6. Ensure no other grouping level is defined on the same field or on an expression that splits Category (for example, an expression that groups some “Sale of Goods” rows differently).

    If you need to group or sort on a calculated value instead of a field, use an expression in the Group, Sort, and Total pane so that all “Sale of Goods” rows share the same group expression value.

    After these changes, all “Sale of Goods” transactions will appear together in one group and subtotal, followed by “Booth Rent.”

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.