Top 20 Query or Report

[jesusoneez]

I have a table of sales figures and I want to extract the Top 20 results from [InvoiceValue]. I've tried something along the lines of;

SELECT TOP 20 tblChelmsfordSales.InvoiceValue, <more fields...>
FROM tblChelmsfordSales;

but this doesn't appear to bring in the top twenty results from the given data. For example, the top result it gives in the query is £3,000 but in reality, the highest information for the given date range is over £50,000. I'm not sure why this isn't working.

Do I have to use SQL for this or can it be done in the Query Designer? I don't really know any SQL so the easier the better really!

Would it be easier to query everything and then somehow display the Top 20 in a report?

Any help with this would be much appreciated.

Regards,

Steve

 

[maxmangion]

since you want to return the top 20 according to the highest value, you short sort your query descending.

 

[jesusoneez]

Heh. Like I say, I don't really know SQL. I just assumed the TOP 20 automatically selects the highest figures...I'll give it a go.

Ta,

Steve

 

[maxmangion]

if you are working in the query design view, you can choose the sort order to descending from your sort row. Alternatively, if you're typing the SQL yourself, you should put Order By Fieldname DESC at the end of your SQL statement.

 

[maxmangion]

using your example above, it should be:

SELECT TOP 20 tblChelmsfordSales.InvoiceValue, <more fields...>
FROM tblChelmsfordSales ORDER BY tblChelmsfordSales.InvoiceValue DESC;