Solved

Top 5 query access

Posted on 2014-01-21
7
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 51

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 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