Sorting Data from a Crosstab query 1

[rj51cxa]

The following crosstab query lists a set of results based on a total score:

TRANSFORM Sum(QryFibuaComb_Team.Score) AS SumOfScore
SELECT QryFibuaTeam.TxtUnit, QryFibuaTeam.TotalFibua AS Total, QryFibuaTeam.Honours_Only, TblBisleyYear.BisleyYear
FROM (TblBisleyYear INNER JOIN QryFibuaTeam ON TblBisleyYear.BisleyYear = QryFibuaTeam.BisleyYear) INNER JOIN QryFibuaComb_Team ON QryFibuaTeam.TxtUnit = QryFibuaComb_Team.TxtUnit
GROUP BY QryFibuaTeam.TxtUnit, QryFibuaTeam.TotalFibua, QryFibuaTeam.Honours_Only, TblBisleyYear.BisleyYear
ORDER BY QryFibuaTeam.TotalFibua DESC 
PIVOT QryFibuaComb_Team.LngEventRef;

The total score (TotalFibua)is based on the sum of six events, the scores for which are shown when the query is run.

From this query, I can create a report in which I can sort initially by TotalFibua and then by the scores for each individual event (LngEventRef) or combination thereof. This resolves the problem of a tie with the total score.

In order to publish the results, I need to isolate first and second place, as generated in the report, into a new report but I cannot find a way of selecting the top 2 in a crosstab query, or from the report that is based on it.

Is there a way of achieving this, either from the crosstab query or being selective in the report (which is already sorted)?

Best Regards
John

 

[dhookom]

If you want to display only the first two record in a report or group, you can:
[tt][blue]
1) add a text box to the detail section
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: Yes or No
2) Add code to the On Format event of the Detail Section

   Cancel = Me.txtCount > 2

[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Hi Dhookom,

That bit of information will be very useful but, before I could use it, I needed to find a way of sorting, as I explained.

However, I did manage to solve the problem by creating another query, based on the crosstab query. This showed me the scores for each event (didn't realise it would do that) so that I was able to move them into the right order and sort them in descending order. Once I had done that, I was able to select the top two from the query toolbar. I think that this is probably the easiest way in the end.

Thanks for your prompt reply, it was much appreciated. Its good to know these extra tips for the future.

Best Regards
John