Learn how to a build a cloud-first strategyRegister Now


How to count / group distinct records in Access

Posted on 2011-10-28
Medium Priority
Last Modified: 2012-05-12
I have a MS Access 2003 table that hold information regarding customers insurance polices. I need to, for lesser terms, count / group them together. Attached is an example of the data set and the result I am trying to achieve.

Question by:Michael Franz
LVL 14

Expert Comment

by:Bill Ross
ID: 37044771

Generally, here's how...

Start a new query.  Select the customers and policies.

Click on the totals tool (group by).

In the Totals Line set customers to Group By and Policies to Count

Post your databse and I can help in more detail.


LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 37044784
This is easier to do in SQL Server; in Access it is difficult because Count(Distinct ColumnName) is not supported.

I address this in my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html

Using just SQL statements:

SELECT z.Rep, Count(z.Customers)
    (SELECT s.Rep, s.Customers
    FROM SomeTable s
    GROUP BY s.Rep, s.Customers) AS z

Open in new window

If you prefer to do it in VBA, my article shows you how to do that.

Author Comment

by:Michael Franz
ID: 37076321

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question