Easy Question: 10 first records

[FlavioT]

Hi, I have a Querry that has listed everyone who had returned checks here. So i want a form with just the 10 top people who had the checks returned...


I already did the Querry OK! Just need to know how can I select the "N" first record!

I can't figure it out!

Flavio
ray:

Sql code:

SELECT DISTINCTROW CPF.Nome, Sum(Cheques.Valor) AS Dev, Sum(Cheques.[Valor Pago]) AS Rec, Sum([valor])-Sum([valor pago]) AS ss, Count(CPF.Nome) AS Qtde
FROM CPF INNER JOIN Cheques ON CPF.CPF = Cheques.CPF
GROUP BY CPF.Nome
ORDER BY Sum([valor])-Sum([valor pago]) DESC;

 

[mdmilner]

SELECT TOP 10 * FROM tblName

ORDER BY determines which are the top 10 entries.

 

[FlavioT]

Sorry,

I think I didn't told exactally what I needed!

I'll load a Form before the querry and the user would choose (textbox) the number of the top "n"..... is it possible?
I thought I could count the records and but a condition linking to this form...

But how can I count records? Like the first one would be "1" the second "2"...
and the filter <=Forms!... etc...

Thanks! ray:

 

[mdmilner]

Create a crosstab query using the wizard interface to count the results.
Create another query that does a SELECT TOP X based on the Crosstab query.

This will not work for a variable number of items.
What you could do is place VBA code behind the textbox so that during the _AfterUpdate event it dynamically generates the same crosstab query and the final query selects the correct number of top items.

If IsNumeric(Me!TetBoxName.Value) Then
  strSQL = SELECT TOP " & Me!TextBoxName.Value & "rest_of_query"
End If

This is an incomplete SQL string but it demonstrates how to concatenate the numeric value from the textbox. IsNumeric tests for a number - mandatory unless you don't mind generating errors should the user type something else. You can also use Validation in the TextBox properties.

The below is a code snippet – how to declare a a querydef (create a query listed under that tab) from code.

Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim strSQL As String

  Set qdf = dbs.CreateQueryDef("queryname", strSQL)
  DoCmd.OpenQuery "queryname", , acEdit

Mike