Show highest numbers in a query

[NickNeville]

Good morning
Could someone kindly show me how to show only the highest numbers in a query.
I have a table with "scores" and am trying to show only the top 5 scores on a report.
Is this possible ? I have looked at the Max total but that doesn't seem to help much.

Many thanks
Nick

 

[Galaxiom]

Open your query in sql view.
Insert TOP 5 just after the SELECT

SELECT TOP 5 fieldname1, fieldname2, etc FROM tablename etc

 

[pbaldy]

Have you tried the TOP predicate?

SELECT TOP 5 FieldName
FROM...
ORDER BY...

 

[NickNeville]

Hi there Guys
many thank for your quick response. This is my first in SQL view
Could you be a bit more specific please.
My query has "scores" fild and the tables being used are are "members" and " "scores table" [joined]
Have tried the SQL but it asks me for Statement?
I am obviously writing something wrong.
Many thanks again
Nick

 

[Galaxiom]

Make you query in Design view. Make sure it works and shows the full data then switch to sql view.

All you should have to do is add TOP 5 after the SELECT so it reads something like this:
SELECT TOP 5 [scores] FROM [Members] INNER JOIN [Scores table] ON [Member].[ID] = [Scores Table].[MemberID] ORDER BY [Scores Table].[scores];

BTW Drop the spaces in names. It makes extra work because you have to always use the square brackets.
I like to use CapitalisedWords WithoutSpaces as names. It is easier than hitting underscores.

Many developers use tbl_SomeName but I am not a fan of this convention. I only use it sometimes in code examples here.

 

[NickNeville]

Hi there and thanks again, but still not working for me,May I send you a snapshot of my SQL page and Query pane please ?
Nick

 

[pbaldy]

You should be able to post them here.

 

[NickNeville]

many thanks Paul
My sql and copy query pane attached in zip as slightly to big otherwise
thanks again

nick

 

[pbaldy]

So did you try what Galaxiom suggested; adding "TOP 5", like:

SELECT TOP 5 Members.[Membership No], Members.Surname...

 

[NickNeville]

Yes try select TOP 5 but it gave me the top 5 membership numbers rather than the top 5 scores. I guess its me. But I could not get that to work.

Nick

 

[pbaldy]

It's based on the sort in the query, which you have as

Members.Class

It sounds like you want to change that to scores. Visualize that the TOP predicate will give you the top first x of whatever the query displays. You need to get the query returning records such that the records you want are the first ones displayed. Then the TOP predicate should work as desired. It can also be changed via the query properties by the way ("Top Values").

 

[NickNeville]

Ah you mean to put Score in the first query slot and the rest follows ?
Nick

 

[Galaxiom]

It the ORDER BY part of the sql which governs which records appear at the top. The TOP predicate only indicates the number of records to return.

 

[NickNeville]

yes I see now, it works ok
many thanks everyone for your help and most of all your patience
best rgds
Nick