Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Unanswered: Top 5 for each date - access query


    I have been racking my brains to figure this out but for the life of me i cannot. That is why i have come here. I hope someone can help.

    I need to get the top 5 results for each date. The query is currently running on a subform but shows way to many results for each day. I need to limit this to five. I have looked at the sql statement to insert SELECT TOP 5 etc but cannot figure it out. I believe my SQL statement currently is a bit complex. That is due to my database being a bit complex. Here is the sql statment i need the select statment to be attached to.

    SELECT [Current MTD Scrap].F1, tbl_dailyperformance.Date, [Current MTD Scrap].F11, [Current MTD Scrap].F12, Sum([F17]+[F18]+[F19]+[F20]) AS [Total Cost], Sum([Current MTD Scrap].F24) AS SumOfF24
    FROM [Current MTD Scrap] INNER JOIN tbl_dailyperformance ON [Current MTD Scrap].F1 = tbl_dailyperformance.Date
    GROUP BY [Current MTD Scrap].F1, tbl_dailyperformance.Date, [Current MTD Scrap].F11, [Current MTD Scrap].F12
    HAVING ((([Current MTD Scrap].F11)<>"23S"))
    ORDER BY Sum([Current MTD Scrap].F24) DESC;

    Hope someone can help

    Thanks in advance


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    No, top N over group is always tricky.

    A few questions:
    Top 5 what for each date? I suspect SUM(F24), but I'm not sure.
    Why bother with the tbl_dailyperformance table? It seems unnecessary, apart from possibly limited the dates.
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009

    The database is quite complex...well rather the database may not have to be complex but i have made it complex. The tbl_dailyperformancetable is where the user inputs all information. I have linked this table to queries. A bit messy but kind of works so bit reluctant to move stuff. The date is current_mtd_scrap.F1 F1 is the date. Its linked all over the place. Below will show basically what i am trying to achieve


    DATE, customer, SCRAP CODE, tool number, works order number, SQFT

    Current_MTD_Scrap: (linked table to excel spreadsheet)

    same + more information as above

    * I need to break down down for each DATE. Top 5 SCRAP CODE with SQFT decsending. I can send the database for you too look but a bit messy. Normally ok at databases but linked databases etc a bit tricky

    Please see attached docs.

    Hopefully it will explain a little easier
    Attached Files Attached Files

Posting Permissions

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