Discussion :

[oursviking]

Bonsoir,
je voudrais pouvoir faire une sélection qui me permettrait de renvoyer le meilleur élève parmi une classe mais voila ils sont notés par des lettres (A,B,C). Il faut donc que je trouve une requête qui me permette de faire le rapport entre le nombre de A et le nombre de contrôle réalisé par l'élève.
Je ne sais pas trop par quel bout prendre ce problème, il va falloir que j'utilise sans doute la fonction Sum, Count et Max mais je ne vois pas comment les imbriquer.
Par avance merci de vos réponses

[carden752]

bonjour,

je te conseille déjà de bien définir la meilleure moyenne.
En effet est-ce que 3A et 3B et 4C sont meilleurs que 2A et 8B?
Il te faut déja déterminer très clairement cette partie, après je pense qu'utiliser une pondération de tes notes en A = a, B=b, C=c avec c=1, B=b*c et A=a*C=a/b*B. Exemple un a vaut 3b et un b vaut 2c.
Note 3A, 3B, 4C = 3*6 + 3*2 + 4 = 28
Note 2A, 8B = 2*6 + 8*2 = 28

Donc 2A+8B = 3A+3B+4C

Il ne te restera qu'à faire un sum des valeurs pondéré et un count pour avoir le nombre total de notes.

La division sera simple et en extraire la meilleure valeure.

[aieeeuuuuu]

pour pouvoir te répondre il faudrait en effet que tu nous explique la logique que tu veux appliquer.

L'idée de carden752 de ponderer les notes me semble bien
genre :
A = 3
B = 2
C = 1

mais la c'est a toi de décider de la logique de calcul
quand elle sera définie, donnes la nous (avec des exemples) afin qu'on puisse t'aider dans la requete
donnes nous aussi ta version de SGBDR

Attention par contre avec la division sur le COUNT : si un élève n'a aucune note, selon la façon dont tu ecrira la requete, tu pourrai provoquer une division par zero, que tu risque de ne pas detecter pendant tes tests... utilise plutot fonction AVG (moyenne)

[CinePhil]

Quand j'étais à l'école primaire, entre 1969 et 1974, on commençait à noter avec des lettres A B C D E. En réalité, même dans nos petites têtes de gamins, on faisait vite la conversation par rapport à une note sur 10 :
0 < E <= 2
2 < D <= 4
4 < C <= 6
6 < B <= 8
8 < A <= 10

Cette notation par lettre est de la poudre aux yeux !
La preuve en est par le problème que tu poses puisque le but caché est quand même de classer les élèves, malgré ce système de notation !

[witch]

Quand j'étais à l'école primaire, entre 1969 et 1974, on commençait à noter avec des lettres A B C D E. En réalité, même dans nos petites têtes de gamins, on faisait vite la conversation par rapport à une note sur 10 :

Peut être que vous voulez dire la conversion?

[CinePhil]

Rhôôô ! Lapsus scripte ! Chipoteur !

[oursviking]

Merci beaucoup pour vos réponses !
En fait j'ai choisi de choisir 6 pour A, 4 pour la note B, 3 pour la note C, 2 pour la note D et 1 pour la note E .
Et il n'y pas de case vide dans mes résultats donc je peux utiliser la fonction count.
J'ai donc une table élève avec, un numéro des élèves (clé primaire), leur nom, prénom etc..
Et une autre table Résultat, avec numéro élève1(clé primaire) et numéro élève 2, et Note (A,B,C,D,E). J'ai créé une intégrité référentielle entre la table élève et la table Résultat.
Il y 2 colonnes numéro d'élèves car chaque élèves ont travaillé en binôme et il y a eu une note global de groupe. Les élèves ont fait plusieurs travaux mais jamais deux fois avec le même binôme.
Je ne vois pas très bien comment faire pour faire une somme de notes par élève...
Merci encore de vos réponses

[aieeeuuuuu]

quel SGBDR utilises-tu ?

[aieeeuuuuu]

