[AC-2002] Requête et Calcul multiple [Résolu]

[jerome94]

Bonjour à tous,

Je suis nouveau sur le forum et je tiens à vous féliciter tous pour ce que vous faites pour des personnes comme moi qui peuvent tourner en rond faute d'énormes connaissances

Je vous expose la situation.
Dans mon entreprise nous avons un ERP sur lequel nous avons un certain nombre de paramètres renseignés en transactionnel.
Malgré des guides et des règles métiers il est très fréquent que les utilisateurs y intègrent des erreurs de paramétrages.
Je dispose à ce jour d'une extraction sous format Excel des ces paramètres ERP que j'introduis dans une base Access 2002 qui me permet de faire des VraiFaux en pagaille sur tous les champs pour voir si le paramètres est bon.

J'ai en gros 80 paramètres dans des configurations articles différentes (au nombre de 16).
Sur ces 80 champs je fais du VraiFaux et dans 16 requêtes de listing d'erreurs.
En plus de ces 80 colonnes dans mes requêtes j'ai aussi beaucoup de ligne pour faire afficher sur chaque champs les valeurs comme " ** error ** ".
Existe - t - il un moyen moins lourd et surtout d'exécution plus rapide car c'est d'une lenteur effrayante.

Je me tiens à votre disposition pour d'autres compléments d'infos ou bien pour vous montrer un visuel de ce que j'ai

Merci

Jérôme

Edit 1 : je pense qu'une impression d'écran parlera un peu mieux

[marot_r]

Une petite amélioration immédiate, si tu cherche seulement les champs qui contiennent exactement " ** error ** " remplace tes Commes par des =" ** error ** ".

Si tu cherches une égalité stricte Comme (ou Like) fait juste perdre du temps.

Ensuite peut-etre qu'il serait plus efficace de traiter tes champs par VBA, un enregistrement a la fois.

A+

[jerome94]

Bonsoir marot_r,

Merci pour ta réponse.

Pour faire un recherche de valeur strict avec =" ** error ** cela ne va pas convenir car en faite en résultat de mon contrôle je mets la valeur du champs + ** error ** ([CHAMP] & " ** error **") pour permettre aux utilisateurs de voir ce qu'ils avaient mis en paramètre : d'où l'utilisation du comme.

Ensuite je ne suis pas contre être plus efficace en utilisant le VBA mais le soucis c'est que je me suis jamais lancé là dessus avec Access et je ne sais pas trop comment faire, où mettre le code etc.

Quand je regarde le SQL de ma requête je prends peur aussi elle est sur je ne sais combien de page !!

Dans tous mes champs j'ai des contrôles de ce genre :

Code :

CREER_DA_PONCT0: VraiFaux((nz([Formulaires]![Formulaire_Accueil]![Transitoire];0)=1 Et (([CREER_DA_PONCT]="Y" Et [POS_LOG]="CHARIOT") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG]="AMONT") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG] Comme "MAG*") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG] Comme "BDL") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG]="EXTERNE"))) Ou (nz([Formulaires]![Formulaire_Accueil]![Transitoire];0)=2 Et (([CREER_DA_PONCT]="Y" Et [POS_LOG]="CHARIOT") Ou ([CREER_DA_PONCT]="Y" Et [POS_LOG]="AMONT") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG] Comme "MAG*") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG] Comme "BDL") Ou (([CREER_DA_PONCT]="N" Ou [CREER_DA_PONCT] Est Null) Et [POS_LOG]="EXTERNE")));[CREER_DA_PONCT];[CREER_DA_PONCT] & " **error**")

ou

Code :

POS_LOG0: VraiFaux((([GROUPE]="MRPRM" Ou [GROUPE]="IF100") Et ([POS_LOG]="MAGASIN" Ou [POS_LOG]="BDL" Ou [POS_LOG]="MAG-BDL")) Ou ([GROUPE]="MRPRMD" Et ([POS_LOG]="MAGASIN" Ou [POS_LOG]="BDL"));[POS_LOG];[POS_LOG] & " **error** ")

Je veux bien un petit coup de pousse pour démarrer et ensuite je pars sur les centaines d'autres champs du même genre

NB : si vous avez une petite bible Access et ou VBA pas pour débutant mais pas pour expert non plus, je suis preneur, je l’achèterais et la mettrais toujours dans mon sac ou sur ma table de nuit

[marot_r]

Oui c'est en effet assez complexe comme logique. Je ne suis pas persuadé que de la recréer en VBA améliorait vraiment les choses si ce n'est que cela éviterai peut-être quelques répétitons et serait peut-être plus facile à maintenir.

