• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Top 5 query access

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
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
2 Solutions
 
Rgonzo1971Commented:
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
 
Seamus2626Author Commented:
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
 
mbizupCommented:
What column or calculation determines who the 'top 5' clients are?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Seamus2626Author Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
 
Seamus2626Author Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now