Solved

Top 5 query access

Posted on 2014-01-21
7
381 Views
Last Modified: 2014-01-21
Hi,

Im looking to get the top 5 customers from an access query based on the count of a particular column

What function/formula would i use?

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 3
  • 3
7 Comments
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 200 total points
ID: 39796381
Hi,

pls try

SELECT TOP 5 Count(t.Ctr) AS Total
FROM Table AS t
ORDER BY Count(t.Ctr) DESC ;

Open in new window

Regards
0
 

Author Comment

by:Seamus2626
ID: 39796513
Hi by: Rgonzo1971,

im a beginner at access

Table name is calculation_d2_all and column name is Client Type Name

Can you fit that in?

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39796548
What column or calculation determines who the 'top 5' clients are?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Seamus2626
ID: 39796553
Can it be count of unique names in Client Type Name


eg

Sole Trader
Sole Trader
Sole Trader
Retail
Retail
Retail

So the top 5 based on the count of the individual client types in that column

Many thanks
0
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 39796595
Try this  - I'm assuming you've got something like an Autonumber or other ID field, which you should substitute for  "ID Field" in the following query:

SELECT TOP 5 COUNT([ID Field]) AS CountID, [Client Type Name]
FROM [calculation_d2_all]
GROUP BY  [Client Type Name]
ORDER BY COUNT([ID Field]) DESC

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39796602
This will also work, but is more generic and should work as-is without any field substitutions:

SELECT TOP 5 COUNT(*) AS CountID, [Client Type Name]
FROM [calculation_d2_all]
GROUP BY  [Client Type Name]
ORDER BY COUNT(*) DESC

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 39796612
Thanks guys!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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