Discussion :

[Samcool]

Bonjour à tous,

je sèche durement sur une requête et j'ai donc besoin de votre aide!

dans une table production, je rentre des produits fabriqués par leur code CodProduit (code produit) et leur cadence CadReelle.(cadence réelle fabrication).

à présent, j'utilise une requête selection access pour regrouper alors par CodProduit et avoir les cadences classées par ordre decroissant.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT production.CodeProduit, production.CadReelle
FROM production
GROUP BY production.CodeProduit, production.CadReelle
ORDER BY production.CodeProduit, production.CadReelle DESC;

jusque là tout va bien, sauf que ça m'affiche toutes les Cadreelle et que maintenant je voudrais selectionner que les 5 premières ( les plus grandes)
le but final sera de faire une moyenne des 5 plus fortes cadences par produit..

j'ai bien essayé la fonction Top 5, mais là , je n'obtient que les 5 premiers enregistrements de la table et pas les 5 cadences par produit

je pense que ce type de requête peut servir dans de nombreux domaines, ex: les 3 meilleures ventes par pays, les 5 voitures plus économiques par marque..etc...
si vous avez une idée, elle sera bienvenue

merci d'avance pour votre aide.

[philben]

bonjour,

Essayer cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT Production.CodeProduit, 
       Production.CadReelle
FROM Production
WHERE Production.CadReelle In 
      (SELECT TOP 5 CadReelle FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit ORDER BY P.CadReelle DESC)
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

cordialement,

Philippe

[J1]

Bonsoir,

le souci de cette requête, c'est que si un produit a les CadReelles suivantes...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
600
500
400
300
200
200
100

... la requête va renvoyer pour ce produit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
600
500
400
300
200
200

200 sera donc en double et la moyenne que Samcool souhaite faire sera donc faite sur 6 valeurs et non 5.
Pour éviter ce problème, il faudrait que chaque CadReelle ait un identifiant unique dans la table Production, ce qui permettrait de remanier la sous-requête corrélée afin qu'elle ne porte plus sur la CadReelle elle-même mais sur son identifiant unique.

[philben]

bonjour,

200 sera donc en double

Une possibilité pour éviter ceci, on peut utiliser soit <Distinct> soit des <GROUP BY> :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT DISTINCT Production.CodeProduit, Production.CadReelle
FROM Production
WHERE Production.CadReelle In (SELECT TOP 5 CadReelle FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit GROUP BY CadReelle ORDER BY P.CadReelle DESC)
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

En espérant que ça réponde à votre attente.

cordialement,

Philippe

[philben]

re bonjour,

il reste une troisième possibilité :
Vous souhaitez le top 5 en comptant les doublons contrairement à la requête précédente.

Je n'ai pas trouvé de solution directe (possible ?) mais en utilisant une fonction VBA.

Si vous souhaitez afficher aux utilisateurs les valeurs qui servent à calculer les moyennes, pour information par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT Production.CodeProduit, Production.CadReelle, Count(Production.CadReelle) AS Compte_CadReelle
FROM Production
WHERE Production.CadReelle In (SELECT TOP 5 CadReelle FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit  ORDER BY P.CadReelle DESC)
GROUP BY Production.CodeProduit, Production.CadReelle
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

Pour calculer les moyennes avec des doublons, une solution consiste à créér une fonction VBA dans un module standard :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
Public Function MaCadenceMoyenne(ByVal lCodeproduit As Long, ByVal iMax As Integer) As Variant
   On Error GoTo errtag
   Dim oDb As DAO.Database
   Dim oRs As DAO.Recordset
   Dim sSql As String
   Dim i As Integer
   Set oDb = CurrentDb
   sSql = "SELECT TOP 5 CadReelle FROM Production WHERE CodeProduit=" & _
          lCodeproduit & " ORDER BY CadReelle DESC"
   Set oRs = oDb.OpenRecordset(sSql, dbOpenForwardOnly)
   If Not oRs.EOF Then
      Do
         MaCadenceMoyenne = MaCadenceMoyenne + oRs(0)
         i = i + 1
         oRs.MoveNext
      Loop Until oRs.EOF Or i = iMax
      If i Then MaCadenceMoyenne = MaCadenceMoyenne / i
   End If
fin:
   Set oRs = Nothing
   Set oDb = Nothing
   Exit Function
errtag:
   Resume fin
