Select Top 2 or 3 2

[rj51cxa]

The following code selects the top 2 from a query:

SELECT TOP 2 QryFibuaTeam_Crosstab.TxtUnit, QryFibuaTeam_Crosstab.Honours_Only, QryFibuaTeam_Crosstab.Total, ([8]+[9]+[10]+[11]) AS Practice1, ([12]+[13]+[14]+[15]) AS Practice2, QryFibuaTeam_Crosstab.[8], QryFibuaTeam_Crosstab.[12], QryFibuaTeam_Crosstab.[9], QryFibuaTeam_Crosstab.[13], QryFibuaTeam_Crosstab.[10], QryFibuaTeam_Crosstab.[14], QryFibuaTeam_Crosstab.[11], QryFibuaTeam_Crosstab.[15], IIf([fFibuaTeam]=True,"FIBUA","") AS Event
FROM QryFibuaTeam_Crosstab INNER JOIN QryFibuaTeam ON QryFibuaTeam_Crosstab.TxtUnit = QryFibuaTeam.TxtUnit
WHERE (((QryFibuaTeam_Crosstab.Honours_Only)=False))
ORDER BY QryFibuaTeam_Crosstab.Total DESC , ([8]+[9]+[10]+[11]) DESC , ([12]+[13]+[14]+[15]) DESC , QryFibuaTeam_Crosstab.[8] DESC , QryFibuaTeam_Crosstab.[12] DESC , QryFibuaTeam_Crosstab.[9] DESC , QryFibuaTeam_Crosstab.[13] DESC , QryFibuaTeam_Crosstab.[10] DESC , QryFibuaTeam_Crosstab.[14] DESC , QryFibuaTeam_Crosstab.[11] DESC , QryFibuaTeam_Crosstab.[15] DESC;

As you can see from the "Where" clause, I have excluded a team that is shooting for "Honours Only". What I would prefer to do is as follows:

If a team shotting for Honours Only comes second, the query selects the next team in the list and displays that as well. I can then show that in the results. If the second and third place team are Honours Only but the fourth is not, the fourth team is actually the second place winner. Consequently, the query should display four records. (I hope that makes sense!)

I'm sure this can be done and I would be grateful if someone could show me how.

Best Regards
John

 

[lespaul]

I would use a union query; the first to get the top two that were NOT Honours and the second to get the top two that WERE Honours:

SELECT TOP 2 "Not Honours" As Type, QryFibuaTeam_Crosstab.TxtUnit, QryFibuaTeam_Crosstab.Honours_Only, QryFibuaTeam_Crosstab.Total, ([8]+[9]+[10]+[11]) AS Practice1, ([12]+[13]+[14]+[15]) AS Practice2, QryFibuaTeam_Crosstab.[8], QryFibuaTeam_Crosstab.[12], QryFibuaTeam_Crosstab.[9], QryFibuaTeam_Crosstab.[13], QryFibuaTeam_Crosstab.[10], QryFibuaTeam_Crosstab.[14], QryFibuaTeam_Crosstab.[11], QryFibuaTeam_Crosstab.[15], IIf([fFibuaTeam]=True,"FIBUA","") AS Event
FROM QryFibuaTeam_Crosstab INNER JOIN QryFibuaTeam ON QryFibuaTeam_Crosstab.TxtUnit = QryFibuaTeam.TxtUnit
WHERE (((QryFibuaTeam_Crosstab.Honours_Only)=False))
UNION
SELECT TOP 2 "Honours" As Type, QryFibuaTeam_Crosstab.TxtUnit, QryFibuaTeam_Crosstab.Honours_Only, QryFibuaTeam_Crosstab.Total, ([8]+[9]+[10]+[11]) AS Practice1, ([12]+[13]+[14]+[15]) AS Practice2, QryFibuaTeam_Crosstab.[8], QryFibuaTeam_Crosstab.[12], QryFibuaTeam_Crosstab.[9], QryFibuaTeam_Crosstab.[13], QryFibuaTeam_Crosstab.[10], QryFibuaTeam_Crosstab.[14], QryFibuaTeam_Crosstab.[11], QryFibuaTeam_Crosstab.[15], IIf([fFibuaTeam]=True,"FIBUA","") AS Event
FROM QryFibuaTeam_Crosstab INNER JOIN QryFibuaTeam ON QryFibuaTeam_Crosstab.TxtUnit = QryFibuaTeam.TxtUnit
WHERE [b](((QryFibuaTeam_Crosstab.Honours_Only)=True))[/b]
ORDER BY QryFibuaTeam_Crosstab.Total DESC , ([8]+[9]+[10]+[11]) DESC , ([12]+[13]+[14]+[15]) DESC , QryFibuaTeam_Crosstab.[8] DESC , QryFibuaTeam_Crosstab.[12] DESC , QryFibuaTeam_Crosstab.[9] DESC , QryFibuaTeam_Crosstab.[13] DESC , QryFibuaTeam_Crosstab.[10] DESC , QryFibuaTeam_Crosstab.[14] DESC , QryFibuaTeam_Crosstab.[11] DESC , QryFibuaTeam_Crosstab.[15] DESC

