SQL top

[dee-u]

What's the description of the error raised?

[dglienna]

You could paste it into Access and execute it. It should give a descriptive error message.

[sacramento]

you can try with the attached project

[dee-u]

You just missed some & and "...

sql1 = "Select Top 3 * From ( " & _
     "select pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null)) " & _
     " ORDER BY classe desc,Totais desc "

[sacramento]

yes you are right...now the code work,but the "top 3" don't extract the desired values because the top 3 extract only 3 recordsets and not all the 3 big recordsets....strange!!!!!!!


Can you see the project attached,and run it,and change the top 3 for top 10?


Thanks

[szlamany]

You are missing an ORDER BY outside the subquery...

SELECT TOP 3 * FROM (...) ORDER BY TOTAIS DESC

Without that you are not going to get the proper top 3 - numerically highest.

[sacramento]

sory but I don't understand...I have a order outside of the subquery...I Think!!!!

do you have see the project?

Thnaks