End Function

Puis la requête qui va se servir de cette fonction pour calculer la moyenne de cadence du top 5 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT Production.CodeProduit, 
       MaCadenceMoyenne([CodeProduit],5) AS [Moyenne Top 5]
FROM Production
GROUP BY Production.CodeProduit;

Y'a peut être plus simple !

cordialement,

philippe

[J1]

bonjour,



Une possibilité pour éviter ceci, on peut utiliser soit <Distinct> soit des <GROUP BY> :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT DISTINCT Production.CodeProduit, Production.CadReelle
FROM Production
WHERE Production.CadReelle In (SELECT TOP 5 CadReelle FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit GROUP BY CadReelle ORDER BY P.CadReelle DESC)
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

En espérant que ça réponde à votre attente.

cordialement,

Philippe

Bonsoir,

en faisant un regroupement à ce niveau, on se retrouvera face à un autre problème. Imaginons qu'un produit ait les CadReelles suivantes...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
600
500
400
400
300
200

... le DISTINCT fera que les CadReelles conservées seront :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
600
500
400
300

On perd donc l'un des 400, alors que cette valeur fait bien partie du TOP 5 du produit.

Mais je le répète, il suffit que Samcool ajoute un identifiant unique aux CadReelles de sa table Production pour que la requête puisse être remaniée.

[philben]

re bonjour,

Mais je le répète, il suffit que Samcool ajoute un identifiant unique aux CadReelles de sa table Production pour que la requête puisse être remaniée

j'avais essayé cette solution mais bizarrement j'ai un top 6 qui sort pour un code produit... je ne dois pas faire la bonne requete ...

cordialement,

philippe

[Samcool]

Bonsoir messieurs et un très grand merci pour ce travail!

vous avez vraiment beaucoup de ressources! c'est impressionnant.


la 1ère solution, même si elle présente des doublons, me va bien. ces cadences sont des relevés des meilleurs moments de chaque production, sur un temps donné.
Je cherche à extraire les cinq meilleures cadences pour avoir une idée de ce que l'on sait faire de mieux par produit fabriqué et ne pas me baser sur un maxi simplement fait une fois, ce qui serait trop optimiste.

si il y a plusieurs cadences élevées identiques, et bien cela représente la réalité, ce qui est possible de faire au mieux, donc pas de problème pour moi.

ainsi, je vais déjà tester cette solution.

je vais également tester la 3ème solution, mais là, je l'avoue, il va falloir que je prenne du temps pour comprendre car c'est costaud pour mes petites connaissances informatiques!

et dire qu'au départ, je pensais qu'il y aurait une requête toute faite dans access pour faire ça!

encore une fois BRAVO à tous

[philben]

bonjour,

Pour info, en ajoutant la colonne ID unique la requete retournait plus de 5 valeurs à cause des doublons...
Pour éviter ceci, il faut inclure obligatoirement la colonne ID dans le tri de la sous-requête ainsi le compte est bon.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT Production.CodeProduit,
       Production.CadReelle
FROM Production
WHERE Production.ID In 
      (SELECT TOP 5 ID FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit
       ORDER BY P.id, P.CadReelle DESC)
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

Cette solution annule et remplace la solution n°3 puisque l'on peut obtenir ainsi directement via SQL la moyenne sur TOP 5 valeurs pour chaque produit avec une deuxième requête.

Merci à J1

cordialement,

Philippe

[J1]

bonjour,

Pour info, en ajoutant la colonne ID unique la requete retournait plus de 5 valeurs à cause des doublons...
Pour éviter ceci, il faut inclure obligatoirement la colonne ID dans le tri de la sous-requête ainsi le compte est bon.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT Production.CodeProduit,
       Production.CadReelle
FROM Production
WHERE Production.ID In 
      (SELECT TOP 5 ID FROM Production As P 
       WHERE P.CodeProduit=Production.CodeProduit
       ORDER BY P.id, P.CadReelle DESC)
ORDER BY Production.CodeProduit, Production.CadReelle DESC;

Bonjour,

c'est tout à fait ça, en effet, à ceci près que l'ordre de tri dans la sous-requête doit être...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY P.CadReelle DESC, P.id

... puisque l'idée est de faire un TOP 5 sur la base de P.CadReelle, puis de départager les ex-aequo de la 5e place à l'aide du P.id.

Merci à J1

De rien