Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query count top 10 (2000)

    Hi, have a table with the number of calls certain client make to the business. I need a list of the top ten clients but to detail each of those client contacts, example client 1, called 4 times then a list of the details of those 4 contacts? Is this possible nad how would the query need be constructed.

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query count top 10 (2000)

    One way is to create two queries:

    1) Create a query that lists the top 10 clients ordered by number of calls, descending (10 is not in the dropdown list for Top values when you design the query, but you can enter 10 manually).
    2) Create a query based on the contact details table and on the query you created in step 1. Join them on the client field, and add all the fields of the contact details table you need (or *) to the query grid.

    Another way:

    - Create a query based on your contact details table.
    - Add * to the query grid, and the client field.
    - Clear the Show check box for the client column, and enter the following in the Criteria line, substituting the appropriate names:

    In (SELECT TOP 10 [ClientID] FROM [tblCalls] ORDER BY [NumberOfCalls] DESC)

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query count top 10 (2000)

    Thanks Hans, I am ring rusty to say the least, only really get exposure to Access for a month or so every January these days. How do I do I order the query by number of calls?

    Thanks Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query count top 10 (2000)

    By selecting Ascending or Descending in the Sort row for the column that shows the number of calls.
    Attached Images Attached Images
    • File Type: png x.PNG (2.9 KB, 0 views)

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query count top 10 (2000)

    Hello, I am ever so sorry, I feel I am becoming an irritant on this issue. I have done as suggested but the query returns the ten customers each who have the highest number ID not the top ten customers who have referred to the company the most times. I am sorry if I have been ambiguous in my deascriptions. Say I had a call table, ID, caller ID I would like a list of the top ten referred callers.

    Thanks Darren.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query count top 10 (2000)

    You originally wrote "have a table with the number of calls certain client make". I now get the impression that the table doesn't contain the number of calls, but the individual calls. If that is correct, the first query should be changed:
    - Create a query based on the call table.
    - Add the Caller ID and ID fields to the query grid.
    - Select View | Totals.
    - Set the Total option for ID to Count.
    - Set the Sort option for ID to Descending.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window
    - Set the Top values option to 10.

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query count top 10 (2000)

    You are correct Hans, I was misleading in my original problem explanation, I do apologise.

    Many thanks for your considered patience.

    Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •