Prev: populating a query
Next: Updateable Query Problem
From: Deenos on 26 Apr 2010 15:01
I have a query that pulls together states from a program. I group the states
and added the Count aggregate to the state field. I want access to display
the top 10 states including an extra entry if two states have the same number.

I hope this is clear.

thank you,
--
Deenos
From: John Spencer on 26 Apr 2010 15:40
Using top 10 should work

SELECT TOP 10 States, Count(SomeField)
FROM SomeTable
GROUP BY States
ORDER BY Count(SomeField) Desc

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Deenos wrote:
> I have a query that pulls together states from a program. I group the states
> and added the Count aggregate to the state field. I want access to display
> the top 10 states including an extra entry if two states have the same number.
>
> I hope this is clear.
>
> thank you,
From: Jerry Whittle on 26 Apr 2010 15:47
Open the query in SQL View. Change the first line to say:

SELECT TOP 10 state

Make sure to sort (AKA group by) the Count([State]) field in DESC order.

However the above will only return extra records if there is a tie for the
10th position. A tie for only the 5th position will only return 10 records.
If there are more than 2 records tied for 10th, all ties will be returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Deenos" wrote:

> I have a query that pulls together states from a program. I group the states
> and added the Count aggregate to the state field. I want access to display
> the top 10 states including an extra entry if two states have the same number.
>
> I hope this is clear.
>
> thank you,
> --
> Deenos
From: Deenos on 27 Apr 2010 14:20
Great. Thank you. It worked
--
Deenos


"John Spencer" wrote:

> Using top 10 should work
>
> SELECT TOP 10 States, Count(SomeField)
> FROM SomeTable
> GROUP BY States
> ORDER BY Count(SomeField) Desc
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Deenos wrote:
> > I have a query that pulls together states from a program. I group the states
> > and added the Count aggregate to the state field. I want access to display
> > the top 10 states including an extra entry if two states have the same number.
> >
> > I hope this is clear.
> >
> > thank you,
> .
>
 | 
Pages: 1
Prev: populating a query
Next: Updateable Query Problem