Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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
0
ArisaAnsar
Asked:
ArisaAnsar
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
ArisaAnsarAuthor Commented:
Thank you both
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now