If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
User Select Top Value Percentage
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. |
#2
|
|||
|
|||
User Select Top Value Percentage
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. |
#3
|
|||
|
|||
User Select Top Value Percentage
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 "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. |
#4
|
|||
|
|||
User Select Top Value Percentage
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. |
#5
|
|||
|
|||
User Select Top Value Percentage
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. |
#6
|
|||
|
|||
User Select Top Value Percentage
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 Ken Sheridan wrote: 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. |
#7
|
|||
|
|||
User Select Top Value Percentage
OIC. I hope Dave's still with us.
Ken Sheridan Stafford, England "John Spencer" wrote: 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 Ken Sheridan wrote: 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. |
#8
|
|||
|
|||
User Select Top Value Percentage
This is great, thanks Ken and everyone!
I'll let you how it turns out. DaveAP "Ken Sheridan" wrote: 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 "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. |
#9
|
|||
|
|||
User Select Top Value Percentage
That I am.
Still keeping fingers crossed when I do the change. Thanks!! DaveAP "Ken Sheridan" wrote: OIC. I hope Dave's still with us. Ken Sheridan Stafford, England "John Spencer" wrote: 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 Ken Sheridan wrote: 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. |
Thread Tools | |
Display Modes | |
|
|