J'essayerai une autre voie assez rapide à tester si tu n'as pas des milliers et des milliers de lignes : créer une table temporaire à partir du resultat des caculs et ensuite faire une sélection sur cette table intermédiaire.

Procéder en 2 étapes pourrait réduire le temps de calcul.

A+

[jerome94]

Envoyé par marot_r

Oui c'est en effet assez complexe comme logique. Je ne suis pas persuadé que de la recréer en VBA améliorait vraiment les choses si ce n'est que cela éviterai peut-être quelques répétitons et serait peut-être plus facile à maintenir.

Rendre l'outil plus facile à maintenir m'intéresse vraiment. Mais le soucis c'est que je vais vraiment avoir besoin du coup de pouce pour démarrer en VBA.

Envoyé par marot_r

J'essayerai une autre voie assez rapide à tester si tu n'as pas des milliers et des milliers de lignes : créer une table temporaire à partir du resultat des caculs et ensuite faire une sélection sur cette table intermédiaire.

Procéder en 2 étapes pourrait réduire le temps de calcul.

Ceci est envisageable oui déjà j'ai fait un requête intermédiaire pour me réduire le nombre de ligne à contrôler.
Au départ, je prenais la table d'origine et je faisais mes filtres "article" dans ma requête de contrôle. Maitenant je fais les filtres avant (en requête, il faut peut que je crée un table ?) et je contrôle dans une seconde requête.
Du coup là je filtre, puis contrôle et enfin affiche le résultat avec ce que tu me dit.

Pour info ma table de départ peut avoir entre 20000 lignes et 30000 lignes.

[marot_r]

Rendre l'outil plus facile à maintenir m'intéresse vraiment. Mais le soucis c'est que je vais vraiment avoir besoin du coup de pouce pour démarrer en VBA.

Si tu es capable de faire des requêtes aussi complexes :-) tu vas être capable.

Personnellement j'aurai fait une fonction par champ à contrôler. Utiliser des fonction dans une requête peut malheuresement ralentir l'exécution de celle-ci.

Donc supposons que tu veux utiliser une fonction par champ.

D'abors il faut que tu créer un nouveau module. Après tu n'as plus qu'à taper le code de ta ou tes fonctions.

Ensuite voici un exemple de fonction qui s'inspire d'un bout de ton SQL :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public function TesterCREER_DA_PONCT(prmCREER_DA_PONCT as variant, prmTransitoire as variant, prmPOS_LOG) as string
 
    dim result as string
 
    dim transitoire as long
 
    if isNull(prmTransitoire) then 
           transitoire=0
        else
           transitoire=prmTransitoire
    end if
 
    if transitoire=1 and prmCREER_DA_PONCT="Y" and prmPOS_LOG="CHARIOT" then
          result=cstr(prmCREER_DA_PONCT)
        else
          result=cstr(prmCREER_DA_PONCT) & " ## Error ## "
    end if
 
    TesterCREER_DA_PONCT=result
end function

après dans ta requête tu n'as plus qu'à mettre

Code :

CREER_DA_PONCT0 : TesterCREER_DA_PONCT([CREER_DA_PONCT]; [Formulaires]![Formulaire_Accueil]![Transitoire]; [POS_LOG])

et access va appeler ta fonction et afficher le résultat.

Comme tu peux le foir, il est plus facile de représenter des logiques complexes avec du code qu'avec des IIF() imbriqués mais ce n'est pas tellement différent.

Tu dispose dans le langage des instructions suivantes :

Code :

if ... then ... else ... end if

Code :

if ... then ... elseif ...then ... else ... end if

Code :

select case ... case ... case else ... end select

et des oppérateurs classiques :

Code :

and, or, not

un et logique peut être représenter par une cascade If si c'est pratique :

ex : A=True et B=True est équivalent à

Code :

1
2
3
4
5
if A=True then
     if B=True then
         'Faire quelque chose
     end if
end if

un ou logique peut être représenter par une succession de if

ex : A=True ou B=True est équivalent à

Code :

1
2
3
4
5
6
7
if A=True
    'Faire quelque chose
end if
 
if B=true
   'Faire le même quelque chose que pour A
end if

enfin le select case est très utile si tu as plusieurs tests sur une même variable :

Code :

1
2
3
4
5
6
7
8
select case A
   case 1
      'Faire quelque chose quand A=1
   case 2, 3
      'Faire quelque chose quand A=2 ou A=3
   case else
      'Faire quelque chose quand A n'est ni 1, ni 2, ni 3
end select

L'indentation sert à rendre le code plus lisbible pour un humain.

A+

[jerome94]

Merci beaucoup encore une fois.

