#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    33
    Rep Power
    12

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


    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
  2. #2
  3. Just another guy
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2003
    Location
    Wisconsin
    Posts
    2,953
    Rep Power
    262
    Most profitable-
    SELECT Top 20 TotalAmount, CustomerName, CustomerCity FROM Database1 ORDER BY Sales DESC
    Might have trouble with negative values, not sure.
    For least profitable, change DESC to ASC

    To then put these into your list boxes, you need to make a data environment, with 2 commands, which are the 2 queries. Then bind the list boxes to the commands
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    33
    Rep Power
    12
    karsh44:

    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?
  6. #4
  7. Just another guy
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2003
    Location
    Wisconsin
    Posts
    2,953
    Rep Power
    262
    No. As the query is now, it will simply sort all the orders in decending and ascending order, no summing included. I don't use Access much, I think there's a wizard or something that might do that automatically.
    --Dave--

    U2kgSG9jIExlZ2VyZSBTY2lzLCBOaW1pdW0gRXJ1ZGl0aW9uaXMgSGFiZXM=

    My hobby: collecting US coins

IMN logo majestic logo threadwatch logo seochat tools logo