pour obtenir une note pour chaque élève, tu peux faire ceci :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
SELECT *
FROM (
    SELECT eleve1, note
    FROM Resultat
    UNION ALL
    SELECT eleve2, note
    FROM Resultat
) AS Result (eleve, note)

Ensuite, personnellement, je ferai une table Affectation (NoteLettre, noteNum)
qui contiendrait tes correspondance note en lettre/note en chiffre :
A|6
...

Après ca, il ne reste qu'a faire une jointure de la requete ci dessus sur cette table, puis une moyenne en groupant par eleve, et un petit classement pour faire propre, et voila

[oursviking]

J'utilise Access.

Merci aieuuu, avec ta formule j'obtiens les notes par élève, mais comme j'ai plusieurs fois les même élèves il faut que je fasse une somme ? sachant que ce sont des lettres (A,B,C ...) et que je voudrais au final que cela m'affiche que le nom du meilleur élève si possible en une seule requête.
Dans un premier temps il faudrait que je trouve un moyen de convertir les notes lettre en notes chiffrées pour ensuite en faire la somme par numéro d'élève et après utiliser la fonction Max pour l'identifier ?

[aieeeuuuuu]

Désolé, j'ai edité mon post précédent dans le meme temps pour ajouter l'histoire de la table contenant les affectations de note :
Affecation (NoteLettre, NoteNum)

Si tu crée cette table (ce qui te permettra de changer facilement tes affecation par la suite sans toucher a ta requete), ca donne :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT TOP 1 Result.Eleve, SUM(Affectation.NoteNum) AS Somme
FROM (
    SELECT eleve1, note
    FROM Resultat
    UNION ALL
    SELECT eleve2, note
    FROM Resultat
) AS Result (eleve, noteLettre)
INNER JOIN Affecation on Result.noteLettre = Affecation.noteLettre
GROUP BY Result.Eleve
ORDER BY SUM(Affectation.NoteNum) DESC

NB:je n'utilise pas Access, il y aura donc peut etre quelques modifications a apporter a cette requete pour qu'il l'accepte...

[oursviking]

Bonjour, au lieu de créer une table affectation, il ne serait pas plutôt possible d'imbriquer directement une fonction si dans la requête ? Si Note = A c'est 6 sinon si Note =B c'est ...
Ou est-ce trop compliqué ?

[aieeeuuuuu]

oui c'est possible

un truc du style :

Code sql :

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

1
2
3
4
5
6
7
8
 
SELECT Eleve, SUM( CASE note 
    WHEN 'A' THEN 6
    WHEN 'B' THEN 4
    ...
END)
FROM 
...

mais je pense que les performances seront bien meilleures avec une table et une jointure.

Si tu ne veux pas créer de table tu peux aussi utiliser une pseudo table dans ta requete

[Simplifi]

Hello
de plus une table permet d'intervenir à posteriori d'une manière plus simple que le farfouillage dans le VB

[oursviking]

Comment ça une pseudo table ?
J'ai essayé ça pour que cela m'affiche la somme des notes par élèves mais cela ne marche, ce qui ne m'étonne pas d'ailleurs :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Select Top 1 Resultat.Eleve, Sum(Case Note
When 'A' Then 6,
When 'B' Then 4,
When 'C' Then 3,
When 'D' Then 2,
When 'E' Then 1)
From (Select Eleve1, note
From Resultat
Union All
Select Eleve2, note
From Resultat)
AS MeilleurEleve

Je préfère éviter de créer une nouvelle table si possible mais si je ne trouve pas autrement je me rabattrais sur cette idée.
Au final je veux le nom du meilleur élève et lui seul.

[aieeeuuuuu]

je crois qu'Access ne gere pas les constructeurs de ligne valuées donc ca donnera quelque chose dans ce genre :

Code sql :

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
 
