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
|
|||
|
|||
Select N for ranking list
Hi,
I am trying to create a form called TopItems and then a report on the form which will give me the Top N products sold for last week. We have about 300 products. I know how to do the date range in my query but I want to create a text box called Namount on my TopItems form. The user would plug in an amount such as 10 or 25 in the text box and would then click a report button on the same form and get a report of the top 10 or top 25 items sold last week. Can someone help me with this? Thanks, -- Chuck W |
#2
|
|||
|
|||
Select N for ranking list
The only way would be to dynamically modify the SQL of the query.
Dim strSQL As String strSQL = "SELECT TOP " & Me.Namount & " Field1, Field2, ...." CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ChuckW" wrote in message ... Hi, I am trying to create a form called TopItems and then a report on the form which will give me the Top N products sold for last week. We have about 300 products. I know how to do the date range in my query but I want to create a text box called Namount on my TopItems form. The user would plug in an amount such as 10 or 25 in the text box and would then click a report button on the same form and get a report of the top 10 or top 25 items sold last week. Can someone help me with this? Thanks, -- Chuck W |
#3
|
|||
|
|||
Select N for ranking list
Douglas,
I am a bit of a novice and am not sure what to do. I have a query called TopProductsSelectDates1 which is listed below. ------------------------------------------------------------------------------------ SELECT TransactionDetails.ItemRef_FullName, TransactionDetails.SalesDesc, TransactionDetails.Amount, AllDB.TxnDate FROM AllDB INNER JOIN TransactionDetails ON AllDB.TxnID = TransactionDetails.IDKEY WHERE (((AllDB.TxnDate) Between [Forms]![SelectItemSales]![txtStartDate] And [Forms]![SelectItemSales]![txtEndDate])); -------------------------------------------------------------------------------------------- I then have a second query which summarizes the amount and sorts it in descending order. The query runs correctly but give me all 300 items when I really want just the top 10 or the top 50. Here is my second query: SELECT TopProductsSelectDates1.ItemRef_FullName, TopProductsSelectDates1.SalesDesc, Sum(TopProductsSelectDates1.Amount) AS SumOfAmount FROM TopProductsSelectDates1 GROUP BY TopProductsSelectDates1.ItemRef_FullName, TopProductsSelectDates1.SalesDesc ORDER BY Sum(TopProductsSelectDates1.Amount) DESC; -------------------------------------------------------------------------------------- Where would I plug in your code? Thanks, -- Chuck W "Douglas J. Steele" wrote: The only way would be to dynamically modify the SQL of the query. Dim strSQL As String strSQL = "SELECT TOP " & Me.Namount & " Field1, Field2, ...." CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ChuckW" wrote in message ... Hi, I am trying to create a form called TopItems and then a report on the form which will give me the Top N products sold for last week. We have about 300 products. I know how to do the date range in my query but I want to create a text box called Namount on my TopItems form. The user would plug in an amount such as 10 or 25 in the text box and would then click a report button on the same form and get a report of the top 10 or top 25 items sold last week. Can someone help me with this? Thanks, -- Chuck W |
#4
|
|||
|
|||
Select N for ranking list
I'd put it either in the AfterUpdate event of the text box, or add a button
to reset the query, and have that code in the button's Click event (probably the latter). It would also be a good idea to put some error checking in: make sure that what's in the text box is a valid value to use the SQL. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ChuckW" wrote in message ... Douglas, I am a bit of a novice and am not sure what to do. I have a query called TopProductsSelectDates1 which is listed below. ------------------------------------------------------------------------------------ SELECT TransactionDetails.ItemRef_FullName, TransactionDetails.SalesDesc, TransactionDetails.Amount, AllDB.TxnDate FROM AllDB INNER JOIN TransactionDetails ON AllDB.TxnID = TransactionDetails.IDKEY WHERE (((AllDB.TxnDate) Between [Forms]![SelectItemSales]![txtStartDate] And [Forms]![SelectItemSales]![txtEndDate])); -------------------------------------------------------------------------------------------- I then have a second query which summarizes the amount and sorts it in descending order. The query runs correctly but give me all 300 items when I really want just the top 10 or the top 50. Here is my second query: SELECT TopProductsSelectDates1.ItemRef_FullName, TopProductsSelectDates1.SalesDesc, Sum(TopProductsSelectDates1.Amount) AS SumOfAmount FROM TopProductsSelectDates1 GROUP BY TopProductsSelectDates1.ItemRef_FullName, TopProductsSelectDates1.SalesDesc ORDER BY Sum(TopProductsSelectDates1.Amount) DESC; -------------------------------------------------------------------------------------- Where would I plug in your code? Thanks, -- Chuck W "Douglas J. Steele" wrote: The only way would be to dynamically modify the SQL of the query. Dim strSQL As String strSQL = "SELECT TOP " & Me.Namount & " Field1, Field2, ...." CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ChuckW" wrote in message ... Hi, I am trying to create a form called TopItems and then a report on the form which will give me the Top N products sold for last week. We have about 300 products. I know how to do the date range in my query but I want to create a text box called Namount on my TopItems form. The user would plug in an amount such as 10 or 25 in the text box and would then click a report button on the same form and get a report of the top 10 or top 25 items sold last week. Can someone help me with this? Thanks, -- Chuck W |
Thread Tools | |
Display Modes | |
|
|