SQL TOP question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was quite interested in some SQL I saw earlier in this site from Allen
Browne and John Spencer. If the poster is OK with this, I'd like to steal it
for my app that deals with ping-pong tounaments. The SQL involved
SELECT........ (SELECT Top 3 ....) ...

Here is what I'm trying to do. when we have tournaments with only a handful
of games, we base the team award on the top 3 scores. But if there are many
teams and many games in a large touney, I'll base the team award on the top 4
or even 5 scores. Instead of determining the team award by hand as I now do,
I'd like my scoring app to have a combox with a value list of 3,4,5. Here
is my question - how do I chose 4 from the combox, for example, then get the
SQL to return Top 4, not Top 3?

Thanks to the contributors of this site
 
You cannot provide a number for TOP as a parameter at runtime.

Therefore you will need to generate the SQL statement dynamically.

Assuming you have created a query named Query1 that contains the right
syntax, switch it to SQL View. Copy the SQL statement into a string in your
code. Break it into 2 strings where the number needs to go. You can then
patch in a number, and assign it to the SQL property of the query, or
directly to the RecordSource of a form (or report in Report_Open.)

This kind of thing:
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE Table1.ID IN (SELECT TOP "
Const strcTail = " ID FROM Table1 AS Dupe WHERE ... );"

strSql = strcStub & "5" & strcTail
CurrentDb.QueryDefs("Query1").SQL = strSql
'Forms!Form1.RecordSourse = strSql

I assume you were referrring to this example:
http://allenbrowne.com/subquery-01.html#TopN
 
works like a charm! much thanks
--
Thanks
Sophie


Allen Browne said:
You cannot provide a number for TOP as a parameter at runtime.

Therefore you will need to generate the SQL statement dynamically.

Assuming you have created a query named Query1 that contains the right
syntax, switch it to SQL View. Copy the SQL statement into a string in your
code. Break it into 2 strings where the number needs to go. You can then
patch in a number, and assign it to the SQL property of the query, or
directly to the RecordSource of a form (or report in Report_Open.)

This kind of thing:
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE Table1.ID IN (SELECT TOP "
Const strcTail = " ID FROM Table1 AS Dupe WHERE ... );"

strSql = strcStub & "5" & strcTail
CurrentDb.QueryDefs("Query1").SQL = strSql
'Forms!Form1.RecordSourse = strSql

I assume you were referrring to this example:
http://allenbrowne.com/subquery-01.html#TopN
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top