Select N for ranking list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
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
 
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

(no e-mails, please!)


ChuckW said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top