Re: User Select Top Value Percentage

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John:

I don't think you can use a variable parameter with the TOP option can you?
Doesn't it have to be a constant?

However, your suggestion of a ranking query gave me an idea of how it can be
done with a percentage parameter without having to rebuild the SQL statement
each time:

SELECT *
FROM Transactions AS T1
WHERE
(SELECT COUNT(*)
FROM Transactions AS T2
WHERE T2.TransactionDate > T1.TransactionDate) <
(SELECT COUNT(*)
FROM Transactions)*([cboPercent]/100)
ORDER BY TransactionDate DESC;

If you are still listening Dave, you don’t have to redesign your existing
query; just base a new query like that above on it, substituting your query
name for Transactions and whatever column you want the results sorted by for
TransactionDate. If the desired sort order is ascending rather than
descending change it to:

SELECT *
FROM Transactions AS T1
WHERE
(SELECT COUNT(*)
FROM Transactions AS T2
WHERE T2.TransactionDate < T1.TransactionDate) <
(SELECT COUNT(*)
FROM Transactions)*([cboPercent]/100)
ORDER BY TransactionDate;

It should be updatable BTW, provided the original query is updatable of
course. Make the new query the RecordSource of the form and in the
AfterUpdate event procedure of the cbopercent control requery the form with:

Me.Requery

Ken Sheridan
Stafford, England

"John Spencer" wrote:

Choice A: Rebuild the query string. Covered by others in this thread.

Choice B:
Select Top N records where N is variable ( a parameter)

One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

DaveAP wrote:
Is there a way to select a different top value or percentage of the queries
results from a form?

We have four different types of auditing based upon the representative's
team number:
- Full Audit (i.e. 100%)
- 50%
- 25%
- 5%

Instead having a separate query running to a separate form (which is what I
have now), could a user select which percentage of the query's result will
display?

Thank you in advance, please be kind as I'm new to this and not using SQL,
just the entry boxes provided from right clicking on the columns.




.

Relevant Pages

  • Re: User Select Top Value Percentage
    ... your suggestion of a ranking query gave me an idea of how it can be done with a percentage parameter without having to rebuild the SQL statement each time: ... FROM Transactions AS T1 ... "John Spencer" wrote: ... One method that can be used is to use a ranking query to assign a number to the records and then use the ranking to return N records. ...
    (microsoft.public.access.queries)
  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • RE: Display queried records with Null values (null recordcount)
    ... "...It might work as a single query with an outer join between the assets ... tables related to Assets and Transactions tables in the query, ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • Re: Design Question - Accounts/Transactions
    ... The indexed view can't return anything about individual transactions. ... reason is that the indexed view contains only one row per account. ... you want an index that will help this query. ... One of the most important thing to get right is the clustered index. ...
    (microsoft.public.sqlserver.programming)
  • Re: User Select Top Value Percentage
    ... "Ken Sheridan" wrote: ... "John Spencer" wrote: ... your suggestion of a ranking query gave me an idea of how it can be ... FROM Transactions AS T1 ...
    (microsoft.public.access.queries)