Select N for ranking list

[ChuckW]

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,

 

[Douglas J. Steele]

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

 

[ChuckW]

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

 

[Douglas J. Steele]

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!)

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

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