count exclusives

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???
dnewman33Asked:
Who is Participating?
 
als315Connect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is one way in a query:

SELECT TableA.AcctNum, Count(TableA.AcctNum) AS CountOfAcctNum
FROM TableA
GROUP BY TableA.AcctNum;
0
 
als315Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dnewman33Author Commented:
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
 
als315Commented:
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
 
dnewman33Author Commented:
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
 
dnewman33Author Commented:
Made simple to understand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.