Requête basée sur nouvelle table

**C15nantes** · 05/04/2018, 15h04

Bonjour à tous et à toutes,

Avec l'aide de certains d'entre vous, je suis en train de restructurer une base de façon plus normalisée.

Au lieu d'avoir une T Adhérents + TCotisations + TCotisationsdues + TAG,
j'ai une table T Adhérents et une table T_Cotisation avec les champs [NUM_COTISATION_PK], (T_Adherent_FK], [Cotisation_An] (Année de cotisation), [AG] (Présence à l'AG), [Cotisation] (Cotisation payée ou pas ?), [Cotisation_Du] (Cotisation restant due).

Les 2 tables sont reliées par T Adhérents.N°Adherent <-->T_Adherent_FK.

J'ai le code suivant

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
Public Sub CotiDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, " _
          & "[Du" & Format(NumDebut, "00") & "]+[Du" & Format(NumDebut + 1, "00") & "]" _
          & "+[Du" & Format(NumDebut + 2, "00") & "]+[Du" & Format(NumDebut + 3, "00") & "]" _
          & "+[Du" & Format(NumDebut + 4, "00") & "]" _
             & " AS [Total dû], [TCotisationsdues].Du" & Format(NumDebut, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 1, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 2, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 3, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 4, "00") _
               & " FROM ([T Adhérents] INNER JOIN [TCotisations] " _
              & " ON [T Adhérents].N°Adherent = [TCotisations].N°Adherent) " _
                & " INNER JOIN [TCotisationsdues] ON [T Adhérents].N°Adherent = [TCotisationsdues].N°Adherent " _
          & " WHERE ((([T Adhérents].Adherent) = True)) ORDER BY [T Adhérents].Nom, [T Adhérents].Prenom;"
  Set q = CurrentDb.QueryDefs("R CotiDues")
  q.SQL = sSql
End Sub

qui me génère la requête R CotiDues (voir PJ) à partir d'un formulaire où je saisis la 1ère année.

