Limit Records

[DS]

I want this query to return only the first ten records. How can I do this?
Thanks
DS

SELECT [CusFirstName] & " " & [CusLastName] AS CUSS, "DOLLAR" AS TYPE,
Sum(tblChecks.ChkTotal) AS SumOfChkTotal
FROM tblCustomers INNER JOIN tblChecks ON tblCustomers.CustomerID =
tblChecks.ChkCustomerID
GROUP BY [CusFirstName] & " " & [CusLastName], "DOLLAR"
ORDER BY Sum(tblChecks.ChkTotal) DESC;

 

[DS]

OK I found the Top Value thing, which I assume is what I need. But I for
the life of me can't figure out how to implement it. Any help appreciated.
Thanks
DS

 

[John Spencer]

Add Top 10 to the start of the query.

SELECT Top 10 [CusFirstName] & " " & [CusLastName] AS CUSS
, "DOLLAR" AS TYPE
, Sum(tblChecks.ChkTotal) AS SumOfChkTotal
FROM tblCustomers INNER JOIN tblChecks
ON tblCustomers.CustomerID = tblChecks.ChkCustomerID
GROUP BY [CusFirstName] & " " & [CusLastName], "DOLLAR"
ORDER BY Sum(tblChecks.ChkTotal) DESC;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Golfinray]

If you go to the query properties (right click in the area where the tables
or queries go) and select top values then top 10 that should get it.

 

[John Spencer]

The Top records are determined by the sort order imposed in the Order By
clause. In Access Top 10 returns ties (so if the nth value is
duplicated, all those records with the nth value will be returned.

If you want to get only 10 then add more fields to the Order By clause.
With your query you only real choice is to add
[CusFirstName] & " " & [CusLastName]
To the sort order.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Add Top 10 to the start of the query.

SELECT Top 10 [CusFirstName] & " " & [CusLastName] AS CUSS
, "DOLLAR" AS TYPE
, Sum(tblChecks.ChkTotal) AS SumOfChkTotal
FROM tblCustomers INNER JOIN tblChecks
ON tblCustomers.CustomerID = tblChecks.ChkCustomerID
GROUP BY [CusFirstName] & " " & [CusLastName], "DOLLAR"
ORDER BY Sum(tblChecks.ChkTotal) DESC;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I want this query to return only the first ten records. How can I do
this?
Thanks
DS

SELECT [CusFirstName] & " " & [CusLastName] AS CUSS, "DOLLAR" AS TYPE,
Sum(tblChecks.ChkTotal) AS SumOfChkTotal
FROM tblCustomers INNER JOIN tblChecks ON tblCustomers.CustomerID =
tblChecks.ChkCustomerID
GROUP BY [CusFirstName] & " " & [CusLastName], "DOLLAR"
ORDER BY Sum(tblChecks.ChkTotal) DESC;

 

[KARL DEWEY]

Other ways are to click in the icon that reads 'All' when you have the query
open in design view and type in 10.
In the SQL view enter TOP X like this --
SELECT TOP 10 [tableName].[FirstField], [SecondField], ....

 

[DS]

Thanks John,
Works Perfectly.
DS

 

[DS]

Thnak You. Now I see it!
DS

 

[DS]

Thanks Karl. Where on earth is the "ALL" Icon. I'm looking but I cannot
find it.
DS