Discussion :

[regis26]

Bonjour à tous,

Actuellement en stage dans une entreprise j'ai une mission à réaliser sur Access et malgré mes tentatives je n'arrive pas à résoudre mon problème!!

Mon problème est le suivant:

Mon extraction provient d'un logiciel comptable et ne peut se faire que mois par mois c'est à dire qu'on ne peut pas extraire les données annuellement mais seulement mensuellement.

Mon extraction me donne les champs suivants avec des centaines de lignes de données:

[B]Compte ; Libellé ; budget mensuel ; réalisé mensuel ; écart mensuel ; budget cumulé ; réalisé cumulé ; budget annuel

Moi j'avais pensé faire sur Access une table pour chaque mois de l'année, soit 12 tables qui auraient exactement les mêmes champs mais avec des valeurs différentes. Et une table "compte" qui résumerait tous les comptes utilisés.

Mes problèmes sont:
- le nombre de compte est différent selon les mois (de janvier à décembre certains comptes peuvent être créés)
- je ne parviens pas à définir des liens pertinents entre les tables
- les comptes auraient des doublons sur le champ "compte" et "libellé" si on en rajoute pour les années qui suivent

Au final j'aimerais réaliser un suivi budgétaire avec une requête qui me permettrait d'afficher tous les champs dont j'ai précisé les noms et ça pour tous les comptes de l'année mais il ne doit pas y avoir de doublon sur les comptes. S'il y a 190 comptes utilisés dans l'année je ne souhaiterais obtenir dans le résultat de ma requête que 190 lignes avec les champs que je veux afficher.

Pour moi le problème doit venir de ma structure de ma base puisque les tables ont les mêmes informations (les mêmes champs). Si quelqu'un a une structure pertinente à me proposer je suis à l'écoute

Peut-être que ce que je veux obtenir n'est pas possible sur Access.

En espérant avoir été assez clair sur mon problème.
Merci d'avance à ceux qui pourront m'aider sur la question!!

[FreeAccess]

Bonjour,

Si j'ai bien compris ton problème, tu souhaite "regrouper" les résultats budgétaire par compte . . . et cela sans doublons..

Dans ta requête il te suffit de faire un regroupement sur le champ [compte]...

Fais quelques recherches dans les anciens posts

Pour exemple, dans la
Comment éviter doublons dans requête

...et une bonne source de lecture (parmi d'autres):
Créer des requêtes simples

[regis26]

Tu suggères donc de ne faire qu'une seule table regroupant les données de chaque mois en réalisant un regroupement par compte via la clause "group by".
Le problème en procédant ainsi est que la requête me donne tous les comptes mais dupliquer 12 fois pour les 12 mois de l'année. De plus, cette requête n'affichera pas les résultats par mois en colonne mais en ligne d'où ma volonté de faire une table pour chaque mois.

Avec ta proposition le résultat serait

compte ; libellé ; budget mois; realisé mois ; ecat mois ; ...
601 Achat de MP 5 4 1 (pour janvier)
601 Achat de MP 8 7 1 (pour février)

Alors que mois je voudrais

compte ; libellé ; budget mois (Janv) ; Réalisé mois (Jan) ;Ecart mois (Janvier) ;Budget mois (fev); réalisé mois(fev); ecart mois (fev);...
601 ; Achat de MP; 5 ; 4 ; 1 ; 8 ; 7 ; 1; ...

Soit un compte 601 pour toute l'année avec les données voulues par mois

La clause Group by ne fonctionnera pas puisque les champs qui suivent les comptes n'ont pas les mêmes valeurs.

J'ai utilisé dans mon exemple seulement 2 mois (janvier et février) et les données utilisées sont inventées.

[FreeAccess]

Bonjour,

Juste une petite précision: Comment fais tu dans tes tables pour distinguer le mois concerné Sur quel champ

Sinon, pour ce que souhaite réaliser, regarde du côté des requêtes "analyse croisée" ....

Quelques exemples de requêtes

... Et une bonne source de lecture:
Le Langage SQL appliqué à Access

[regis26]

Ce sont les tables elles-même qui identifient le mois puisqu'il y a une table par mois et pour éviter les doublons soit je met une clé primaire type 1; 2; 3 ... pour chaque ligne soit je fais un code qui est "le numéro de compte+le mois+l'année".

J'avais pensé à faire une requête d'analyse croisé mais je ne parviens pas à la réaliser!!

Merci pour ton aide.

[FreeAccess]

Re,

N'étant pas un spécialiste des requêtes, je te propose une solution peut être un peu "bourrine" . . . mais qui fonctionne . . . en tous cas jusqu'à ce qu'un "maître en requête" t'apporte une solution plus simple, voire plus rapide...

1) A partir d'une seule table [T_BUDGET] qui regroupe l'ensemble des données de l'année en cours:

