0

I have this query below. For each product, I'd want the top 2 rows that have the highest Note. Query So for example, for the product 177-R3A-K1-C, it would show only the row with the note 0,837 and the one with the note 0,741.

This is my query code :

SELECT [Lot-Prix-Delai Requête].P_Code, [Lot-Prix-Delai Requête].F_Code, [Lot-Prix-Delai Requête].L_PrixUnitaire, [Lot-Prix-Delai Requête].L_Delais, [Lot-Prix-Delai Requête].L_Taille, [Lot-Prix-Delai Requête].M_Code, [Lot-Prix-Delai Requête].L_Code, Fournisseur.F_Flexibilite, Fournisseur.F_Fiabilite, Fournisseur.F_Collaboration, [F_Flexibilite]*0.058+[F_Fiabilite]*0.129+[F_Collaboration]*0.038 AS [Note]
FROM Fournisseur INNER JOIN
     [Lot-Prix-Delai Requête]
     ON Fournisseur.[F_Code] = [Lot-Prix-Delai Requête].[F_Code]
ORDER BY [F_Flexibilite]*0.058+[F_Fiabilite]*0.129+[F_Collaboration]*0.038 DESC;
June7
  • 19,874
  • 8
  • 24
  • 34
Acces124
  • 1
  • 3
  • Tag your question with the database you are using. – Gordon Linoff Dec 21 '20 at 18:53
  • Does this answer your question? [MS Access Select top n query grouped by multiple fields](https://stackoverflow.com/questions/21059077/ms-access-select-top-n-query-grouped-by-multiple-fields) – June7 Dec 21 '20 at 19:05
  • Also https://stackoverflow.com/questions/910894/access-2002-how-to-group-by-and-top-n-records-in-same-query – June7 Dec 21 '20 at 19:07
  • No it doesn't, because i don't understand how to apply it with my code.. – Acces124 Dec 21 '20 at 19:29
  • Well, the query technique of correlated subquery is appropriate to your stated requirement. There are many examples. Does need a unique identifier field - autonumber should serve. Make an attempt. – June7 Dec 21 '20 at 22:58
  • I've tried for 10 hours and still doesn't work. – Acces124 Dec 21 '20 at 23:24
  • "Doesn't work" means what - error message, wrong result, nothing happens? Edit question to show sample raw data as text tables. What would be unique identifier field(s)? – June7 Dec 24 '20 at 07:04

0 Answers0