Select top x values in query (1 Viewer)

smasak · Nov 18, 2018

Good evening, in this sample i have a query named "QRTOTALTEAMcateg" which showing the total results by category, country and race.
I would like to select the top 4 results for PC category and the top 3 for SPR category.I tried with the command "select top 3" but my knowledge is poor .
The results will be displayed in the report "rptResultsTEAMcateg" and I will adapt them to "rptRESULTSteamTOTAL" report.

Thank you in advance.

Dreamweaver · Nov 18, 2018

Go To the designe tab and select return it will add SELECT TOP 5 or 10 Etc you can see this by going to SQL view and amend as needed.

smasak · Nov 18, 2018

Thank you for your answer, but it's not what I want.
I don't want the first five results but the 5 bigger ones.

Dreamweaver · Nov 18, 2018

You just Set the order assending or desending so the highest values will be the first in the list which will then be limited to the top 5 which should be the biggest.

smasak · Nov 18, 2018

I think it's not so easy..

Look, in Qry QRTOTAL there are fields SCORE1, SCORE2, SCORE3 by category and Nation and Date RACE.
In Qry QRTOTALTEAM they are summed up according to the nation and Date RACE.
But did not summed up the only 3 best scores but all nation scores.
For example in Qry QRTOTALTEAM GREECE in the category SPR it would have to be in the total1 116 not 151 etc....

Sorry i can not explain it in more details...

plog · Nov 18, 2018

No point writing paragraphs about what your data looks like--just show it. Provide use 2 sets of data:

A. Starting sample data from your table/query. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect to end up with.

Again, 2 sets of data--starting and ending. I recommend using this format if typing into the forum:

TableNameHere
FieldName1, FieldName2, FieldName3, ...
Steve, 18, 1/21/2018
Larry, 245, 2/26/2017

JHB · Nov 19, 2018

The problem is when you've more rows with the same value, and you haven't set up a criteria which one to pick out.
In the below picture, which one would you choose and why, (if you know the why, then set the why as the criteria)?

Select top x values in query (1 Viewer)

smasak

New member

Attachments

Dreamweaver

Well-known member

smasak

New member

Dreamweaver

Well-known member

smasak

New member

plog

Banishment Pending

JHB

Have been here a while

Attachments

Similar threads

Users who are viewing this thread