Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Topt ten and total (2003)

    I have a query where i have hospital data and I am doing a count on claims in descending order and I want the top ten to appear and have all the others grouped as others. Also, in the report , I want the total line for all facilties as well as percentage of claims to the right. Any ideas on this?

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

    Re: Query Topt ten and total (2003)

    1) Start by creating a top ten query based on your table(s) or query.

    2) Next, create a query based on the same table(s) or query and set the criteria for the ID field on which you group to

    Not In (SELECT ID FROM [TopTenQuery])

    Substituting the correct names of course.
    Change this query to a Totals query.
    Change the Total option for the ID field to Where.
    Add a dummy column Other: Other and set its Total option to the default Group By.
    Add any field and set the Total option to Count.

    3) Create a Union query based on those created in 1) and 2)

    4) Create a Totals query that returns the total count (this query will have only one field and one record)

    5) Create a query based on the Union query from 3) and on the Totals query from 4). Calculate the percentage of the total in this query.
    You can use this query as record source for your report.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Topt ten and total (2003)

    When I run the query which is based on the top ten query, no results appear.

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

    Re: Query Topt ten and total (2003)

    Query 2) should *not* be based on the top 10 query, but on the same table(s) and/or query/ies as the top 10 query itself.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Topt ten and total (2003)

    I understand now. To be a union query all the same fields have to be in the 2 queries correct?

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

    Re: Query Topt ten and total (2003)

    The number and type of the fields must be the same, but they don't have to have the same names - the union query will use the field names from the first query.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Topt ten and total (2003)

    Works well and I have created a report with this query. Problem is the top ten facilties are listed and the others are grouped together but there is nothing under facility name for others. Is there a way to make it say others just for the others line of data?

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

    Re: Query Topt ten and total (2003)

    You should be able to specify this in the query mentioned under 2) in my first reply. The line
    <hr>Add a dummy column Other: Other and set its Total option to the default Group By.<hr>
    was meant for this.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Topt ten and total (2003)

    Thanks. I see where I messed up.

Posting Permissions

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