how to get top 3 most frequent record?

[jerry28ph]

Hi,

I am having a problem in solving to get the top 3 calling numbers. I m doing a call log sheet report to determine which number is the most frequent callers. Say i have 10 records of callers and 3 out of 10 callers is from 1-800-204-5255 toll free numbers. How could I write a SQL script to get this toll free number and display how many times it called.

Please help.
Thank you.

Regards,
Jerry

 

[speakers_86]

Just create the query! There is a place that you can enter Top N. Just select 3 or whatever you want.

 

[Mike375]

In a query drag down the field in question and drag it again so it will be on the field line twice

On the toolbar there is a button that looks like an E. When the mouse pointer goes on it "totals" will come up. Click it. You will then see another row added to the query grid called Group By and it will be under the field names you dragged down.

Click on the Group By on the second field and a drop down list will show, click Count.

That query will then dispay one record for each phone number and in the other field will be the number of times the entry is there.

 

[jerry28ph]

hi Mike375,

I did what you said on your first reply, I created a query with 2 same field names, and used "E" Totals on the tool bar, Group By appeared on the first field and I chose Count on the second field. When I ran the query, it gave me incorrect values. I have an example,

Phone Nos:
4166381083 has 20 calls
4162041000 has 35 calls
4165004100 has 40 calls
4162001041 has 8 calls

I want to display the top 3 frequent callers as follows:

4165004100 has 40 calls
4162041000 has 35 calls
4166381083 has 20 calls

Please if you have an idea, I am asking again your help.
Thank you.

Regards,
Jerry

 

[jerry28ph]

hi speakers_86,

I just did the query, but where can I put the expression "Top N" in the query?
Please need your help.

Thanks.
Jerry

Just create the query! There is a place that you can enter Top N. Just select 3 or whatever you want.

 

[Mike375]

In the query I gave you select sort Descenting in the Count field and with the field highlighted right click in the open grey area of the query design screen and then click properties and Top Values and enter 3 (the default is All)

 

[raskew]

Hi -

Here's a tested sample query based on Northwind's Orders table.
It returns the top 3 customers based on the number of orders.
If you're still having problems, you might copy this to a new
query, substituting table/field names as necessary.

SELECT
    top 3 Orders.CustomerID
  , Count(Orders.CustomerID) AS CountOfCustomerID
FROM
   Orders
GROUP BY
   Orders.CustomerID
ORDER BY
   Count(Orders.CustomerID) DESC;

HTH - Bob

 

[jerry28ph]

Hi Mike375,

I'd like to thank you for your help to all my questions, I really appreciated it. I did you email re the top 3 frequent calls and it works.
My last concern is, how do I put "DESC" order on the SQL, I tried to add it on the 2nd field after GROUP by but it didnt work. After I ran the query, I need to click DESC button to get the top 3.
You're such a genuis man.

REgards,

Jerry

In a query drag down the field in question and drag it again so it will be on the field line twice

On the toolbar there is a button that looks like an E. When the mouse pointer goes on it "totals" will come up. Click it. You will then see another row added to the query grid called Group By and it will be under the field names you dragged down.

Click on the Group By on the second field and a drop down list will show, click Count.

That query will then dispay one record for each phone number and in the other field will be the number of times the entry is there.

 

[Mike375]

Top Values 3 gives you the 3 from the Group By. You will only have 3 records show on the query.

Again, put the field that has these values on the query grid twice. Do one as Group By and the other is Count from the Group By drop down list. On the row "sort" under the Count select Descending from the drop down list.

Top Values 3 you get from right clicking above the query grid area or below, the area that is grey, click Properties on the menu and then you will see Top Values. It defaults to All so change it to 3 or whatever.

 

[Mike375]

PS,

You have an easy to see solution just above your post, Raskew's post.

Copy what he posted and change the table and field name to match yours.

Start a new query and from bring in your table. Then go to View on the Toolbar and click SQL then past in Raskews code. Back to View and click Design and you should see it done.

Over and out and good luck.

 

[jerry28ph]

This time, I would like to give you thanks and I did it very well, ofcourse without your help, i dont think I could made. You mean a lot to me, thanks for all the help. I'll keep in touch with you Mike375 for my other things that really made confusing.

Best Regards,
Jerry

PS,

You have an easy to see solution just above your post, Raskew's post.

Copy what he posted and change the table and field name to match yours.

Start a new query and from bring in your table. Then go to View on the Toolbar and click SQL then past in Raskews code. Back to View and click Design and you should see it done.

Over and out and good luck.