a little confused

  • Thread starter Thread starter mate
  • Start date Start date
M

mate

I have an order table with
fields, "ord_number", "prod_number", "vendor_number", etc.
First i searched by vendor by entering in vendor# criteria
[enter vendor #]. Now i would like it to return the
product numbers for that vendor that have occurred the
most. For example, the five best selling product numbers
for vendor X. is there any way to do this. any help or
direction would be great as always. thanks, mate.
 
Mate,

Make a query based on your table, with the fields added to the query
design grid in reverse order. Make it a Totals Query (select Totals
from the View menu). If you use the query design view, in the Totals
row of the Vendor_number field, leave Group By, and in the Criteria
enter your Parameter prompt. In the Totals row of the Prod_number
field, leave Group By. In the Totals row of the Ord_number field, put
Count, and Sort Descending. And then, in the Top Values combobox on the
toolbar, enter 5. The SQL view of this query will look a bit like this...
SELECT TOP 5 Vendor_number, Prod_number, Count(Ord_Number) AS Prods
FROM Orders
GROUP BY Vendor_number, Prod_number
HAVING Vendor_number=[enter vendor #]
ORDER BY Count(Ord_Number) DESC
 
Back
Top