Solved

count exclusives

Posted on 2011-10-25
256 Views
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???
0
Question by:dnewman33

LVL 75

Expert Comment

Here is one way in a query:

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

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;
0

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]
or
Count(TableA.AcctNum) AS CountOfAcctNum?
0

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;
Query2:
SELECT Count(Q1.AcctNum) AS [Count-AcctNum] FROM Q1;
0

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.
0

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.
DBexclusive.mdb
0

Author Closing Comment

0

Featured Post

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…