Discussion :

[nap75]

Bonjour,

Je bloque sur le problème suivant.

J'ai trois fichier Excel en entrée d'une base Access (donc je suis libre dans ma modélisation).

Un fichier facture, un fichier paiement et un fichier me permettant de lier mon paiement à une facture.

J'ai besoin d'une sortie qui m'indique pour une facture chaque paiement et le reste à payer.

Facture
Fact 1 : 100€
Fact 2 : 150€
Fact 3 : 70€

Paiment :
P10 : 20
P11 : 50
P12 : 30
P20 : 70
P21 : 50
P30 : 40
P31 : 40

Lien :
Fact 1 - P10
Fact 1 - P11
Fact 1 - P12
Fact 2 - P20
Fact 2 - P21
Fact 3 - P30
Fact 3 - P31

Résultat Attendu
Fact 1 100 20 80
Fact 1 100 50 30
Fact 1 100 30 0
Fact 2 150 70 80
Fact 2 150 50 30
Fact 2 150 - 30
Fact 3 70 40 30
Fact 3 70 40 -10

Voilà je crois que j'ai les 3 cas facture payée, facture avec reste à payer et trop perçu (cas rare mais que je ne peux pas négliger on ne sait jamais).

Moi j'arrive bien sure à une situation où je génère les deux premières colonnes mais je ne sais pas dans une même table utilisé la ligne précédente pour calculer la ligne suivante.
Bon après j'ai des cas rare ou un paiement va concerné 2 factures mais ca je pense qu'avec une priorisation genre sur la facture la plus ancienne je vais m'en sortir... c'est vraiment le solde à payer que je ne sais pas gérer.

Avez vous une idée de comment je pourrais procéder, modéliser mon problème.

[micniv]

Bonjour,
Est-ce qu'un paiement peut concerner plusieurs factures ?
Sinon tu peux simplifier ton modèle

[nap75]

Bonjour,

Oui comme je l'indique c'est possible qu'un paiement concerne deux factures mais bon je pourrais trouver une astuce pour traiter ce cas spécifique qui n'est clairement pas le plus fréquent.

Que vois tu comme simplification de modèle... je n'avance pas donc un point de vue extérieur ne pourra m'être que positif.

[f-leb]

bonjour,

le modèle convient si un paiement peut concerner plusieurs factures

Lien :
Fact 1 - P10
Fact 1 - P11
Fact 1 - P12

Résultat Attendu
Fact 1 100 20 80
Fact 1 100 50 30
Fact 1 100 30 0

Pour ça, il faudrait que l'on sache dans quel ordre sont payées les factures, il n'y a pas de numéro de ligne dans une table Access. Tu as peut-être un champ de type [DatePaiement], non ?

[nap75]

J'ai effectivement l'information je n'en ai pas besoin dans ma table résultat mais oui je peux la véhiculer ce n'est pas un soucis.

Pour cela que je disais aussi que si un paiement correspondait à deux factures j'utiliserai effectivement pour l'affectation du paiement la date de la facture la plus ancienne.

Mon souci est bien le côté itératif cad être capable de soustraire du montant de ma facture chaque paiement. Je ne vois pas comment je peux utiliser le total d'une ligne n pour alimenter ma ligne n+1 j'imagine qu'il faudrait un programme vba ce qui n'est pas un problème bien que je ne connaisse pas trop.

Mais dans l'absolu le calcul est assez simple c'est juste ca mise en place que je ne vois pas.

[f-leb]

Avec les assistants sur les différentes tables jointes, commence par une requête intermédiaire (que je nomme R_LignesPaiement) qui ramène les lignes de paiement avec les champs:
NumFacture, MontantFacture, DatePaiement, MontantPaiement

Dessus, tu fais une autre requête avec le champ calculé Solde:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
SELECT R_LignesPaiement.NumFacture,
       R_LignesPaiement.MontantFacture,
       R_LignesPaiement.MontantPaiement,
       R_LignesPaiement.MontantFacture - (SELECT SUM(T.MontantPaiement)
                                          FROM   R_LignesPaiement AS T
                                          WHERE  T.NumFacture = R_LignesPaiement.NumFacture
                                                 AND T.DatePaiement <= R_LignesPaiement.DatePaiement) AS  Solde
FROM   R_LignesPaiement
ORDER  BY R_LignesPaiement.NumFacture,
          R_LignesPaiement.DatePaiement;

Par contre, je ne vois pas bien quand un paiement concerne plusieurs factures

[nap75]

Alors si je comprend bien ta proposition, elle doit me permettre de calculer un solde final effectivement par contre je n'aurais pas de solde intermédiaire, me trompe je?

Je vais déjà essayé celà, merci.

