Access-Repeating Accounts

I have a table in Access which contains account numbers.  There are many cases where the account numbers are repeated in the database. I need to obtain a count of each unique account numbers in the dataset.  Is that possible in a Query.
Sample of the table is attached.
Relationship-Test.accdb
ArisaAnsarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are talking across multiple tables or just one table ?

mx
0
lee555J5Commented:
The simplest way is to use the DISTINCT keyword in the SQL query, as in

SELECT DISTINCT field FROM table;

In your case,

SELECT DISTINCT AccountNo FROM [Jan Baseline]

You must enclose your tables in [ ] because you used spaces.

There are 2 ways to get the DISTINCT keyword in there: 1) switch to SQL View and type it in yourself or 2) in Design View, show the query property window and change Unique Values to Yes.

Lee
0
Gustav BrockCIOCommented:
The count is obtained this way:

SELECT
  [Jan Baseline].AccountNo,
  Count(*) AS AccountNoCount
FROM
  [Jan Baseline]
GROUP BY
  [Jan Baseline].AccountNo;

/gustav
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ArisaAnsarAuthor Commented:
Lee555J5 and Cactus Data,
Thank you both.  That helps very much.

DatabaseMX,
If I wanted to run against multiple tables, how should I proceed?
Thanks all!  I appreciate it very much.
0
lee555J5Commented:
After reading gustav's solution, I realized I may not have answered your question correctly. If you want a count of appearances of account numbers, gustav's solution is the one you want. If you want a list of account numbers with each number appearing only once no matter how many times it occurs in the table, my solution is the one you want.

Re: Multiple tables
It's the same as any other query. The FROM clause determines the recordset from which to select columns.

Modifying gustav's solution:

SELECT [Jan Baseline].AccountNo, Count(*) AS AccountNoCount
FROM [Jan Baseline] INNER JOIN [Mar Data] ON [Jan Baseline].AccountNo = [Mar Data].AccountNo
GROUP BY [Jan Baseline].AccountNo;

Modifying my solution:

SELECT DISTINCT [Jan Baseline].AccountNo
FROM [Jan Baseline] INNER JOIN [Mar Data] ON [Jan Baseline].AccountNo = [Mar Data].AccountNo;

Also, because the column (field) names in both tables are the same, you must specify the table name with the column name so the query knows which table's column to use.

Lee
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArisaAnsarAuthor Commented:
Thank you both
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.