Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Top 5 query access

Posted on 2014-01-21
Medium Priority
385 Views
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
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
• 3
• 3

LVL 52

Assisted Solution

Rgonzo1971 earned 800 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 ;
``````
Regards
0

Author Comment

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

ID: 39796548
What column or calculation determines who the 'top 5' clients are?
0

Author Comment

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

eg

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

mbizup earned 1200 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
``````
0

LVL 61

Expert Comment

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
``````
0

Author Closing Comment

ID: 39796612
Thanks guys!
0

## Featured Post

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month9 days, 18 hours left to enroll