Access 2003 - Multiple matching records need to count as 1 occurre

  • Thread starter Thread starter ReportLady
  • Start date Start date
R

ReportLady

I'm just learning Access (teaching myself) and am in need of assistance. I
have a table that I need to query that will review all the records in the
Acct Nbr column and count the occurrences of duplicate Acct Nbr's as a count
of 1 rather than 2. I have 16,901 records of whichabout 1/3 are duplicate
records (account numbers). Can someone assist me with how I would filter this
data?

Thank you kindly.
 
SELECT Acct Nbr, Count([Acct Nbr]) AS CountOfAcct Nbr
FROM YourTable
WHERE Count([Acct Nbr]) >1
GROUP BY Acct Nbr;
 
I'm just learning Access (teaching myself) and am in need of assistance. I
have a table that I need to query that will review all the records in the
Acct Nbr column and count the occurrences of duplicate Acct Nbr's as a count
of 1 rather than 2. I have 16,901 records of whichabout 1/3 are duplicate
records (account numbers). Can someone assist me with how I would filter this
data?

Thank you kindly.
Click to expand...

Try a query based on another query. First create a query on your table
selecting only the Acct Nbr field; set the query's properties to "Unique
Value" to bring up only one instance of each Acct Nbr.

Then do a totals query counting the records in this query (or just navigate to
the end of the recordset and look at the recordcount...)
 
Back
Top