"Top Three Answers" on the board??

  • Thread starter Thread starter TA via AccessMonster.com
  • Start date Start date
T

TA via AccessMonster.com

Hello everyone,

I'm trying to figure out how I can report on a query's top three values in a
particular field along with the number of times each of those values appears.
Can anyone point me in the right direction?

Many thanks!

-TA
 
Take a look at "Totals" queries, and the Top property of the query. You
will basically use "Totals" to get a count of rows (group by the field
values, count, say, the row ids). Then sort descending. Finally, use the
Top property to indicate the top 3.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff said:
Take a look at "Totals" queries, and the Top property of the query. You
will basically use "Totals" to get a count of rows (group by the field
values, count, say, the row ids). Then sort descending. Finally, use the
Top property to indicate the top 3.
Click to expand...

Here's a "relational" version of the TOP 3 syntax, using Nothwind's
[Order Details] table

SELECT DT1.ProductID, DT1.count_orders
FROM (
SELECT T1.ProductID,
COUNT(*) AS count_orders
FROM [Order Details] AS T1
GROUP BY T1.ProductID
) AS DT1
WHERE 3 > (
SELECT COUNT(*) FROM (
SELECT T2.ProductID,
COUNT(*) AS count_orders
FROM [Order Details] AS T2
GROUP BY T2.ProductID
) AS DT2
WHERE DT2.count_orders > DT1.count_orders)

In case you are wondering how that WHERE clause came about, consider

SELECT DT1.ProductID,
DT1.count_orders, (
SELECT COUNT(*) FROM (
SELECT T2.ProductID, COUNT(*) AS count_orders
FROM [Order Details] AS T2
GROUP BY T2.ProductID
) AS DT2
WHERE DT2.count_orders > DT1.count_orders
) AS count_greater_than_count_orders
FROM (
SELECT T1.ProductID,
COUNT(*) AS count_orders
FROM [Order Details] AS T1
GROUP BY T1.ProductID
) AS DT1
ORDER BY DT1.count_orders DESC
 
Thanks to both of you. This is exactly what I needed!

All the best,

TA
 
Back
Top