Pour l'histoire du paiement sur 2 factures c'est un client qui fait un paiement qui solde ca dernière facture et qui paye une première traite d'une nouvelle.
C'est effectivement pas le cas le plus fréquent mais encore une fois si je loupe un cas ca va être relou surtout que celui ci arrive qd même.

[nap75]

Bon c'est bien ça j'arrive à avoir une position net mais pas un détail par ligne...

[tee_grandbois]

Bonsoir,
Le solde intermédiaire peut être obtenu en utilisant une fonction :
je suis parti du modèle avec les 3 tables énoncées au début :
Factures : T_FACTURES
champs :Numfacture, MontantFacture
Paiement : R_LignesPaiement
champs Idpaiement,MontantPaiement
Lien : T_LIENFACTURE_PAIEMENT
champs :NumFacture,Idpaiement

Les factures sont identifiées par un numéro unique (NumFacture)
Les lignes de paiement sont identifiées par un numéro unique (Idpaiement)

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
Function mtResteAPayer(pNumFacture As Long, pIdPaiement As Long) As Currency
Dim Rst As Recordset
Dim stSQL As String
Dim cuResteAPayer As Currency
Dim cuTotFacture As Currency
 
stSQL = "SELECT T_FACTURES.NumFacture, R_LignesPaiement.IdPaiement, T_FACTURES.MontantFacture, R_LignesPaiement.MontantPaiement " & _
        "FROM (T_LIENFACTURE_PAIEMENT INNER JOIN R_LignesPaiement ON T_LIENFACTURE_PAIEMENT.IdPaiement = R_LignesPaiement.IdPaiement) INNER JOIN T_FACTURES ON T_LIENFACTURE_PAIEMENT.NumFacture = T_FACTURES.NumFacture  " & _
        "WHERE  T_FACTURES.NumFacture=" & pNumFacture & " AND IdPaiement <= " & pIdPaiement & ";"
 
Set Rst = CurrentDb.OpenRecordset(stSQL)
        While Not Rst.EOF
            cuResteAPayer = cuResteAPayer + Rst("MontantPaiement")  ' Montant du paiement
            cuTotFacture = Rst("MontantFacture")                    ' Montant de la facture
            Rst.MoveNext
        Wend
mtResteAPayer = cuTotFacture - cuResteAPayer
 
Rst.Close
Set Rst = Nothing
 
End Function

Pour l'utiliser dans la requête, il suffit de passer le Numéro de facture et l'identifiant du paiement en paramètre :

Code :

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

mtResteAPayer(T_LIENFACTURE_PAIEMENT.NumFacture;T_LIENFACTURE_PAIEMENT.IdPaiement)

[nap75]

Bonjour,

Merci pour cette piste.

Par contre oui je ne vois pas trop comment intégrer du VBA dans ma base access...

Je vais chercher vois comment je peux faire celà.

[f-leb]

bonsoir,

Bon c'est bien ça j'arrive à avoir une position net mais pas un détail par ligne...

si tu veux davantage d'aide , il va falloir m'expliquer ce que cette phrase signifie

[tee_grandbois]

Bonsoir tout le monde,
f-leb ce que veut nap75 est ce qu'on appelle un solde glissant (pas sol glissant ) c'est le résultat du calcul montant facture - cumul(montants règlements) appliqué à chaque ligne de facture comme le montre la pièce jointe.
nap75 : tu dois créer un module pour insérer le code que je t'ai posté.
Tu pourras ensuite l'utiliser dans tes requêtes comme dans l'exemple qui suit :

Code :

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

 MT_SOLDE:mtResteAPayer([t_factures].[NumFacture];[R_LignesPaiement].[Idpaiement])

[f-leb]

Salut tee_grandbois,

... ce que veut nap75 est ce qu'on appelle un solde glissant (pas sol glissant ) c'est le résultat du calcul montant facture - cumul(montants règlements) appliqué à chaque ligne de facture ...

à moins d'avoir dérapé sur le sol(de) glissant, n'est-ce pas ce que je viens de proposer plus haut ?

Code sql :

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

1
2
3
4
R_LignesPaiement.MontantFacture - (SELECT SUM(T.MontantPaiement)
                                          FROM   R_LignesPaiement AS T
                                          WHERE  T.NumFacture = R_LignesPaiement.NumFacture
                                                 AND T.DatePaiement <= R_LignesPaiement.DatePaiement) AS  Solde

[nap75]

Bonjour,

Alors j'ai créé mon module.

J'appelle la fonction dans Access mais il me retourne :
-Fonction "Calcul_Solde" non définie dans l'expression-

Comprends pas.

