Menu
My Access database table has 2 columns: name and price. I want to do a query that select the top 10 highest prices. How to do this? Thanks.
810
On the Design tab, in the Query Setup group, click the down arrow next to Return (the Top Values list), and either enter the number or percentage of records that you want to see, or select an option from the list. Click Run to run the query and display the results in Datasheet view.
In the Cost column, click the Sort box list arrow and select Descending. Next you have to use the Top Values list to specify the number of top values you want to be displayed in your query results. Click the Top Values list arrow on the toolbar and select 5, as shown in figure.
You can use the Max function in a query by clicking on the Totals button in the toolbar (This is the button with the summation symbol). The Max function is used in conjunction with the Group By clause. This query would return the maximum UnitsInStock for each ProductName.
select top 10 Name, Price from MyTable order by Price desc Updated: @Remou pointed out that:
"Access SQL selects matches, so it will select all items with the same highest prices, even if this includes more than 10 records. The work-around is to order by price and a unique field (column)."
So, if you have a unique product code column, add like so:
select top 10 Name, Price from MyTable order by Price desc, UniqueProductCode desc If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