Je voudrais obtenir la même requête avec les valeurs des cotisations dues de 14 à 18 (comme dans l'exemple joint) et le total de ces sommes.

Merci de votre aide.
A+

C15

**Zekraoui_Jakani** · 05/04/2018, 22h41

J'avoue ne pas comprendre "cotisations dues de 14 à 18"; s'agit-il de la 1ère guerre ? blague à part; peux-tu être plus explicite ?

**C15nantes** · 05/04/2018, 23h12

Bonsoir,

En fait, on veut faire le point des cotisations de l'année en cours et des 4 précédentes (il y a de mauvais payeurs), d'où les cotisations Du14 à Du18 et le total du = Du14+Du15
Du16+Du17+Du18.

L'année prochaine, ce sera Du15 à Du19.

Le problème, c'est que dans la version précédente de ma base (TCotisationdues), j'avais les champs Du83 (année de création de l'association concernée), jusqu'à Du35 (je prévoyais l'avenir pour mon successeur) ; maintenant, dans ma nouvelle structure, je n'ai plus cela, or nous voulons toujours connaitre les cotisations dues par année et par adhérent pour lui adresser, comme actuellement le point de ses cotisations.

Merci de ton aide
A+
C15

**Zekraoui_Jakani** · 06/04/2018, 14h08

Pour connaître les cotisations dues par année et par adhérent, je recommande d'ajouter un champ "Année de paiement" sur lequel vous pouvez effectuer une requête simple affichant le N°adhérent, nom, prénom, année, montant dû, montant payé.
Pour cela tous vos champs intitulés "DuXX" seront inutiles car ils découlent d'une mauvaise conception dès le départ. Si, néanmoins, vous voulez garder ces anciens champs, l'exercice que vous demandez exige un code bien plus élaboré. Mais à quoi bon se compliquer la vie ?

**C15nantes** · 06/04/2018, 17h31

Bonjour,

Ce champ "année de paiement" n'est-ce pas le champ [Cotisation_An] que j'ai déjà dans ma table T_Cotisation dont les champs sont :

[NUM_COTISATION_PK] N° enregistrement de la cotisation,
[T_Adherent_FK] Identifiant adhérent
[Cotisation_An] Année de cotisation
[AG] Case cochée si Présence à l'AG
[Cotisation] Case cochée si Cotisation payée
[Cotisation_Du] Cotisation restant due (=0 si case [Cotisation] cochée).

J'ai essayé de faire la requête, cela ne convient pas car j'ai tout en colonne avec autant de ligne par adhérent qu'il y a d'années.

N°Adherent	Nom	Prénom	Cotisation_An	Cotisation_Du
1	XXXX	Marc	2014	0
1	XXXX	Marc	2015	0
1	XXXX	Marc	2016	0
1	XXXX	Marc	2017	25
2	YYYY	Jean	2014	0
2	YYYY	Jean	2015	0
2	YYYY	Jean	2016	0
2	YYYY	Jean	2017	0

Ce que je voudrais, c'est une ligne par adhérent avec les colonnes suivantes :

N°Adherent	Nom	Prénom	2014	2015	2016	2017
1	XXXX	Marc	0	0	0	25
2	YYYY	Jean	0	0	0	0

Merci de ton aide.
A+

c15

**Zekraoui_Jakani** · 07/04/2018, 12h03

Simple via l'assistant de requête , choisir "analyse croisée" et suivre les instructions sur écran: N°adhérent en ligne et années en colonne; les valeurs à sommer seront les cotisations.

**C15nantes** · 07/04/2018, 13h32

La honte

Je connais pourtant bien les requêtes analyse croisée et cela ne m’était pas venu à l'esprit.
Cela correspond effectivement à ce que je recherche.

Il ne me reste plus qu'à adapter la procédure citée en début de discussion pour déclencher la création de cette requête au travers d'un formulaire.
En effet, la plage des cotisations observées doit glisser d'une année à l'autre (le changement se fait lors de l'assemblée générale quand les adhérents paient la cotisation de l'année à venir).

Merci encore de ton aide.
A+

C15
PS : je garde encore le fil de la discussion ouvert au cas où je coincerais sur l'adaptation de mon code

**tee_grandbois** · 07/04/2018, 14h51

Bonjour c51nantes,
si cela peut t'avancer, la requête devrait ressembler à ça :

Code :

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

1
2
3
4
5
6
7
TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du
SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total
FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK
WHERE (((T_Cotisation.Cotisation_An) Between Year(Now()) And Year(Now())-4) AND (([T Adhérents].Adherent)=True))
GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom
ORDER BY [T Adhérents].Nom, [T Adhérents].Prenom
PIVOT T_Cotisation.Cotisation_An;

Reste à l'adapter pour mettre l'année de référence en paramètre selon ton besoin.

**C15nantes** · 07/04/2018, 17h52

Bonjour,

Merci de cette réponse.
Pour faciliter le travail du trésorier qui gère au quotidien cette appli, j'avais créé, avec l'aide d'un contributeur de ce forum, un formulaire avec une zone de texte txtNombreDepart dans laquelle il saisit, par exemple 14.
A un bouton de commande est associée la procédure événementielle suivante :

Code :

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

1
2
3
4
5
6
7
8
Private Sub Commande3_Click()
  If Me.txtNombreDepart > 0 Then
      Call MAJCotisDues(Me.txtNombreDepart)
      DoCmd.OpenQuery "0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES"
    Else
      MsgBox "Vous saisir une année de départ"
  End If
End Sub

Cette procédure MAJCotisDues doit être l'adaptation de ma procédure ancienne configuration
Pub

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
lic Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, " _
          & "[Du" & Format(NumDebut, "00") & "]+[Du" & Format(NumDebut + 1, "00") & "]" _
          & "+[Du" & Format(NumDebut + 2, "00") & "]+[Du" & Format(NumDebut + 3, "00") & "]" _
          & "+[Du" & Format(NumDebut + 4, "00") & "]" _
             & " AS [Total dû], [TCotisationsdues].Du" & Format(NumDebut, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 1, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 2, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 3, "00") & "," _
               & "[TCotisationsdues].Du" & Format(NumDebut + 4, "00") _
               & " FROM ([T Adhérents] INNER JOIN [TCotisations] " _
              & " ON [T Adhérents].N°Adherent = [TCotisations].N°Adherent) " _
                & " INNER JOIN [TCotisationsdues] ON [T Adhérents].N°Adherent = [TCotisationsdues].N°Adherent " _
          & " WHERE ((([T Adhérents].Adherent) = True)) ORDER BY [T Adhérents].Nom, [T Adhérents].Prenom;"
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.SQL = sSql
End Sub

que j'ai essayé d'adapter, mais là pas bon

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du" _
& "SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total" _
& " FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK" _
& " WHERE (((T_Cotisation.Cotisation_An) Between [Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart] And ([Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart])+4) AND (([T Adhérents].Adherent)=True))"
& "GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& "ORDER BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& "PIVOT T_Cotisation.Cotisation_An;"
 
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.SQL = sSql
End Sub

Monsieur Access me dit qu'il y a erreur de syntaxe, je ne suis pas surpris. Je m'y perds entre les & les espaces les virgules les _ ou les "
Peut-être même y -a-t-il une erreur de raisonnement.
Merci de ton aide.
A+

C15

**tee_grandbois** · 07/04/2018, 18h07

Bonsoir,
il manquait 2 espaces: avant SELECT et GROUP BY).
Il est préférable de mettre les espaces toujours au même endroit, je te conseille de les mettre tous au début (sachant que SQL se fiche qu'il y ait plus d'un à la suite) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du" _
& " SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total" _
& " FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK" _
& " WHERE (((T_Cotisation.Cotisation_An) Between [Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart] And ([Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart])+4) AND (([T Adhérents].Adherent)=True))"
& " GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " ORDER BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " PIVOT T_Cotisation.Cotisation_An;"
 
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.SQL = sSql
End Sub

**C15nantes** · 08/04/2018, 11h12

Bonjour,

J'ai un petit problème

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du" _
& " SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total" _
& " FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK" _
& " WHERE (((T_Cotisation.Cotisation_An) Between [Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart] And ([Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart])+4) AND (([T Adhérents].Adherent)=True))" _
& " GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " ORDER BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " PIVOT T_Cotisation.Cotisation_An; "
 
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.SQL = sSql
End Sub

Access me dit qu'il ne reconnait pas

Code :

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

[Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart]

en tant que nom de champ ou expression correcte.
Cela correspond à la zone de texte de mon formulaire dans laquelle je saisis la 1ère des 5 années qui m'intéressent
Par exemple je saisis 2014 pour avoir les cotisations dues de 2014 à 2018
Peut être y a t il encore un problème de syntaxe dans mon critère Where (encore des parenthèses mal placées ?

Mercide ton aide.
A+

C15

**tee_grandbois** · 08/04/2018, 16h07

Bonjour,
c'est toujours le souci lorsque l'on met des paramètres issus d'un formulaire dans une requête enregistrée car VBA ne les voit pas directement.
Essaie ce code:

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
Public Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du" _
& " SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total" _
& " FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK" _
& " WHERE (((T_Cotisation.Cotisation_An) Between [Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart] And ([Formulaires]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart])+4) AND (([T Adhérents].Adherent)=True))" _
& " GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " ORDER BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom " _
& " PIVOT T_Cotisation.Cotisation_An; "
 
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.parameters(0).Value = [Forms]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart]
  q.parameters(1).Value = [Forms]![0_FORMULAIRE_MAJ_COTIS]![txtNombreDepart]+4
  q.SQL = sSql
End Sub

**C15nantes** · 08/04/2018, 16h44

Bonjour,

Désolé, cela ne marche pas mieux, j'ai le même message d'erreur et le débogueur bloque sur la ligne 13.

Question :
La ligne 1 (issue de ma première version, voir 1er post) ne serait-elle pas à changer ?

Pour info, dans la 1ère version, je saisissais par exemple 14 dans la zone de texte [txtNombreDepart] du formulaire puis déclenchait la procédure via un bouton de commande

Code :

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

1
2
3
4
5
6
7
8
Private Sub Commande3_Click()
  If Me.txtNombreDepart > 0 Then
      Call CotiDues(Me.txtNombreDepart)
      DoCmd.OpenQuery "R CotiDues"
    Else
      MsgBox "Vous saisir une année de départ"
  End If
End Sub

Cela devenait (par quel miracle ?) Numdebut.

Et cela marchait parfaitement, le requête s'ouvrait avec les champs Du14 à Du18

Merci de ton aide.
A+

C15

**tee_grandbois** · 08/04/2018, 18h37

si Me.txtNombreDepart contient l'année de départ et est envoyé en paramètre via NumDebut dans le code appelé, alors il faut l'utiliser comme cela:

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
Public Sub MAJCotisDues(NumDebut As Integer)
  Dim sSql As String
  Dim q As QueryDef
  sSql = "TRANSFORM Sum(T_Cotisation.Cotisation_Du) AS SommeDeCotisation_Du" _
& " SELECT [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom, Sum(T_Cotisation.Cotisation_Du) AS Total" _
& " FROM [T Adhérents] INNER JOIN T_Cotisation ON [T Adhérents].N°Adherent = T_Cotisation.T_Adherent_FK" _
& " WHERE (((T_Cotisation.Cotisation_An) Between " & NumDebut _
& " And " & NumDebut +4 & ") AND (([T Adhérents].Adherent)=True))" _
& " GROUP BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom" _
& " ORDER BY [T Adhérents].N°Adherent, [T Adhérents].Nom, [T Adhérents].Prenom" _
& " PIVOT T_Cotisation.Cotisation_An;"
 
  Set q = CurrentDb.QueryDefs("0_R_COTISATIONS_ANNEE_0_+_4_SUIVANTES")
  q.SQL = sSql
End Sub

**C15nantes** · 09/04/2018, 08h40

Bonjour,

Bingo !

Cela fonctionne

Par contre je ne saisis pas tout à fait comment et pourquoi on passe de txtNombreDepart à NumDebut ; si tu pouvais me l'expliquer en quelques mots, cela me permettra de modifier plus facilement une autre procédure un peu du même genre à laquelle je vais m'attaquer.
En attendant, je considère cette discussion comme efficacement résolue

Merci encore de ton aide.

A+

C15

**tee_grandbois** · 09/04/2018, 14h37

Bonjour,

Par contre je ne saisis pas tout à fait comment et pourquoi on passe de txtNombreDepart à NumDebut

MAJCotisDues(NumDebut As Integer) est une procédure et tout ce qui se trouve entre les parenthèses d'une procédure sont des paramètres, on y définit le nom: NumDebut et le type du paramètre: Integer.
Comme txtNombreDepart contient la valeur du paramètre, l'appel de la procédure ce fera ainsi: call MAJCotisDues(Me.txtNombreDepart) ainsi NumDebut, le nom du paramètre va prendre la valeur de txtNombreDepart pour tout le code exécuté.

Requête basée sur nouvelle table [AC-2007]

VBA Access

Discussions similaires

Partager

Partager