SELECT TOP 1 Result.Eleve, SUM(Affectation.NoteNum) AS Somme
FROM (
    SELECT eleve1, note
    FROM Resultat
    UNION ALL
    SELECT eleve2, note
    FROM Resultat
) AS Result (eleve, noteLettre)
INNER JOIN (
    SELECT 'A', 6
UNION ALL SELECT 'B',4
UNION ALL SELECT 'C', 3
...
) AS Affecation(noteLettre, noteNum) ON Result.noteLettre = Affecation.noteLettre
GROUP BY Result.Eleve
ORDER BY SUM(Affectation.NoteNum) DESC

je n'ai pas access sous la main pour voir si il accepte cette syntaxe, mais le principe est la...

[oursviking]

J'ai donc créé une table Affectation avec NoteLettre et NoteNum en attribut et j'ai créer une intégrité référentielle entre la table Resultat et la table Affectation, j'ai ensuite utiliser ta solution :

Code sql :

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
SELECT TOP 1 Result.Eleve, SUM(Affectation.NoteNum) AS Somme
FROM (
    SELECT eleve1, note
    FROM Resultat
    UNION ALL
    SELECT eleve2, note
    FROM Resultat
) AS Result (eleve, noteLettre)
INNER JOIN (
    SELECT 'A', 6
UNION ALL SELECT 'B', 4
UNION ALL SELECT 'C', 3
UNION ALL Select 'D', 2
UNION ALL Select 'E', 1
) AS Affecation(noteLettre, noteNum) ON Result.noteLettre = Affecation.noteLettre
GROUP BY Result.Eleve
ORDER BY SUM(Affectation.NoteNum) DESC

Dans un premier temps access m'a indiqué une erreur dans la clause From que j'ai réglé avec des parenthèses en plus et ensuite il m'a indiqué des problèmes avec Union All et avec Inner Join .... Est ce un problème de syntaxe lié à access ou alors j'ai encore des erreurs ?

[aieeeuuuuu]

Si tu as créé la table, alors utilise la première requete, avec la jointure sur la table affectation...

[Richard_35]

Bonjour à tous,

Je me permets de d'immisser...
Je ne vois pas pourquoi tu ne veux pas passer par la table Affectation, maintes fois proposée :
Structure :
- NoteLettre (clé primaire)
- NoteChiffre

Contenu :

Code :

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

1
2
3
4
5
6
NoteLettre   NoteChiffre
A            6
B            4
C            3
D            2
E            1

==> cette table est crée une fois pour toute.


*** EXISTANT ***

Table Eleve :
- Id_eleve (clé primaire)
- nom

Table Resultat :
- Id_resultat (clé primaire)
- Id_eleve1
- Id_eleve2
- NoteLettre

Relations :
Eleve 1--->N Resultat, via Id_eleve1
Eleve 1--->N Resultat, via Id_eleve2
Resultat 1<--->1 Affectation, via NoteLettre


*** SUGGESTION ***

Requête R1 (via l'assistant, mode graphique) :
Résultat liée à Eleve, via Id_eleve1/Id_eleve ;
Résultat liée à Affectation, via NoteLettre ;
Sélection Id_eleve1 as Id_eleve, nom, NoteChiffre.

Requête R2 (via l'assistant, mode graphique) :
Résultat liée à Eleve, via Id_eleve2/Id_eleve ;
Résultat liée à Affectation, via NoteLettre ;
Sélection Id_eleve2 as Id_eleve, nom, NoteChiffre.

Requête R3 (via l'assistant, mode SQL) :
(SQL de R1)
UNION
(SQL de R2)

Requête R4 (via l'assistant, mode graphique) :
Requête R3 triée comme tu veux, avec les champs que tu veux.

***

Dans cette procédure, il n'y a, pratiquement, que du copier/coller, sans s'occuper de la syntaxe SQL généré par l'assistant.

[oursviking]

Merci de ta réponse Richard, j'ai donc bien créé une table Affectation avec NoteLettre et NoteNum finalement.

Tes explications ont l'air très claires mais je ne me suis jamais servi de l'assistant en mode graphique, je n'y avais jamais prêté attention avant, j'ai toujours appris à effectuer des requêtes en mode création. Mais je vais essayer de comprendre comment ça marche car cela à l'air assez facile.

Encore merci