Sinon je pense que je vais qd même choisir la version VBA (si j'arrive à le faire) car je vais pouvoir je pense plus facilement ajouter des conditions notamment pour éviter d'avoir le montant négatif du cas 3.

[nap75]

Bon à priori c'est le nommage de ma fonction qui a pas plus...

[nap75]

Donc ca y est ca fonctionne presque.

Par contre au niveau du calcul je comprends pas trop le fonctionnement des variable :
Set Rst = CurrentDb.OpenRecordset(stSQL)
While Not Rst.EOF
cuResteAPayer = cuResteAPayer + Rst("MontantPaiement") ' Montant du paiement
cuTotFacture = Rst("MontantFacture") ' Montant de la facture
Rst.MoveNext
Wend
mtResteAPayer = cuTotFacture - cuResteAPayer

cuResteAPayer et cuTotFacture vont chercher leur valeur où?
En execution pas à pas tout reste à 0 en faite...

[tee_grandbois]

Bonsoir f-leb et nap75,

à moins d'avoir dérapé sur le sol(de) glissant, n'est-ce pas ce que je viens de proposer plus haut ?

Mis à part qu'il est basé sur l'utilisation de la date de paiement, alors que le modèle de données utilise l'identifiant du paiement, ton code est tout à fait ce qu'il faut à nap75. C'est dommage qu'il n'aie pas donné le noms des colonnes cela aurait pu aider à lui fournir le code plus rapidement.

Donc ca y est ca fonctionne presque.

Par contre au niveau du calcul je comprends pas trop le fonctionnement des variable :
Set Rst = CurrentDb.OpenRecordset(stSQL)
While Not Rst.EOF
cuResteAPayer = cuResteAPayer + Rst("MontantPaiement") ' Montant du paiement
cuTotFacture = Rst("MontantFacture") ' Montant de la facture
Rst.MoveNext
Wend
mtResteAPayer = cuTotFacture - cuResteAPayer

cuResteAPayer et cuTotFacture vont chercher leur valeur où?
En execution pas à pas tout reste à 0 en faite...

nap75, quelles sont les variables que tu ne comprends pas ?
stSQL : nom de la requête SQL basée sur les 3 tables et filtrée avec les numéros de facture et de paiement passés en paramètre.
Rst : nom du Recordset (ou jeu d'enregistrements) que l'on construit à partir de la requête créée.
cuResteAPayer : contient le montant cumulé des paiements, chargé avec le ou les enregistrements inférieurs ou égaux au numéro de paiement passé en paramètre (champ MontantPaiement).
cuTotFacture : contient le montant total de la facture (chargé avec le champ MontantFacture)
mtResteAPayer : variable de la fonction renvoyant le montant final calculé (Total facture - cumul des paiements)

As-tu bien remplacé le nom des champs que j'ai utilisé par celui qui est dans tes tables Rst("MontantPaiement") et Rst("MontantFacture") ?

[nap75]

Bonjour,

Encore merci pour ton retour.
J'ai effectivement avancé et ai compris le jeu des varaibles, là j'essaie de comprendre la boucle While Not Wen car des fois je passe dedans et des fois non.

Mais surtout pour le moment il ne me me génère pas un sous total par couple Paiement/Facture mais un même chiffre sur toutes les lignes...
J'essaie d'avancé encore un peu afin de poser des question plus pertinente.

Effectivement j'ai pas donné la nom des colonnes car comme je disais mon modèle peut changer.
Après le VBA me semble une meilleure solution car je vais pouvoir me semble t'il y intégrer plus de conditions, ma problématique étant un piol plus complexe je cherchais à ce que l'on me mette sur la piste pas que l'on fasse mon travail.

En tous cas un grand merci car ca me remet en selle déjà.

[nap75]

Alors voilà moi j'arrive aux deux premier tableau mais je voudrais le troisieme :
Facture Paiement Paiement Montant Solde
124808 1248002 3 138 350,85 3 386 573,00 248 222,15
124808 1248001 1 254,40 3 386 573,00 3 385 318,60
210808 2108001 1 697,76 3 387 043,00 3 385 345,24
210808 2108002 3 124 676,12 3 387 043,00 262 366,88

Facture Paiement Paiement Montant Solde
124808 1248002 3138350,85 3386573 246 967,75
124808 1248001 1254,40352 3386573 246 967,75
210808 2108001 1697,759241 3387043 260 669,12
210808 2108002 3124676,119 3387043 260 669,12

Facture Paiement Paiement Montant Solde
124808 1248002 3138350,85 3386573 248 222,15
124808 1248001 1254,40352 3386573 246 967,75
210808 2108001 1697,759241 3387043 3 385 345,24
210808 2108002 3124676,119 3387043 260 669,12

Je ne sais pas ce qui bloque

[nap75]

En faite qd je regarde le calcul pas à pas à l'intérieur de la boucle j'ai bien le calcul que je veux par contre en restitution il ne m'affiche pas le calcul intermédiaire...