count exclusives

Posted on 2011-10-25
Last Modified: 2013-12-12
I have a field of account numbers . some are repeated. what is the easiest way to get a net number and how. query, report? what is the expression???
Question by:dnewman33
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Here is one way in a query:

    SELECT TableA.AcctNum, Count(TableA.AcctNum) AS CountOfAcctNum
    FROM TableA
    GROUP BY TableA.AcctNum;
    LVL 39

    Expert Comment

    But, if you like to count unique account numbers, you should use query like this:

    SELECT Count(Q1.AcctNum) AS [Count-AcctNum]
    FROM (SELECT TableA.AcctNum FROM TableA GROUP BY TableA.AcctNum) As Q1;

    Author Comment

    I left out I am a beginner. First enable Group by. Select Expression. I would type these into the criteria line?
    Count(Q1.AcctNum) AS [Count-AcctNum]
    Count(TableA.AcctNum) AS CountOfAcctNum?
    LVL 39

    Expert Comment

    In the beginning it will be better not to join two queries into one, but have them separate:
    Query1 (Q1):
    SELECT TableA.AcctNum FROM TableA GROUP BY TableA.AcctNum;
    SELECT Count(Q1.AcctNum) AS [Count-AcctNum] FROM Q1;

    Author Comment

    I wasn't joining them, but I have a choice on which to use. Am I correct that the one I choose should be typed into the criteria line.
    LVL 39

    Accepted Solution

    See example. There are two possible methods:
    a) Query 1 and Query2
    b) Q2
    In case b all is done in one query, but it is absolutely same to the case a.

    Author Closing Comment

    Made simple to understand.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    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 …
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now