[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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???
0
dnewman33
Asked:
dnewman33
  • 3
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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
 
als315Commented:
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
 
dnewman33Author Commented:
Made simple to understand.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now