# Thread: Query Topt ten and total (2003)

1. ## 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. ## 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. ## Re: Query Topt ten and total (2003)

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

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•