Selecting Top 2 Records 1

[bxgti4x4]

I have a table named TblPresident containing the following three fields:
LngCompetitorNo
TxtUnit
SumOfLngPracticeScores

For each TxtUnit there are four SumOfPracticeScores, one for each LngCompetitorNo. I need to create a query which selects the lowest two scores only for each unit.

Could anyone please advise me how to proceed

Thanks a lot
John

 

[amorous]

Try this:

SELECT t1.*
FROM TblPresident t1
WHERE t1.SumOfLngPracticeScores In (select top 2 t2.SumOfLngPracticeScores from TblPresident t2 where t2.TxtUnit = t1.TxtUnit ORDER BY t2.SumOfLngPracticeScores Asc);


-VJ

 

[bxgti4x4]

Thanks Amorous, this did exactly what I wanted. However, before I create the report based on this information, I would like to create a cross-tab query so that the competitor names can be shown against the total of their combined scores, sorted by Txt Unit. This will enable me to create a report that shows the winning TxtUnit as the first record.

If it is not too much trouble, could you please advise how to do this.

Thanks in advance
John

 

[amorous]

John...

I am not sure what you want?? Can you show some of your data and the kind of output you are looking for?

-VJ

 

[bxgti4x4]

Hi Amorous

I used your code to design a Query which produces data as follows:

LngCompetitorNo TxtUnit SumOfLngPracticeScore TxtName TxtInitials TxtRank_Rating fWRN TxtClass
3 AIR-CAMBRIA 44 HODKINSON K. AB -1 T
2 AIR-CAMBRIA 116 PARR C. PORS 0
6 CAROLINE 36 IRVINE K AB -1 T
10 CAROLINE 92 MILLER D. AB 0 T

What I would like to see from the Cross-Tab query is:
TxtUnit TxtName1 TxtName2 TotalScore
Air-Cambria Hodkinson Parr 160
Caroline Irvine Miller 128

I would like to see initials and rank as well but I don't think that this is possible because a Cross-Tab query only allows one column heading and this would have to be TxtName.

I hope this is enough to show you what I would like.

Best Regards
John

 

[bxgti4x4]

Hi Amorous,

I think I have partly solved the problem myself. I created a report based on the query I wrote from the information you gave me, grouped the data by TxtUnit and created a group footer in which I placed an Unbound Text Box with the Control Source set to =Sum(SumOfLngPracticeScore]).

This gives me the total of the two scores but I cannot find a way of ordering the report to show the TxtUnit with the highest total first. If I can do this, my problem is solved.

Any ideas would be much appreciated.

Best Regards
John

 

[bxgti4x4]

Hi again Amorous

I think I have cracked this one all on my own!

I created a second query which summed the totals of the two LngPracticeScore fields for each TxtUnit and used this as the main report. I used my orginal report as a sub report and I now have just what I need.

Thanks for all your help, you gave me the start I needed.

Best Regards
John