[AC-2003] Requête sur elle même (récursive, dépendant de l'enregistrement précédent) [Résolu]

[thomas.m]

Bonjour à tous,

Après quelques recherches, je n'ai toujours pas trouvé réponse à mon problème. Tout d'abord, je ne pense pas qu'il s'agisse d'une requête récursive, quoi que son fonctionnement m'y fait penser ...

J'ai une requête qui me donne des enregistrements de la sorte:

REQ:

Code :

1
2
3
4
5
6
7
 
ID_Usine	Semaine		Entrée	Conso
Usine1		"2011/09"	4000	900
Usine1		"2011/10"	0	1200
Usine1		"2011/11"	0	1150
Usine1		"2011/12"	0	1200
Usine1		"2011/13"	3500	1455

Je voudrais ainsi savoir ce qu'il me reste comme quantité à la fin de chaque semaine, selon la formule suivante : Reste = Entrée - Conso + SemainePrécédente.Reste

Je voudrais donc faire une jointure (externe) de la requête sur elle même, en rajoutant un champ SemainePrécédente, avec comme critère

Code :

ON(T1.ID_Usine = T2.ID_Usine AND T1.SemainePrécédente = T2.Semaine)

Et ensuite faire Reste=T1.Entrée-T1.Conso+T2.Reste
Mais Access n'aime pas ... "référence circulaire" bla bla bla, même avec un IIf pour mettre à zéro le reste précédent s'il est nul (pour la première semaine).

Je sais qu'il est aussi possible de trouver ce reste en faisant une somme cumulée du style:

Code :

1
2
3
4
Reste =
SOMDOM("[Entrée]", "[REQ]", "[ID_Usine]=' " & ID_Usine & " ' AND [Semaine]<' " & Semaine & " ' ")
-
SOMDOM("[Conso]", "[REQ]", "[ID_Usine]=' " & ID_Usine & " ' AND [Semaine]<' " & Semaine & " ' ")

Ce qui revient à sommer toutes les entrées et consos des semaines antérieures.

Cependant cette dernière solution est beauuuuucoup trop longue à s'executer, car je dispose de milliers d'enregistrements dans ma dernière requête, qui est déjà composée de 4 à 5 sous requêtes traitant des tables contenant plusieurs dizaines de milliers d'enregistrements ...

Merci d'avance pour votre aide

[Aegnor]

Bonjour,

je rebondis sur cette phrase :

Envoyé par thomas.m

Je voudrais ainsi savoir ce qu'il me reste comme quantité à la fin de chaque semaine, selon la formule suivante : Reste = Entrée - Conso + SemainePrécédente.Reste

Celà ne reviendrait-il pas à dire : Reste = Somme des Entrées - Somme des Consos ?

Si c'est bien le cas, la requête est ensuite facile à faire.

Cordialement,

[thomas.m]

Envoyé par Aegnor

Celà ne reviendrait-il pas à dire : Reste = Somme des Entrées - Somme des Consos ?

Envoyé par thomas.m

Je sais qu'il est aussi possible de trouver ce reste en faisant une somme cumulée du style:

Code :

Ce qui revient à sommer toutes les entrées et consos des semaines antérieures.

Bonjour,

C'est bien ce que je disais, mais en effet, c'est facile mais lourd ... refaire la somme pour chaque enregistrement, ce n'est pas du tout efficient, et je ne peux pas me le permettre. A moins qu'il y ait une manière plus simple et efficace de construire la requête.

[User]

Salut,

Je ne vois pas d'autre solution, sauf à essayer ceci :

Code sql :

1
2
SELECT Req.ID_Usine, Req.Entrée, Req.Conso, [Entrée]-[Conso]+nz((SELECT (sum([Entrée]) - sum([Conso])) AS rest FROM Req AS r1 WHERE r1.[ID_Usine]=Req.[ID_Usine] AND r1.[Semaine]<Req.[Semaine]),0) AS Reste
FROM Req;

A+

[thomas.m]

Merci User, ça marche et c'est vraiment 1000 fois plus rapide que la fonction Somdom que je n'utiliserai plus jamais ...

Mais bon par contre ça met toujours 14 minutes pour m'afficher 557 restes ...
Pourtant ma requête "REQ" (qui est composée pas mal de choses déjà) ne met que 2 à 3 secondes à s'ouvrir ... (ce qui est déjà beaucoup certes)
Etant donné que je vais devoir encore effectuer beaucoup de requêtes sur cette dernière requête, il est intéressant d'enregistrer le résultat dans une table non? Tant pis la redondance d'informations?
Access ne garde t il pas en mémoire tampon les résultats des requêtes pour ne pas avoir à tout recalculer à chaque fois ?

EDIT: en sauvegardant le résultat de ma première requête "REQ" dans une table, avec une jolie clé multichamp, le calcul du reste est instantanné .. je pense que je vais dire au diable le principe de redondance des informations dans les tables. Y a t il des règles à propos de ça ?

[User]

En effet,

Il arrive dans certain cas que la création d'une table temporaire (par exemple) soit indispensable niveau performance, mais as-tu essayé de le faire en 1 seule requête sans passer par la requête intermédiaire "Req" ?

Regardes aussi si tu ne peux pas mettre des index sur certains champs pour optimiser les temps d'exécutions...

A+

[thomas.m]

Toutes mes tables disposent de Clés Multichamps, et toutes les clés étrangères sont aussi indexées. Quasiment toutes mes relations sont faite avec l'intégrité référentielle. Tout est sensé être bien optimisé.

Effectuer la requête sans passer par "REQ" ? Ma requête "REQ" est composée de différentes jointures de requêtes, elles même composées de jointures de requêtes .. ça fait beaucoup. Je viens d'essayer, et ça met autant de temps.

Je vais donc passer par une table temporaire que je vais créer proprement en VBA, ça me parait inévitable.

Merci pour ton aide en tout cas.

[thomas.m]

Bonjour,

Dans le même thème, je me heurte à un nouveau problème ...

Je voudrais effectuer un calcul de la sorte:



Style un peu gestion des stocks (mais différent), avec chaque semaine, une livraison, une entrée qui correspond au reste de la semaine précédente, une consommation Prévision = entrée * un certain pourcentage, et ma sortie qui est l'entrée - prévision.
Dans le cas précédent, la consommation était constante et il suffisant de faire la somme des livraisons précédentes - la somme des consos précédentes.

Or dans mon cas, ce n'est pas possible comme ça. J'ai même essayé en rajoutant un champ "% cumulé" qui est le produit des % précédents, mais dès qu'une nouvelle livraison arrive, cela foire tout ( normal ! ).
Et lorsque je fais une requête du type

Code :

1
2
 
DoCmd.RunSQL "UPDATE [Ma_Table] SET Entree=SortiePrecedente(Modèle,SemainePlan)+Livraison"

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
Public Function SortiePrecedente(Modèle As String, Semaine As String) As Double
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    On Error GoTo err
    Set db = CurrentDb
 
    strSQL = "SELECT [Ma_Table].Entree*[Ma_Table].% FROM [Ma_Table] " _
        & "WHERE [Ma_Table].Modèle='" & Modèle & "' AND " _
        & "[Ma_Table].SemainePlan<'" & Semaine & "';"
    Set rst = db.OpenRecordset(strSQL)
 
    If rst.EOF Then
        SortiePrecedente = 0
        Exit Function
    Else
        rst.MoveLast
        SortiePrecedente = rst.Fields(0)        
    End If
 
    rst.Close
    Set rst = Nothing
 
err:
   MsgBox (err.Description)
End Function

Mais là j'ai le droit à un joli "Erreur 94 : Mauvaise utilisation de null".
Même en faisant des tests "If(Not isnull( ... ) ) je me récupère cette erreur la ligne suivante en vouant récupérer la valeur du Fields(0).
A mon avis, cela vient du fait que je fasse la requête sur le même Champ (je veux mettre à jour le champ Entrée en fonction du champ Entrée précédent), et que la requête "UPDATE ..." ne met à jour les champs en une seule fois à la fin ...

Merci d'avance pour votre aide

[thomas.m]

Finalement j'ai trouvé. Il suffisait de refaire les étapes de calcul une par une sans chercher à repasser par la valeur du même champ précédent:

Code :

DoCmd.RunSQL "UPDATE [Ma_Table] SET Entree=SortiePrecedente(Modèle,SemainePlan)+Livraison

Et

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
Public Function SortiePrecedente(Modèle As String, Semaine As String) As Double
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim resultat As Double
 
    resultat = 0
    On Error GoTo err
    Set db = CurrentDb
    strSQL = "SELECT [MaTable].Livraison, [MaTable].%FROM [MaTable] " _
                   & "WHERE [MaTable].Modèle='" & Modèle & " ' " _
                   & "AND [MaTable].SemainePlan<'" & Semaine & "';"
    Set rst = db.OpenRecordset(strSQL)
    If rst.EOF Then
        SortiePrecedente = 0
        Exit Function
    Else
        rst.MoveFirst
        While Not rst.EOF
            resultat = (resultat + rst.Fields(0)) * (1 - rst.Fields(1)) 'calcul. toute la subtilité était là
            rst.MoveNext
        Wend
    End If
    SortiePrecedente = resultat
    rst.Close
    Set rst = Nothing     
err:
   MsgBox (err.Description)
End Function