How to get the 20 MOST and LEAST profitable customers from an OLEdb into Listboxes 1

[piscis]

Gentlemen:

Could you please let me know the right code for extracting the 20 top most and 20 least profitable customers using the SALES column in Database1 into listbox1 and 2?

On listbox1 I need to show:
20 MOST PROFITABLE CUSTOMERS
TotalAmount / CustomerName / CustomerCity

On listbox2 I need to show:
20 LEAST PROFITABLE CUSTOMERS
TotalAmount / CustomerName / CustomerCity


Thanks

Andy

I’m using:
VB.NET 2003 Standard
Access 2000

[Lunatic3]

Select Top 20 TotalAmount,CustomerName,CustomerCity from mytable Order By TotalAmount Desc

That will give you the top 20 who have highest total amount.
And the following:
Select Top 20 TotalAmount,CustomerName,CustomerCity from mytable Order By TotalAmount ASC
should give you the least.

[piscis]

Lunatic3:

Thank you for you reply:

Question:

Do you think this query will add all the values of all the sales corresponding to a specific customer?

What I mean is most customers have many sales, would the query first Sum all sales for each customer and then select the top 20?

Andy

[Lunatic3]

No, that query does not do it. You should modify it in a way that first sums up the amount (using sum function and grouping) then get the top and least 20 of those sums.

[piscis]

Lunatic3:

Could you give me an idea?

Pease!!!

[Lunatic3]

SELECT DISTINCTROW TOP 20 Sum(TotalAmount) AS [Sum Of Total Amount],CustomerName,CustomerCity FROM mytable
GROUP BY CustomerName ORDER BY Sum(TotalAmount) DESC

[piscis]

Lunatic3:

Thank you very much, I'll give it a try!!

[piscis]

Lunatic3:


I forgot to ask you how would you put the result of this query into the two listboxes?

I only need these fields in the listboxes:

TotalAmount / CustomerName / CustomerCity

Thanks

[Lunatic3]

I am afriad you can not show three fields in the listbox unless you use custom listboxes available. And for one field you may use that query to buld an adapter, then fill a dataset with that adapter and set the datasource, datamember and other related properties of the listbox based on the table in the dataset.