Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access-Repeating Accounts

Posted on 2011-03-19
7
Medium Priority
?
259 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:ArisaAnsar
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75
ID: 35174205
Are talking across multiple tables or just one table ?

mx
0
 
LVL 13

Assisted Solution

by:lee555J5
lee555J5 earned 1332 total points
ID: 35174210
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 total points
ID: 35174942
The count is obtained this way:

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

/gustav
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ArisaAnsar
ID: 35175539
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
 
LVL 13

Accepted Solution

by:
lee555J5 earned 1332 total points
ID: 35176580
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
 

Author Closing Comment

by:ArisaAnsar
ID: 35325795
Thank you both
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35331687
You are welcome!

/gustav
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question