Top 11%

[Beagle1927]

Hello,

I'm trying to create a query that pulls the top total amount paid
based on a combo box in a form such as 11%. where do I put the form
name and combo box in the SQL statement.

SELECT TOP 35 PERCENT Query1.[Cust ID], Sum(Query1.[Total Amount
Paid]) AS [SumOfTotal Amount Paid]
FROM Query1
GROUP BY Query1.[Cust ID]
ORDER BY Sum(Query1.[Total Amount Paid]) DESC;

 

[Guest]

SELECT TOP 11 PERCENT Query1.[Cust ID],
Sum(Query1.[Total Amount Paid]) AS [SumOfTotal Amount Paid]
FROM Query1
WHERE Query1.?????? = [Forms]![FormName]![FieldName].[value]
GROUP BY Query1.[Cust ID]
ORDER BY Sum(Query1.[Total Amount Paid]) DESC;

 

[Marshall Barton]

I'm trying to create a query that pulls the top total amount paid
based on a combo box in a form such as 11%. where do I put the form
name and combo box in the SQL statement.

SELECT TOP 35 PERCENT Query1.[Cust ID], Sum(Query1.[Total Amount
Paid]) AS [SumOfTotal Amount Paid]
FROM Query1
GROUP BY Query1.[Cust ID]
ORDER BY Sum(Query1.[Total Amount Paid]) DESC;

You can not use a parameter for the TOP NN.

You can use VBA to construct the SQL statement and stuff
that into a form/report record source when it opens.

Dim strSQL As String
strSQL = "SELECT TOP " & Forms!theform.thecombo & _
" PERCENT [Cust ID], Sum([Total Amount Paid]) " _
& "AS [SumOfTotal Amount Paid] " & _
"FROM Query1 " & _
"GROUP BY [Cust ID] " & _
"ORDER BY Sum([Total Amount Paid]) DESC
Me.RecordSource = strSQL

 

[John Spencer]

If you mean that you want to change the percent on the fly based on a value
from a combobox, the answer is - you can't do this using Top and a reference
to a combobox.

You can build the query on the fly with VBA.

There is another method to doing this but I don't have an example handy.
Something like the following for selecting the top 33% (with ties)

SELECT T1.data_col
FROM Test AS T1
WHERE (
SELECT COUNT(*) * 0.3333333333333333333333333
FROM Test AS T3
) >= (
SELECT COUNT(*)
FROM Test AS T2
WHERE T1.data_col > T2.data_col
)

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

 

[Michel Walsh]

Just for info, with MS SQL Server 2005, you can use a parameter for TOP:


DECLARE @N AS int
SET @N = 11
SELECT TOP (@N) PERCENT WITH TIES * FROM somewhere


Note the use of parenthesis. Does not work with Jet, though, neither with
older MS SQL Server versions.


Vanderghast, Access MVP

I'm trying to create a query that pulls the top total amount paid
based on a combo box in a form such as 11%. where do I put the form
name and combo box in the SQL statement.

SELECT TOP 35 PERCENT Query1.[Cust ID], Sum(Query1.[Total Amount
Paid]) AS [SumOfTotal Amount Paid]
FROM Query1
GROUP BY Query1.[Cust ID]
ORDER BY Sum(Query1.[Total Amount Paid]) DESC;

You can not use a parameter for the TOP NN.

You can use VBA to construct the SQL statement and stuff
that into a form/report record source when it opens.

Dim strSQL As String
strSQL = "SELECT TOP " & Forms!theform.thecombo & _
" PERCENT [Cust ID], Sum([Total Amount Paid]) " _
& "AS [SumOfTotal Amount Paid] " & _
"FROM Query1 " & _
"GROUP BY [Cust ID] " & _
"ORDER BY Sum([Total Amount Paid]) DESC
Me.RecordSource = strSQL