compte;mois_année;libellé;budget mensuel;réalise mensuel mois_année sous la forme Année/mois (2012/1 ; 2012/8 ....)

2) Création d'une première requête [Requête1] Sélection basé sur la table [T_BUDGET]

Code sql :

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

1
2
SELECT DISTINCT T_BUDGET.Compte, T_BUDGET.Libellé, T_BUDGET.mois_année, T_BUDGET.[budget mensuel], T_BUDGET.[realise mensuel]
FROM T_BUDGET;

3) Création d'un première requête Analyse croisée([Rq_1] basée sur requête [Requête1] qui va retourner l'ensemble des données du champ (budget_mensuel) même vide..et cela pour tous les mois.

Code sql :

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

1
2
3
4
5
TRANSFORM Sum(Requête1.[budget mensuel]) AS [SommeDebudget mensuel]
SELECT Requête1.Compte, Requête1.Libellé
FROM Requête1
GROUP BY Requête1.Compte, Requête1.Libellé
PIVOT Mid([mois_année],InStr([mois_année],"/")+1) In (1,2,3,4,5,6,7,8,9,10,11,12);

Explications:

.... Mid([mois_année],InStr([mois_année],"/")+1) ... permets de récupérer la partie droite du champ (mois_année) placée aprés le slash "/"

.... IN (1,2,3,4,5,6,7,8,9,10,11,12) ... Valeurs de la propriété "En-têtes des colonnes", permettant d'avoir l'intégralité des mois d'une année même quand ce champ est vide

4) Tu recopie une fois la requête Rq_1 en la nommant Rq_2..:
Rq_2 valeur = champ (réalisé mensuel)

5) Pour terminer, tu crée une nouvelle requête Analyse croisée [Rq_Budget_Annuel] basée sur Rq_1 et Rq_2

Code sql :

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

1
2
3
SELECT Rq_1.Compte, Rq_2.Libellé, Rq_1.[1], Rq_2.[1], Rq_1.[2], Rq_2.[2], Rq_1.[3], Rq_2.[3], Rq_1.[4], Rq_2.[4], Rq_1.[5], Rq_2.[5], Rq_1.[6], Rq_2.[6], Rq_1.[7], Rq_2.[7], Rq_1.[8], Rq_2.[8], Rq_1.[9], Rq_2.[9], Rq_1.[10], Rq_2.[10], Rq_1.[11], Rq_2.[11], Rq_1.[12], Rq_2.[12]
FROM Rq_1 INNER JOIN Rq_2 ON Rq_1.Compte = Rq_2.Compte
GROUP BY Rq_1.Compte, Rq_2.Libellé, Rq_1.[1], Rq_2.[1], Rq_1.[2], Rq_2.[2], Rq_1.[3], Rq_2.[3], Rq_1.[4], Rq_2.[4], Rq_1.[5], Rq_2.[5], Rq_1.[6], Rq_2.[6], Rq_1.[7], Rq_2.[7], Rq_1.[8], Rq_2.[8], Rq_1.[9], Rq_2.[9], Rq_1.[10], Rq_2.[10], Rq_1.[11], Rq_2.[11], Rq_1.[12], Rq_2.[12];

Ici avec cette requête tu obtiens bien tous champs (budget mensuel) et (réalisé mensuel) pour tous les mois de l'année.....

[Rq_1.[1]] budget mensuel du mois 1 (Janvier)
[Rq_2.[1]] budget réalisé mensuel du mois 1 (Janvier)
....
....
[Rq_1.[12]] budget mensuel du mois 12(Décembre)

Ici, dans toutes les requêtes je n'utilise jamais le champ (écart mensuel) car celui ci peut / doit être calculé "à la demande"....

5) Pour conclure,un état basé sur la requête [Rq_budget_annuel]

C'est dans cet état que tu rajoute un champ "indépendant" permettant l'affichage de l'écart mensuel....


