Discussion :

[Thi0123]

Bonjour,

Je travaille sur un programme de gestion de projet sous access et sql server.

J'ai 5 tables :

- une table employé contenant les noms et infos des employés
- une table grade contenant les noms des grades
- une table projet contenant les différents projets
- une table affectation_grade permettant d'affecter un grade à un employé
- une table affectation_projet permettant d'affecter un projet à un employé

A partir de ces tables je dois générer une requête m'affichant pour chaque projet et pour chaque grade, le nombre de personne affectée sur le projet et sur ce grade.

Le problème est que les colonnes des grades ne sont pas fixes, si jamais j'ajoute un nouveau grade, il faut que mon tableau ajoute automatiquement la colonne. Pareil pour les projets, si j'ai 10 projets j'aurai donc plus de lignes.



J'avais commencé à créer une table temporaire avec du code vba pour me créer la table dynamiquement puis insérer les données avec autant de requete select count qu'il y a de grade et de projet mais on m'a demandé d'abandonner cette solution ET d'utiliser des vues à la place.

Est ce que c'est possible de faire ça uniquement avec des vues?
Merci d'avance pour votre aide

[Antoun]

Access te permet de faire une requête de type "Analyse croisée". Si tu y places tes grades en Colonnes, tes projets en Lignes et un Compte d'employés en Valeurs, tu as ta requête, moins les ligne et colonne de total.

PS : pour la colonne de total, tu fais une requête à part, faisant les totaux par projet, et tu la jointures ensuite avec la requête analyse croisée.

Pour la ligne de total, tu fais à nouveau une requête analyse croisée, sans rien en Lignes, et tu fais une Union avec le paquet ci-dessus.

[Antoun]

Voici maintenant la solution en pur SQL (pour faire une vue dans SQL Server). La contrainte est que tu dois connaître à l'avance la liste de tes colonnes ; autrement dit, si un nouveau grade apparaît, il faudra que tu modifies ta vue.

Code :

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

1
2
3
4
5
6
7
SELECT Projet,
  SUM(CASE WHEN IDgrade = 1 THEN 1 ELSE 0 END) AS Grade1,
  SUM(CASE WHEN IDgrade = 2 THEN 1 ELSE 0 END) AS Grade2,
  SUM(CASE WHEN IDgrade = 3 THEN 1 ELSE 0 END) AS Grade3,
  COUNT(*) AS Total
FROM...
GROUP BY Projet

Pour la ligne de total, tu peux faire un ROLLUP si ta version de SQL Server le permet ; sinon, tu ajoutes une seconde requête en Union :

Code :

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

1
2
3
4
5
6
7
8
9
...
UNION ALL
SELECT 'Total projets',
  SUM(CASE WHEN IDgrade = 1 THEN 1 ELSE 0 END) AS Grade1,
  SUM(CASE WHEN IDgrade = 2 THEN 1 ELSE 0 END) AS Grade2,
  SUM(CASE WHEN IDgrade = 3 THEN 1 ELSE 0 END) AS Grade3,
  COUNT(*) AS Total
FROM...
-- pas de GROUP BY

[Thi0123]

Un grand merci pour avoir regardé
Pour la première solution je ne peux pas l'utiliser vu que je suis connecté à sql serveur. Ca aurait pu marcher si la base était dans access directement mais la c'est pas possible, il ne me propose pas l'option
Pour la deuxième solution je peux pas me permettre de faire ça, ça a l'air pas mal mais il faut que la vue se mette à jour toute seule lorsque je rajoute des grades.

[Antoun]

Un grand merci pour avoir regardé
Pour la première solution je ne peux pas l'utiliser vu que je suis connecté à sql serveur. Ca aurait pu marcher si la base était dans access directement mais la c'est pas possible, il ne me propose pas l'option

