Solved

Access-Repeating Accounts

Posted on 2011-03-19
7
251 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 333 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 333 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 49

Expert Comment

by:Gustav Brock
ID: 35331687
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This collection of functions covers all the normal rounding methods of just about any numeric value.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now