Peut être un peu compliqué . . . mais cela fonctionne correctement..

[FreeAccess]

Pour faire suite à mon précédent post . . .

Avec la solution proposée précédemment, l'utilisation d'une table unique pour sauvegarder l'ensemble des données est "limitée" à une seule année ...
En effet, si l'on saisie des données issue de différentes années, les requêtes vont alors "additionner" tous les budgets pour un même compte ... les requêtes se basant sur la partie droite du champ (mois_année) qui représente le mois en nombre (2011/1 ; 2011/3 ; 2012/1 ; 2012/12)

Solution proposée:

a) création d'un formulaire permettant la sélection d'une année et l'impression de l'état correspondant.
b) légère modification des requêtes pour permettre la prise en compte de l'année.

a) Création du formulaire [F_Edition]
Ici, rien de compliqué, un simple formulaire indépendant avec deux contrôles:

- un zone de liste [lstdate] ayant comme propriété "Contenu" (Regroupement des différentes années de la table [T_BUDGET])

Code sql :

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

1
2
3
SELECT Left([mois_année],InStr([mois_année],"/")-1) AS année
FROM T_BUDGET
GROUP BY Left([mois_année],InStr([mois_année],"/")-1);

- un bouton de contrôle [btnImprime] avec événement "Sur clic"

Code :

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

1
2
3
Private Sub btnImprime_Click()
DoCmd.OpenReport "E_Budget_Annuel", acViewPreview
End Sub

b) Modifications des requêtes

[Requête1] Ajout d'un champ retournant l'année (année)

Code sql :

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

1
2
3
4
SELECT DISTINCT T_BUDGET.Compte, T_BUDGET.Libellé, T_BUDGET.mois_année, T_BUDGET.[budget mensuel], T_BUDGET.[realise mensuel],
 Mid([mois_année],InStr([mois_année],"/")+1) AS mois,
 Left([mois_année],InStr([mois_année],"/")-1) AS année
FROM T_BUDGET;

[Rq_1] Ajout du champ (année) dont le critère de sélection sera basé sur la valeur choisie dans la zone de liste (lstDate) du formulaire (F_Edition)

Code sql :

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

1
2
3
4
5
6
TRANSFORM Sum(Requête1.[budget mensuel]) AS [SommeDebudget mensuel]
SELECT Requête1.Compte, Requête1.Libellé
FROM Requête1
WHERE (((Requête1.année)=[forms]![F_Edition]![lstDate].[value]))
GROUP BY Requête1.Compte, Requête1.Libellé, Requête1.année
PIVOT Mid([mois_année],InStr([mois_année],"/")+1) In (1,2,3,4,5,6,7,8,9,10,11,12);

[Rq_2] Même modification que [Rq_1]

. . . et voilà, c'est tout. Il suffit juste de sélectionner une année du formulaire (F_Edition) et de cliquer sur le bouton (btnImprime) pour avoir l'aperçu de l'état du Budget correspondant....

** NOTA **
Ceci n'est qu'un exemple. A toi d'adapter et surtout améliorer en prenant en compte tous les "bons conseils" que tu trouvera sur ce site, en particulier:

- renommer tes champs en évitant les accents et/ou les espaces...
- éviter de sauvegarder une donnée qui peut être calculée (sauf impératif) . . . comme ton champ (écart mensuel)
- prendre en compte les solutions "bien plus optimisées" que les Maîtres en la matière sauront te prodiguer . . .

[regis26]

Bonjour, j'ai testé ta solution elle me paraît super intéressante!!

J'ai juste un soucis sur les requêtes Rq_1 et Rq_2 qui sont basées sur la requête1.

En effet, dans Rq_1 et Rq_2 j'ai bien la structure que je souhaite avec en ligne les comptes et libellés et en colonne les budget mois et réalisé mois sauf que toutes les cellules sont vides comme si le:

TRANSFORM Sum(Requête1.budgmois) ne fonctionnait pas et pareil pour le realmois. Je ne comprends pas trop.

Dans le résultat de la requête1, tu as bien toutes les données pour tous les comptes avec des doublons sur les comptes? (les comptes sont répliqués autant fois que le nombre de mois soit 12 mois donc 12 fois le compte 601 par exemple?)

[FreeAccess]

Bonjour,

