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;
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