Normalement il devrait te le proposer quand même (dans l'écran de requête, Menu Requête / Analyse croisée).

Pour la deuxième solution je peux pas me permettre de faire ça, ça a l'air pas mal mais il faut que la vue se mette à jour toute seule lorsque je rajoute des grades.

Dans ce cas c'est la même solution, mais il te faut une proc stock T-SQL qui lise la table des grades et rédige dynamiquement la requête ci-dessus.

[Thi0123]

J'ai pas trouvé l'option, il me propose plein de choses comme créer une vue ou une procédure stockée ou une fonction mais pas de requête croisée comme avant.

Pour l'histoire de la procédure stockée, je fais une fonction qui me retourne ma liste des champs dans un curseur et dans une autre procédure je crée la requête en bouclant sur la première?

[Antoun]

- pour Access : c'est parce que tu dois être dans un .mdp ; si tu fais un bête .mdb (avec des tables liées vers SQL Server), tu auras l'analyse croisée (par contre les perfs seront dégueulasses)

- en général : n'importe quel outil de reporting te fais un tableau croisé en 5 minutes... avec SQL Server, tu as sans doute Reporting Services ?

- pour SQL Server : un seul curseur/boucle suffit (c'est déjà trop*)

en gros, ça donne ça :

Code :

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

1
2
3
4
5
6
7
8
 
@sql = 'SELECT Projet'
curseur ramenant @IDgrade et @LibGrade
boucle sur ton curseur
  @sql = @sql + ', SUM(CASE WHEN IDgrade =' + @IDgrade + ' THEN 1 ELSE 0 END) AS ' + @LibGrade
retour boucle
@sql = @sql + 'reste de la requête'
EXEC @sql

[Antoun]

* L'astuce 2 sur http://sqlpro.developpez.com/cours/s...r_avoidCursor/ te permet même d'éviter ce curseur-là

[Thi0123]

Merci beaucoup c'est vraiment sympa de m'aider!
J'ai presque réussi avec ta méthode, j'arrive pour l'instant à lister pour chaque projet et chaque grade le nombre de personne qu'il y a dessus Il me manque juste la ligne total mais j'ai pas encore essayé de faire le union ALL... je sens que ça va marcher! J'essayerai ça demain puisque j'ai pas sql server chez moi

[Antoun]

Merci beaucoup c'est vraiment sympa de m'aider!
J'ai presque réussi avec ta méthode, j'arrive pour l'instant à lister pour chaque projet et chaque grade le nombre de personne qu'il y a dessus

ça me fait plaisir de savoir que ça marche, ça fait des années que je connais ce truc-là sans jamais avoir eu l'occasion d'essayer !

Il me manque juste la ligne total mais j'ai pas encore essayé de faire le union ALL...

avant de te lancer dans l'UNION, essaie juste de faire ton GROUP BY comme ça :

Code :

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

GROUP BY Projet WITH ROLLUP

[Thi0123]

ROLLUP fonctionne pour le moment sous Sql server 2005 mais je vais bientot repasser en version 2000, j'espère que ça marchera toujours.

Sinon tu sais s'il est possible d'afficher "Total" à gauche de la ligne qui fait le total de chaque colonne? Car la ça fait bizarre j'ai une colonne total tout à droite pour le total de chaque projet mais une ligne sans libellé qui fait le total de chaque grade

Edit : ça fonctionne avec deux requêtes et le union all

[Antoun]

Sinon tu sais s'il est possible d'afficher "Total" à gauche de la ligne qui fait le total de chaque colonne?

Code :

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

1
2
SELECT COALESCE(Projet, 'Total'), LibGrade1, LibGrade2, ...
FROM (tout le bouzin)

[Thi0123]

C'est impeccable, ça marche super bien maintenant! Ca marchait déja avec la seconde requête mais c'est encore mieux la Je connaissais pas COALESCE, si je comprends bien ça remplace les cases vides par le nom qu'on lui passe entre parenthèse

Merci beaucoup pour ton aide Je pense pas que j'aurai réussi sans toi j'avais passé plusieurs journées à chercher sur google mais sans succès.