Solved

Top 5 query access

Posted on 2014-01-21
7
382 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 50

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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