J'espère que je serais capable de faire ce VBA car je me prends la tête avec ce IIF de fou

Ensuite je vais travailler ce code pour l'enrichir de toutes conditions et je te dirais si cela marche comme je le veux sinon je reviendrais sonner à la porte

En tout cas là je viens de m'apercevoir d'un truc c'est que je suis humain et que je comprends bien mieux ton code que mon IIF

De plus j'ai coupé mon contrôle en 3 et je pense que cela va bien plus vite maintenant (enfin je le verrais mieux quand j'aurais fait toutes mes requêtes)

Donc ça plus le code je vais me régaler

Je ne mets pas le post résolu de suite car si nous avons besoin de revenir sur le même sujet on y comprendrais plus rien

[jerome94]

Bonjour,

J'ai terminé de modifier la structure de la base de données en faisant des requêtes et des tables pour rendre plus véloce la base : c'est déjà ça !
J'ai chronométré j'ai divisé le temps d’exécution par 5 wouha...

Maintenant je commence le code et voilà mon premier soucis
A l'aide d'un If Then Else j'ai voulu faire une condition simple.
Cette condition est si le champ n'est pas vide alors j'affiche le champ et si il n'est pas vide (="" ou est null) j'affiche le champ + " ** error **".

C'est là que ça coince en faite car la fonction CStr renvoie une erreur sur mon champ vide (="" ou est null). Ce qui est normal à priori quand je regarde l'aide de la fonction.
Quelle autre fonction je dois utiliser pour que cela marche du coup ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Function TesterFAMILLE(prmFAMILLE As Variant) As string
 
Dim result As String
 
'Je contrôle que le champ FAMILLE soit jamais vide
If IsNull(prmFAMILLE) Then
          result = CStr(prmFAMILLE) & " ** error **"
      Else
          result = CStr(prmFAMILLE)
End if
 
TesterFAMILLE = result
 
End Function

En fait quand je regarde tous les types de champs que je peux avoir j'ai du Texte, des nombres, des pourcentages, des dates, et quasi à chaque fois je peux avoir soit une valeur, soit un vide soit un vide pas vraiment vide.

Merci de votre aide

Jérôme

[marot_r]

marrant les coincidences je viens d'avoir exactement le même problème avec un de mes clients.

Un null ne peut être converti automatiquement en chaîne de caratère. Il faut donc le faire manuellement. Voici deux solutions possibles :

Code :

1
2
3
4
5
If IsNull(prmFAMILLE) Then
          result = "** error **" 'Le Null n'apportant pas d'information, inutile de le concaténer au résultat
      Else
          result = CStr(prmFAMILLE)
End if

Code :

1
2
3
4
5
If IsNull(prmFAMILLE) Then
          result = "#Null# " & " ** error **" 'Là je veux pouvoir voir les nulls.
      Else
          result = CStr(prmFAMILLE)
End if

Pour mon client j'ai fait :

Code :

1
2
3
4
5
6
7
If IsNull(prmChamp) Then
          result = "#Null#"
      Else
          if prmChanp="" then
              result = "#Blanc#"
          end if
end if

Qui me permet de repérer facilement les Nulls et les chaînes vides.

A+

[marot_r]

Au fait si j'étais toi je remplacerai "** error **" par par exemple "#Error#" pour accélérer légèrement le traitement mais surtout pour faciliter la recherche avec le [ctrl][F] de Access car le * est un caractère générique qui dans une recherche remplace plusieurs caractères (par exemple "A*" va te donner la liste de tous les textes commençant par A) donc il n'est pas évident de dire à Access que le caractère que tu cherches est "*".

Un bon truc pour ce genre de chose c'est de définir une constante globale et une fonction public qui te permettent ensuite de facilement changer le message sans changer le code.

à mettre dans un module

Code :

1
2
3
4
5
global const ALERTE_ERREUR as string="#Erreur#"
 
public function LireAlerteErreur() as string
   LireAlerteErreur=ALERTE_ERREUR
end function

Dans ton code tu mets par exemple :

Code :

Result=ALERTE_ERREUR

dans une requête tu mets

Code :

LireAlerteErreur()

à la place de "** error **"

Attention : L'utilisation de fonction dans une requête peut ralentir celle-ci mais comme cela peut aussi la rendre beaucoup plus lisible ou maintenable le prix en vaut peut être la chandelle. Personnellement c'est mon premier choix, et si j'ai des problèmes de performance, je reviens dessus.

A+

[jerome94]

Merci beaucoup pour ces informations complémentaires.
En effet, malgré la restructuration de la base de données où j'ai gagné du temps en exécution, le fait de rajouter du code je perds de nouveau un peu de temps et semble t il la base de données a tendance à grossir aussi un peu plus

