How to count / group distinct records in Access

Posted on 2011-10-28
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:Newbi22
    LVL 14

    Expert Comment

    by:Bill Ross

    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 92

    Accepted Solution

    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

    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
    GROUP BY z.Rep

    Open in new window

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

    Author Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now