Solved

Access-Repeating Accounts

Posted on 2011-03-19
7
254 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

820 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