Sinon promis ton client ce n'est pas moi dans ton exemple

Mais il est clair, enfin pour moi, que coté maintenabilité c'est super TOP.
En plus je vais pouvoir mettre un mot de pass dessus et comme ça l'utilisateur ne fera pas joujou avec

Bon je donne une formation ces jours ci donc je ferais les essais d'écriture la nuit pour prendre de l'avance

Pour info, j'ai écris déjà tous les fonctions pour les contrôles simples, il me reste les contrôles imbriqués où il faut un peu de calme sans enfants (idéale dans ma chambre d'hotel cette semaine)

Merci encore

[marot_r]

En plus je vais pouvoir mettre un mot de pass dessus et comme ça l'utilisateur ne fera pas joujou avec

Attention si tu mets un mot de passe assures-toi de le garder à une place où d'autres peuvent le trouver. Je ne connais rien de plus frustrant que de devoir faire la maintenance d'une application et de découvrir que le code est protégé et que personne ne connais le mot de passe ... vraiment pas agréable.

Personnellement en 20 ans de carrière j'ai constaté que les utilisateurs étaient BEAUCOUP plus intressé à faire leurs tâches, grâce à mes super-méga-cools programmes :-), que d'aller jouer dans mon code.

A+

[jerome94]

Oui je suis d'accord avec toi que faire la maintenance ou faire évoluer un outil quand on ne dispose pas du mot de passe cela fait c.....

Mais certain chez nous quand ils voient du code partent en courant mais d'autres s'amusent avec et du coup quand il y a divergence sur les résultats on ne sait plus si c'est le créateur qui s'est planté ou l'apprenti sorcier

Bon je suis encore dans l'écriture de mes différentes fonctions mais du coup c'est de plus en plus long oups.

je vais finir et ensuite je verrais comment je vais agencer tout ceci pour faire la part des choses entre rapidité et code facilement maintenable.

Envoyé par marot_r

à mettre dans un module

Code :

Dans ton code tu mets par exemple :

Code :

Result=ALERTE_ERREUR

dans une requête tu mets

Code :

LireAlerteErreur()

J'ai tenté rapidement de faire ceci mais je n'y suis pas arrivée enfin je n'ai pas gratté plus que ça encore.
J'ai mis dans le module la fonction
J'ai mis dans le critère de sélection de ma requête le LireAlerteErreur()
Mais je ne vois pas où mettre dans mon code le Result = ALERTE_ERREUR ! A la suite de chaque fonction que je teste ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Public Function TesterGAMME(prmGAMME, prmCODE_SOURCE As Variant) As String
 
Dim result As String
 
'Je contrôle que le champ NMCL soit égale à 1 quand article fabriqué (Code source 1)
If prmGAMME = "1" And prmCODE_SOURCE = "1" Then             'Si NMCL égale 1 et code source égale à 1
        result = CStr(prmGAMME)                             'je mets la valeur du champ
    Else                                                    'sinon
        result = CStr(Nz(prmGAMME, "")) & " # Error #"      'je mets la valeur du champ avec " # Error #"
 
'Je contrôle que le champ NMCL soit égale à 0 quand article acheté ou transféré (Code source 2)
If prmGAMME = "0" And prmCODE_SOURCE = "2" Then             'Si NMCL égale 0 et code source égale à 2
        result = CStr(prmGAMME)                             'je mets la valeur du champ
    Else                                                    'sinon
        result = CStr(Nz(prmGAMME, "")) & " # Error #"      'je mets la valeur du champ avec " # Error #"
End If
End If
 
TesterGAMME = result
Result=ALERTE_ERREUR
 
End Function

[marot_r]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Function TesterGAMME(prmGAMME, prmCODE_SOURCE As Variant) As String
 
Dim result As String
 
'Je contrôle que le champ NMCL soit égale à 1 quand article fabriqué (Code source 1)
If prmGAMME = "1" And prmCODE_SOURCE = "1" Then             'Si NMCL égale 1 et code source égale à 1
        result = CStr(prmGAMME)                             'je mets la valeur du champ
    Else                                                    'sinon
        result = CStr(Nz(prmGAMME, "")) & ALERTE_ERREUR      'je mets la valeur du champ avec " # Error #"
 
'Je contrôle que le champ NMCL soit égale à 0 quand article acheté ou transféré (Code source 2)
     If prmGAMME = "0" And prmCODE_SOURCE = "2" Then             'Si NMCL égale 0 et code source égale à 2
           result = CStr(prmGAMME)                             'je mets la valeur du champ
        Else                                                    'sinon
            result = CStr(Nz(prmGAMME, "")) & ALERTE_ERREUR      'je mets la valeur du champ avec " # Error #"
    End If
