User Select Top Value Percentage

[DaveAP]

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.

 

[pietlinden]

You either have to open the QBE grid and specify it, or you need to
modify the SQL using VBA and then update the SQL property of the
querydef.

 

[Ken Sheridan]

Instead of using the query name as the form's RecordSource property, use the
SQL statement. Firstly open one of the queries in design view and then
switch to SQL view. Copy the entire SQL statement to the clipboard.

Open the form in design view and:

1. In its properties sheet select the RecordSource property. Delete the
query name from it.

2. Open the form's Open event procedure and enter:

Me.RecordSource = "SELECT TOP 100 PERCENT…….."

i.e. paste in the complete SQL statement of the query between the quotes.

3. Amend the above to

Me.RecordSource = "SELECT TOP " & Me.cboPercent & " PERCENT…….."

It will be more readable if you break up the statement of course, e.g.

Dim strSQL AS String

strSQL = "SELECT TOP " & Me.cboPercent & " PERCENT "
strSQL = strSQL & "TransactionDate, TransactionAmount "
strSQL = strSQL & "FROM Transactions "
strSQL = strSQL & "WHERE YEAR(TransactionDate) = "
strSQL = strSQL & "YEAR(DATE) "
strSQL = strSQL & "ORDER BY TransactionDate DESC"

Me.RecordSource = strSQL

4. Add a combo box, cboPercent, to the form's header or footer.

5. Set the combo box's RowSource Type property to 'Value List' and its
RowSource property to:

100;50;25;5

6. Set its DefaultValue property to whatever percentage you want as the
default for the form when it opens, e.g. 100 to show all rows by default.

7. In the combo box's AfterUpdate event procedure put:

Me.RecordSource = "SELECT TOP " & Me.cboPercent & " PERCENT…….."

i.e. the same as in the form's Open event procedure.

When you select a percentage value from the combo box the form will show
only that top percentage of the total rows, by transaction date (latest
first) in my simplified example above.

Ken Sheridan
Stafford, England

 

[John Spencer]

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

 

[Ken Sheridan]

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

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

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.

 

[John Spencer]

I didn't mean to say that you could use a parameter for Top N. What I meant was

IF you want to use
Select Top N records FROM SomeTable
where N is variable ( a parameter) you can't do that using the above query
structure. You can do something like the following to acheive it.

....

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

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

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

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.

 

[Ken Sheridan]

OIC. I hope Dave's still with us.

Ken Sheridan
Stafford, England

I didn't mean to say that you could use a parameter for Top N. What I meant was

IF you want to use
Select Top N records FROM SomeTable
where N is variable ( a parameter) you can't do that using the above query
structure. You can do something like the following to acheive it.

....

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

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

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.

 

[DaveAP]

This is great, thanks Ken and everyone!

I'll let you how it turns out.



DaveAP

 

[DaveAP]

That I am.
Still keeping fingers crossed when I do the change. Thanks!!


DaveAP

OIC. I hope Dave's still with us.

Ken Sheridan
Stafford, England

I didn't mean to say that you could use a parameter for Top N. What I meant was

IF you want to use
Select Top N records FROM SomeTable
where N is variable ( a parameter) you can't do that using the above query
structure. You can do something like the following to acheive it.

....

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

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

:

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.