Multiple Top Values

[Don]

I'm not a strong individual with VBA code; however, let me explain what I
think I know and I'm hoping someone will show and explain in simple enough
terms for this humble grasshopper to understand.

I have a table the the following field names which are all text fields:

[PBH Description]
[Item #]
[AvgMov]

In my query, I want to return the top 5 Abs([AvgMov]) for each change in the
[PBH Description] field. I'd like the query to also match up the [Item #] to
each [AvgMov] returned. Additionally, if I wanted to change the 5 to the top
10%, how would I do this. Also, some of the values in the [AvgMov] column
could be the same value. If they are, I'd like to see all those values if
they make up the top 5 or top 10%.

Just so I understand: does any code you may show me, go into a module that
is referred to in the query? I don't understand this process; however, I'm a
visual person and if I can see how something works, I can use that knowledge
for future uses.

Thanks to anyone that would help me with this.

 

[John W. Vinson]

In my query, I want to return the top 5 Abs([AvgMov]) for each change in the
[PBH Description] field.

"Every change" suggests to me that you're viewing the table as if it were a
spreadsheet, with a defined order of records (i.e. the PBH Description in this
row is different from the PBH Description in the previous row, indicating a
break for the calculation).

Unfortunately tables don't work this way! A Table is an unordered "heap" of
records. There is nothing that I can see in your table definition that would
allow "the top 5" or "the top 10%" to be defined - top 5 of *what group*?

More info please!

 

[John Spencer]

The SQL for Top N of a group would look like the following
SELECT *
FROM TheTable
WHERE AvgMov in
(SELECT TOP 5 AvgMov
FROM TheTable as Temp
WHERE Temp.[PBH Description] = TheTable.[PBH Description]
ORDER BY Temp.AvgMov Desc)

Change TOP 5 to TOP 10 PERCENT if you want a percentage of the records
returned. If you want to change the TOP N on the fly, you will need to build
the query string on the fly OR use a different query structure. See one idea
below.

Alternative to Top N%

(This drops ties)
SELECT T1.data_col
FROM Test AS T1
WHERE (
SELECT COUNT(*) * 0.3333333333333333333333333
FROM Test AS T3
) >= (
SELECT COUNT(*)
FROM Test AS T2
WHERE T1.data_col >= T2.data_col
)

(This includes ties)
SELECT T1.data_col
FROM Test AS T1
WHERE (
SELECT COUNT(*) * 0.3333333333333333333333333
FROM Test AS T3
) >= (
SELECT COUNT(*)
FROM Test AS T2
WHERE T1.data_col > T2.data_col
)


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