Others

How to Count Distinct Activities in an MS Access Report

Huda Qurayshi Sep 30, 2026 869 views

Question details

The user needs a way to count distinct activities in a database report rather than counting every individual detail record.

How to Count Distinct Activities in an Access Report

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating reports where activities contain multiple related records and are grouped by status, requiring an accurate count of unique activities.
Observed behavior: The report currently counts every single detail record, leading to inflated numbers instead of reflecting the actual count of distinct activities.

Before you start

Ensure your report is already grouped by the appropriate status field and that you have a unique identifier (ID) assigned to each activity. Back up your database before making changes to query relationships or report control formulas.

Solution 1Recommended

Use an Unbound Text Box with the Running Sum Property

Prevent duplicate detail records from inflating your totals by configuring an unbound text box to evaluate unique records and run a sum over the group.

Adding an unbound text box by itself will not solve the issue; it must be combined with a conditional expression that flags only the first instance of an activity. Once flagged, the Running Sum property handles the calculation.

Open Report in Design View

Right-click your report in the Navigation Pane on the left side of the screen and select Design View from the context menu.

Insert an Unbound Text Box

Navigate to the Report Design Tools tab, click the Text Box control icon, and click inside the Detail section of your report to place it.

Configure the Control Source Expression

Select the new text box, open the Property Sheet (press F4), and navigate to the Data tab. Set the Control Source to an IIf expression that outputs 1 for the first occurrence of the activity ID and 0 for duplicates.

Set Running Sum to Over Group

In the same Data tab of the Property Sheet, locate the Running Sum property. Click the dropdown arrow and change it from No to Over Group.

Display the Final Count in the Footer

If you only want to see the total count, set the Visible property of your detail text box to No. Then, add another text box in the Group Footer and set its Control Source to reference the name of your hidden Running Sum text box.

Use an Unbound Text Box with the Running Sum Property

Verify Query Relationships: If you encounter errors regarding relationship branches when setting up the underlying query, double-check that your table joins are correctly configured and enforced in the Database Tools tab.

Free Microsoft Office alternative

Need a Lightweight Alternative for Data Tracking?

While Microsoft Access is built for complex relational databases, many data tracking and reporting tasks can be easily managed using WPS Spreadsheet. WPS Office offers a free, lightweight suite that provides a familiar interface and advanced data tools for seamless productivity.

Easily manage, filter, and count distinct data records using PivotTables and built-in functions.Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation with a user-friendly, tabbed interface for seamless migration.Built-in PDF editing and data visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my report count detail records instead of unique activities?

By default, standard aggregate functions like Count() evaluate every individual row returned by the report's underlying query. If a single activity has multiple detail records tied to it, each row is counted unless you apply specific grouping logic or distinct counting formulas.

Where do I find the Running Sum property?

The Running Sum property is located in the Property Sheet of a text box control. Open your report in Design View, select the text box, press F4 to open the Property Sheet, and look under the Data tab.

Can I count distinct records directly in the underlying query?

Yes, you can use a Totals query utilizing the Group By operator or write a subquery that specifically returns only distinct records before feeding that data into the report. This approach often simplifies the report design process.

What happens if I just add an unbound text box without an expression?

An unbound text box alone will not filter out duplicates. If you apply the Running Sum property without an expression that outputs 1 for unique items and 0 for duplicates, the report will simply continue to sum every detail record, producing the same incorrect result.

Office Quick Solutions