you may need to tweak your Orderby in order to get them in the correct order for each type, but that should give you the right idea....

HTH

Leslie

Have you met Hardy Heron?

 

[rj51cxa]

Thanks Leslie,that is just the hint I needed. I'd forgotten about a Union Query and how useful they can be. Have a star.
Kind regards
John

 

[rj51cxa]

Hi Leslie,

This idea worked well and my code ends up looking like this:

SELECT TOP 2 "N_H" As Type, QryFibuaTeam_Crosstab.TxtUnit, QryFibuaTeam_Crosstab.Honours_Only, QryFibuaTeam_Crosstab.Total, ([8]+[9]+[10]+[11]) AS Practice1, ([12]+[13]+[14]+[15]) AS Practice2, QryFibuaTeam_Crosstab.[8], QryFibuaTeam_Crosstab.[12], QryFibuaTeam_Crosstab.[9], QryFibuaTeam_Crosstab.[13], QryFibuaTeam_Crosstab.[10], QryFibuaTeam_Crosstab.[14], QryFibuaTeam_Crosstab.[11], QryFibuaTeam_Crosstab.[15], IIf([fFibuaTeam]=True,"FIBUA","") AS Event
FROM QryFibuaTeam_Crosstab INNER JOIN QryFibuaTeam ON QryFibuaTeam_Crosstab.TxtUnit = QryFibuaTeam.TxtUnit
WHERE (((QryFibuaTeam_Crosstab.Honours_Only)=False))
UNION SELECT TOP 2 "H" As Type, QryFibuaTeam_Crosstab.TxtUnit, QryFibuaTeam_Crosstab.Honours_Only, QryFibuaTeam_Crosstab.Total, ([8]+[9]+[10]+[11]) AS Practice1, ([12]+[13]+[14]+[15]) AS Practice2, QryFibuaTeam_Crosstab.[8], QryFibuaTeam_Crosstab.[12], QryFibuaTeam_Crosstab.[9], QryFibuaTeam_Crosstab.[13], QryFibuaTeam_Crosstab.[10], QryFibuaTeam_Crosstab.[14], QryFibuaTeam_Crosstab.[11], QryFibuaTeam_Crosstab.[15], IIf([fFibuaTeam]=True,"FIBUA","") AS Event
FROM QryFibuaTeam_Crosstab INNER JOIN QryFibuaTeam ON QryFibuaTeam_Crosstab.TxtUnit = QryFibuaTeam.TxtUnit
WHERE (((QryFibuaTeam_Crosstab.Honours_Only)=True))
ORDER BY Total DESC , Practice1 DESC , Practice2 DESC , [8] DESC , [12] DESC , [9] DESC , [13] DESC , [10] DESC , [14] DESC , [11] DESC , [15] DESC;

The problem is that the sorting does not work. The results come out in "type" order and not in "Total" - i.e. the first two results are "N_H" and the third and fourth results are "H". No matter what I do, I can't get them to sort in "Total" order.

I tried creating a new query based on the Union query and I can sort on this one OK, but I don't really want another query, as this just increases the size of the database.

Is there any way of modifying the original query so that it sorts correctly?

Best Regards
John

 

[PHV]

OREDER BY 4 DESC, 5 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rj51cxa]

Thank PHV, that solved it. You learn something new every day! Have a star.
Best Regards
John