Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: Append top 30 records for each Customer

    Hi, I'm new to this forum, and a complete novice to VBA, although it's something that I'm interested in developing my skills at.

    I would like to process data that I have contained in a table called Customer_Sales. The data is sorted in order of Customer_ID, Order_Value, and then Order_Number.

    I would like to append the data in this table to a table named Customer_Sales_Top_30. This will have the same fields as Customer_Sales.


    The data that I would like to append is the top 30 records for each Customer_ID based on highest Order_Value.

    For example, some customers may have 100 orders, and I would only want to append the top 30 records based on the value of sales. Some customers, however, may have only 5 orders, and therefore I would want to append all records.


    The fields in my table are as follows.


    Customer_ID Order_Value Order_Number


    There are approximately 1000 different Customer_ID's with an average of 40 records per customer.



    Is there a way in which Access or VBA can automatically process these records, as currently I'm resorting to placing a count of records per Customer_ID, and for the ones that exceed 30 records, doing an individual top 30 records query on an individual basis.

    This is obviously quite soul destroying on such a large data set, and the reason I'm trying to add some intelligence to this query.

    Any help would be greatly appreciated.

    Kind Regards

    Kal

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Kal

    To backtrack a bit - why do you want to do this (e.g. to produce a report showing the top 30 customers by sales value)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    Hi Pootle,

    many thanks for your reply. I'm looking to produce a report/table that reflects the data for up to the top 30 orders per customer based on sales value.

    I hope this makes sense.


    Many Thanks

    Kal

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Just create Query that

    This is the way I doit

    1st show all records

    select table .* from table

    (cool I see something)

    2nd Get group by the what every

    select table.* from table GROUP BY table.feildname;

    (cool I see someting)

    3rd get some totals

    select table.*,Sum(feildname) AS SumOffeildname from table GROUP BY table.feildname;

    (cool I see the totals)

    4th Sort in the right order

    select table.*,Sum(feildname) AS SumOffeildname from table GROUP BY table.feildname ORDER BY Sum(feildname) DESC;

    (cool I see it sorted it right)


    5th show me the top ??

    select TOP 30 table.*,Sum(feildname) AS SumOffeildname from table GROUP BY table.feildname ORDER BY Sum(feildname) DESC;

    (cool I see it )

    6th create the report

    (cool I see it )


    7th next job
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You can't use the TOP predicate to get the top 30 for every customer.
    The TOP predicate applies only to the entire recordset. You need to do a self-join on the table. I used my own table to put something together, so you'll have to convert to your table, but here's how it works

    Code:
    SELECT     a.SHIP_TO, a.SHIP_DATE
    FROM         ORDER_HEADER a INNER JOIN
        ORDER_HEADER b ON a.SHIP_TO = b.SHIP_TO 
             AND    a.SHIP_DATE <= b.SHIP_DATE
    GROUP BY a.SHIP_TO, a.SHIP_DATE
    HAVING      (COUNT(*) <= 3)
    ORDER BY a.SHIP_TO, a.SHIP_DATE DESC
    That shows the last 3 shipments to each customer. Make sure your table has good indexes, otherwise this will be ungodly slow.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by myle
    select table.* from table GROUP BY table.feildname;

    (cool I see someting)
    actually, the only way you'd see someting from that query is if "table" has only one column, "feildname"

    otherwise it's a syntax error because of invalid GROUP BY

    (or else you're running it in mysql, which will actually execute it)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2007
    Posts
    3
    Hi All,

    the code from RedNecKGeek works a treat. I have to say that I am truly amazed at the helpfulness of the people who contribute to this forum.

    RedNeckGeek you're a star.

    Many thanks

    Kind Regards

    Kal:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •