VBA & Macro Problems

How to Count Unique Patient Values in Microsoft Access VBA

Khadija Khan Sep 30, 2026 869 views

Question details

The user needs to count the number of unique patients in a Microsoft Access table, ensuring that duplicate encounters for the same patient are not counted multiple times.

How to Count Unique Patient Values in Microsoft Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Querying an Access database table with VBA or SQL to retrieve a distinct count of individuals from a dataset containing multiple encounter records.
Observed behavior: The user wants to retrieve a precise count of distinct individuals (patients) rather than the total number of rows (encounters) using an Access query or VBA function.

Before you start

Ensure you have the exact name of your Access table and the specific column (e.g., 'Patient' or 'PatientID') you wish to count uniquely.

Solution 1Recommended

Using an SQL Subquery to Count Distinct Values

This method uses a nested SQL query to first select distinct patients, then counts those results. It is the most direct and efficient method in Access.

Unlike other database systems like SQL Server, MS Access SQL does not natively support the 'COUNT(DISTINCT column)' syntax. To achieve this, you must construct a subquery that isolates the distinct records first.

Open SQL View

In Microsoft Access, go to the Create tab on the Ribbon, click Query Design, close the Show Table dialog box, and switch to SQL View.

Write the Distinct Subquery

Construct the inner query to retrieve unique values. Type: SELECT DISTINCT Patient FROM Encounters;

Wrap with the COUNT Function

Enclose your distinct query within an outer counting query: SELECT COUNT(*) AS PatientCount FROM (SELECT DISTINCT Patient FROM Encounters);

Run the Query

Click the Run button (red exclamation mark) on the Query Design tab to see your unique patient count.

Using an SQL Subquery to Count Distinct Values

Syntax Check: Replace 'Patient' and 'Encounters' with your actual column and table names. If your table names contain spaces, enclose them in square brackets, e.g., [Patient Records].

Solution 2

Using the DCount Function with a Saved Query in VBA

Save a distinct query in your database, then reference it using the DCount function within your VBA code.

Free Microsoft Office alternative

Switch to WPS Office for Powerful Data Management

While Microsoft Access handles complex relational databases, for everyday data tracking, list management, and distinct counting tasks, WPS Spreadsheet offers an incredibly lightweight and free alternative. It seamlessly supports Excel formats and includes built-in functions to remove duplicates and count unique values instantly without needing VBA code.

1. Open WPS Spreadsheet: Launch WPS Office and open your data list or create a new spreadsheet.
2. Select Your Data: Highlight the column containing the patient names or IDs you want to count.
3. Remove Duplicates: Go to the Data tab on the ribbon and click 'Remove Duplicates' to isolate unique records.
4. Count the Result: Use the standard =COUNTA() function or simply look at the status bar at the bottom to see your distinct patient count.

Free to use with a lightweight and fast installation process.Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Built-in 'Remove Duplicates' feature to easily filter distinct values.Familiar user interface for quick adaptation without a learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use COUNT(DISTINCT column) natively in Microsoft Access?

No, Microsoft Access SQL does not support the COUNT(DISTINCT) aggregate function like SQL Server or MySQL does. You must use a subquery to select the distinct values first, and then apply COUNT(*) to the results of that subquery.

How do I count unique combinations of multiple columns in Access?

You can include multiple columns in your SELECT DISTINCT subquery, for example: SELECT DISTINCT FirstName, LastName FROM Encounters. The outer COUNT query will then count the unique combinations of those specific columns.

Why does my DCount function return an error in VBA?

Ensure that the query name you are referencing in the DCount function is spelled correctly and enclosed in quotation marks. Additionally, verify that the saved query successfully runs on its own in Access without syntax errors before calling it in VBA.

Is it faster to use SQL or VBA DCount for counting records?

Using a pure SQL subquery is generally faster and more efficient for processing large datasets in Access. The DCount domain aggregate function is convenient for quick lookups but can be significantly slower when executed on very large tables.

Excel & Data Problems