..le résultat de la requête1 . . .toutes les données pour tous les comptes avec des doublons sur les comptes?

Exact, cette requête Sélection retourne l'ensemble des comptes présents dans la table T_BUDGET

Ici, ce qui est important ce sont les résultats du champ mois et année

Compte / Libellé / mois_année / budget mensuel / realise mensuel / mois / année
601 / Achat de MP / 2011/1 / 6 / 5 / 1 / 2011
601 / Achat de Mp / 2011/11 / 5 / 5 / 11 / 2011
601 / Achat de MP / 2011/2 / 5 /4 / 2 / 2011
601 / Achat de MP / 2012/1 / 5 / 4 / 1 / 2012
601 / Achat de MP / 2012/2 / 8 / 7 / 2 / 2012
601 / Achat de MP / 2012/4 / 7 / 5 / 4 / 2012
601 / Achat de MP / 2013/1 / 6 / 5 / 1 / 2013
603 / Entretien / 2011/2 / 4 / 3 / 2 / 2011
603 / Entretien / 2012/1 / 5 / 1 / 1 / 2012
604 / Hors budget / 2012/2 / 2 / 1 / 2 / 2012
605 / Divers / 2013/5 / 8 / 5 / 5 / 2013

. . . et d'ailleurs à ce sujet, puisque le mois et l'année sont déjà extrait à partir de la requête [Requête1], il n'y a plus besoin de le refaire dans [Rq_1] et [Rq_2] . . . ce qui donne le code Sql corrigé ci-joint:

Code sql :

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

1
2
3
4
5
6
TRANSFORM Sum(Requête1.[budget mensuel]) AS [SommeDebudget mensuel]
SELECT Requête1.Compte, Requête1.Libellé
FROM Requête1
WHERE (((Requête1.année)=[forms]![F_Edition]![lstDate].[value]))
GROUP BY Requête1.Compte, Requête1.Libellé, Requête1.année
PIVOT Requête1.mois In (1,2,3,4,5,6,7,8,9,10,11,12);

. . . et dans [Requête1] tu peux également supprimer le champ [mois_année]

Code sql :

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

1
2
3
4
SELECT DISTINCT T_BUDGET.Compte, T_BUDGET.Libellé, T_BUDGET.[budget mensuel], T_BUDGET.[realise mensuel], 
Mid([mois_année],InStr([mois_année],"/")+1) AS mois,
Left([mois_année],InStr([mois_année],"/")-1) AS année
FROM T_BUDGET;

[regis26]

Merci beaucoup tout fonctionne maintenant je te suis vraiment reconnaissant pour ton aide et ta réactivité!! Un grand merci

[FreeAccess]

** Correction des requêtes ** . . .

Ici, il n'est pas besoin de passer par la création d'une requête de sélection [Requête1] pour créer les deux requêtes d'analyse croisée [Rq_1] et [Rq_2].

On peut s'affranchir de cette requête et construire directement les RAC en les modifiant comme suit:

[Rq_1]

Code sql :

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

1
2
3
4
5
6
TRANSFORM Sum(T_BUDGET.[budget mensuel]) AS [SommeDebudget mensuel]
SELECT T_BUDGET.Compte, T_BUDGET.Libellé
FROM T_BUDGET
WHERE (((Left([mois_année],InStr([mois_année],"/")-1))=[forms]![F_Edition]![lstDate].[value]))
GROUP BY T_BUDGET.Compte, T_BUDGET.Libellé, Left([mois_année],InStr([mois_année],"/")-1)
PIVOT Mid([mois_année],InStr([mois_année],"/")+1) In (1,2,3,4,5,6,7,8,9,10,11,12);

[Rq_2]

Code sql :

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

1
2
3
4
5
6
TRANSFORM Sum(T_BUDGET.[realise mensuel]) AS [SommeDerealise mensuel]
SELECT T_BUDGET.Compte, T_BUDGET.Libellé
FROM T_BUDGET
WHERE (((Left([mois_année],InStr([mois_année],"/")-1))=[forms]![F_Edition]![lstDate].[value]))
GROUP BY T_BUDGET.Compte, T_BUDGET.Libellé, Left([mois_année],InStr([mois_année],"/")-1)
PIVOT Mid([mois_année],InStr([mois_année],"/")+1) In (1,2,3,4,5,6,7,8,9,10,11,12);