End If
 
TesterGAMME = result
 
End Function

[marot_r]

Un truc qui me choque dans ton code mais qui est peut être correct :

Tes 2 tests s'enchaîne donc c'est un peu comme un ET logique mais le premier cas faut dit que c'est un erreur et le second cas vrai dit que c'est correct.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
If prmGAMME = "1" And prmCODE_SOURCE = "1" Then             'Si NMCL égale 1 et code source égale à 1
        result = CStr(prmGAMME)                             'je mets la valeur du champ
    Else                                                    'sinon
        result = CStr(Nz(prmGAMME, "")) & ALERTE_ERREUR      'je mets la valeur du champ avec " # Error #"
        'Ici c'est un erreur
'Je contrôle que le champ NMCL soit égale à 0 quand article acheté ou transféré (Code source 2)
     If prmGAMME = "0" And prmCODE_SOURCE = "2" Then             'Si NMCL égale 0 et code source égale à 2
           result = CStr(prmGAMME)                             'je mets la valeur du champ
           'ici ce n'est plus une erreur, est-ce normal ?
        Else                                                    'sinon
            result = CStr(Nz(prmGAMME, "")) & ALERTE_ERREUR      'je mets la valeur du champ avec " # Error #"
    End If
End If

Je me serai attendu à quelque chose comme cela :

Code :

1
2
3
4
5
6
7
If (prmGAMME = "1" And prmCODE_SOURCE = "1") _
   or (prmGAMME = "0" And prmCODE_SOURCE = "2") _
     then
        result = CStr(prmGAMME)
     else
        result = CStr(Nz(prmGAMME, "")) & ALERTE_ERREUR
end if

Autre remarque : prend garde à NZ() car parfois il fait sauter les décimales des nombres réels. Pour un entier ou une chaîne, c'est par contre super !.

A+

[jerome94]

En fait le vrai test en français c'est :
Si j'ai le champ GAMME avec la valeur 1 et le code source à 1 alors c'est OK
ou si j'ai le champ GAMME à la valeur 0 et le code source source à 2 alors c'est OK
les autres combinaisons ne sont pas bonne ou bien n'existe pas dans la pratique.

Ce que tu as réécris me semble pas si mal et bien plus court en fait.

Ok je vois ou je mets l'ALERT (cela ne m'étais pas venu encore à l'esprit )

Sinon pour la fonction NZ c'est le seul moyen que j'ai trouvé rapidement.
Ensuite dans tous mes possibilités de champs je n'ai que des nombres qui sont entier ou bien des nombres qui sont du texte. Donc comme tu me le dis cela devrait être ok.

Sinon je me penche sur un autre truc en même temps ce soir c'est le lancement de requêtes de création de table après les avoir supprimées.
Ce ne sont pas de requêtes de mise à jour des enregistrement mais bien je supprime la table et la refait avec de nouvelles valeurs.

Si tu as un truc plus rapide que ma recherche je suis preneur

Merci encore

[jerome94]

Je viens de trouver la solution par l'objet Macro et en lancer via mon code DoCmd.RunMacro "M_maj_Table_liste_article"

[marot_r]

Pour remplacer

Code :

NZ(TaVariable, 0)

tu peux utiliser

Code :

iif(isnull(TaVariable),0,TaVariable)

.

Pour executer une requête à partir du code tu peux faire aussi comme ceci.

Code :

1
2
3
docmd.setWarning false 'Supprime les messages d'alerte de manipulation de données de Access
docmd.openQuery "NomTaRequete"
docmd.setwarning true 'Rétabli les messages

ou

Code :

1
2
3
dim db as dao.database:set db=currentdb
dq.querydefs("NomTaRequete").execute
set db=nothing

A+

[jerome94]

Ok merci de ces infos complémentaires.

Sinon je suis confronté là à une augmentation de la taille de ma base et en faisant manuellement l'opération de compactage c'est OK mais existe un moyen de faire ce compactage en automatique à la fermerture de la base par un bouton quitté ?

Merci par avance

[Beub']

Bonjour,

Pour compacter automatiquement à la fermeture :
- Menu Outils/Options
- Onglet Général
- Compacter lors de la fermeture (case à cocher).

Est-ce bien ce que tu cherchais ?

Dans tous les cas, regarde de ce côté là, tu devrais trouver tout ce qu'il te faut
http://access.developpez.com/faq/?page=General#Compact

Cordialement,
Beub'