Discussion :

[Domi2]

Bonsoir,

Je poste à mon tour...

J'ai une requête assez "cossue" que je développe actuellement dans le QBE... Pas très compliquée, mais beaucoup de champs calculés...

Les limites que je connais (32 tables et 255 champs) ne sont pas dépassées...

En ajoutant 2 champs et en modifiant un peu mes calculs, j'ai le message cité en titre...

Je ne recherche pas une solution directe, mais des pistes...

Est-ce que le fait de la transformer en requête SQL peut améliorer les choses ?

Et la "découper" en requête /sous requêtes ?

Merci d'avance de tout retour d'expérience.

Domi2

[Chtulus]

Salut Domi2 ,

Toujours un plaisir !

J'ai peur de ne pas bien saisir ta problématique !

Pourrais-tu nous montrer ta requête ou un squelette représentatif

Et vue tes dires, un petit point sur ton environnement !

[Domi2]

Bonjour,

Merci de ton intérêt Chtulus

J'utilise Access XP SP3 (sous Windowa XP ou Vista)

La requête en cause. Celle-ci fonctionne encore :

Code :

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

1
2
3
4
SELECT tblImmeubles.ImmId, tblDecomptes.DecId, tblDecomptes.DecImm, tblImmeubles.ImmNumero, tblImmeubles.ImmNatureTravaux, tblImmeubles.ImmNumeroOfl, tblImmeubles.ImmAdresse, tblCommunes.CommuneNom, tblProprietaires.PropNom, tblProprietaires.PropLocalite, tblGerances.GerNom, tblGerances.GerLocalite, tblDecomptes.DecDateDebut, tblDecomptes.DecDateFin, [ImmCFC0Logements]+[ImmCFC1Logements]+[ImmCFC2Logements]+[ImmCFC4Logements]+[ImmCFC5Logements]-[ImmReductionLogements] AS ImmCoutTotalLogements, Int([DecFondsPropres]*[DecTauxFondsPropres]+1*0.5)+Int([DecPretAsh]*[DecTauxPretAsh]+1*0.5)+Int([DecFondsPropresAutres]*[DecTauxFondsPropresAutres]+1*0.5)+Int([DecCapitalEmprunte]*[DecTauxCapitalEmprunte]+1*0.5)+Int([DecAmortissementBase]*[DecTauxAmortissement]+1*0.5)+Int((Int([DecFraisGenerauxBase]/[DecIpcIndiceInitial]*[DecIpcIndiceIndexe])+1*0.5)*[DecTauxFraisGeneraux]+1*0.5)+[DecRedevanceTerrain]+Int([DecTravauxSpeciaux]*[DecTauxTravauxSpeciaux]+1*0.5)-[DecEffortLoyers]+[DecFondsAttribution]-[DecFondsPrelevement]-[DecRevenuAutresParties] AS DecRevenuLocatifBrut, tblLogements.LogEntree, tblLogements.LogEtage, tblLogements.LogNumGerance, tblLocataires.LocDebutPeriode, tblLocataires.LocFinPeriode, IIf(Len([Loc1Nom] & " " & [Loc1Prenom])>20,Left([Loc1Nom] & " " & [Loc1Prenom],19) & ".",[Loc1Nom] & " " & [Loc1Prenom]) AS LocNomPrenom, tblLocataires.LocDecisionCantonOld, tblLocataires.LocDecisionCanton, Int([DecRevenuLocatifBrut]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5)+[LogDiffEtage] AS LogLoyerBrut, Int([DecAideCanton]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideCanton, Int([DecAideCommune]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideCommune, Int([DecAideTiers]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideTiers, [LogLoyerBrut]-[LogAideCanton]-[LogAideCommune]-[LogAideTiers] AS LogLoyerAbaisse, SupprimerAidesCanton([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideCantonSupp, SupprimerAidesCommune([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideCommuneSupp, SupprimerAidesTiers([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideTiersSupp, [LogLoyerAbaisse]+[LogAideCantonSupp]+[LogAideCommuneSupp]+[LogAideTiersSupp] AS LogLoyer, SupprimerAidesCanton([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideCantonSuppPrec, SupprimerAidesCommune([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideCommuneSuppPrec, SupprimerAidesTiers([LogLoyerBrut],[LogAideCanton],[LogAideCommune],[LogAideTiers],[LogLoyerAbaisse],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideTiersSuppPrec, [LogLoyerAbaisse]+[LogAideCantonSuppPrec]+[LogAideCommuneSuppPrec]+[LogAideTiersSuppPrec] AS LogLoyerPrec, Int([DecFondsPropresOld]*[DecTauxFondsPropresOld]+1*0.5)+Int([DecPretAshOld]*[DecTauxPretAshOld]+1*0.5)+Int([DecFondsPropresAutresOld]*[DecTauxFondsPropresAutresOld]+1*0.5)+Int([DecCapitalEmprunteOld]*[DecTauxCapitalEmprunteOld]+1*0.5)+Int([DecAmortissementBaseOld]*[DecTauxAmortissementOld]+1*0.5)+Int((Int([DecFraisGenerauxBaseOld]/[DecIpcIndiceInitialOld]*[DecIpcIndiceIndexeOld])+1*0.5)*[DecTauxFraisGenerauxOld]+1*0.5)+[DecRedevanceTerrainOld]+Int([DecTravauxSpeciauxOld]*[DecTauxTravauxSpeciauxOld]+1*0.5)-[DecEffortLoyersOld]+[DecFondsAttributionOld]-[DecFondsPrelevementOld]-[DecRevenuAutresPartiesOld] AS DecRevenuLocatifBrutOld, IIf([DecRevenuLocatifBrutOld]>0,Int([DecRevenuLocatifBrutOld]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5)+[LogDiffEtage],0) AS LogLoyerBrutOld, Int([DecAideCantonOld]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideCantonOld, Int([DecAideCommuneOld]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideCommuneOld, Int([DecAideTiersOld]/[ImmCoutTotalLogements]*[LogCout]/12+1*0.5) AS LogAideTiersOld, [LogLoyerBrutOld]-[LogAideCantonOld]-[LogAideCommuneOld]-[LogAideTiersOld] AS LogLoyerAbaisseOld, SupprimerAidesCanton([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideCantonSuppOldOld, SupprimerAidesCommune([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideCommuneSuppOldOld, SupprimerAidesTiers([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCantonOld],[LocLoyerDeterminantOld]) AS LogAideTiersSuppOldOld, [LogLoyerAbaisseOld]+[LogAideCantonSuppOldOld]+[LogAideCommuneSuppOldOld]+[LogAideTiersSuppOldOld] AS LogLoyerOldOld, SupprimerAidesCanton([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideCantonSuppOld, SupprimerAidesCommune([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideCommuneSuppOld, SupprimerAidesTiers([LogLoyerBrutOld],[LogAideCantonOld],[LogAideCommuneOld],[LogAideTiersOld],[LogLoyerAbaisseOld],[LocDecisionCanton],[LocLoyerDeterminant]) AS LogAideTiersSuppOld, [LogLoyerAbaisseOld]+[LogAideCantonSuppOld]+[LogAideCommuneSuppOld]+[LogAideTiersSuppOld] AS LogLoyerOld, IIf([LogLoyer]-[LogLoyerAfficheOld] Between -1 And 1,0,[LogLoyer]-[LogLoyerAfficheOld]) AS LogLoyerDifference, IIf([LocDebutPeriode]>[DecDateDebut],[LogLoyerPrec],IIf([LocDebutPeriode]=[DecDateDebut],[LogLoyerOldOld],[LogLoyerOld])) AS LogLoyerAfficheOld
FROM tblProprietaires INNER JOIN ((((tblGerances INNER JOIN (tblCommunes INNER JOIN tblImmeubles ON tblCommunes.CommuneId = tblImmeubles.ImmNumeroCommune) ON tblGerances.GerId = tblImmeubles.ImmGerance) INNER JOIN tblDecomptes ON tblImmeubles.ImmId = tblDecomptes.DecImm) INNER JOIN tblLogements ON tblImmeubles.ImmId = tblLogements.LogImm) INNER JOIN tblLocataires ON tblLogements.LogId = tblLocataires.LocLog) ON tblProprietaires.PropId = tblImmeubles.ImmProprietaire
WHERE (((tblLocataires.LocDebutPeriode)<=[DecDateFin]) AND ((tblLocataires.LocFinPeriode) Is Null Or (tblLocataires.LocFinPeriode)>=[DecDateDebut]))
ORDER BY tblImmeubles.ImmId, tblDecomptes.DecDateDebut DESC , tblLogements.LogNumGerance;

Je dois encore rajouter 2 colonnes et modifier quelques calculs...

Les deux colonnes à rajouter sont assez simples, il s'agit de calculer une différence entre 2 dates...

Je suis en train de faire divers essais.

Domi2

[Chtulus]

Salut domi,

DateDiff() peut-être ?

[Invité]

Bonjour Domi2

Ta requête est copieuse mais sans plus.
Je ne pense pas qu'il soit utile de la découper en requêtes-sous requêtes.
Par contre, le QBE fait parfois des caprices, et je crèe de plus en plus mes requêtes sous SQL DAO. Les erreurs sont plus difficiles à cerner, mais cela oblige à avoir un code nickel.

Espérant que cela pourra t'aider.

[Domi2]

Bonjour,

@Chtulus

Oui, DateDiff(), mais c'est pas le soucis...

J'arrive bien à rajouter mes deux colonnes, c'est dès les modifications de calculs que ça commence à "coincer" (enfin, pour ce que j'en vois).

@Gayot

J'ai commencé à la transformer en SQL, on verra bien si ça se passe mieux.

A+

Domi2

[Domi2]

Re,

Bon, ça ne passe toujours pas...

J'ai bien 2 solutions envisageable, me semble-t-il !

Enregistrer en "dur" les résultats des 2 plus gros champs calculés dans ma table, mais ça ne me plaît pas trop...

Ou essayer de faire les calculs dans des fonctions avec un recordset et appeler le résultat dans la requête, mais je crains pour les performances...

Je vais essayer, en commençant par la deuxième solution...

Domi2

[=JBO=]

Salut Domi2 et ...
chapeau bas aux aventuriers qui ont osé braver le code SQL de Domi2

Pour apporter une petite pierre à l'édifice, je me demandais si ce n'était pas un souci de référence à un nom de colonne calculée (basée sur une expression):

Dans le SELECT tu crées une colonne LogLoyerOld:

Code SQL :

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

[LogLoyerAbaisseOld]+[LogAideCantonSuppOld]+[LogAideCommuneSuppOld]+[LogAideTiersSuppOld] AS LogLoyerOld,

Et juste après (toujours dans le même SELECT) tu fais référence à une colonne LogLoyerOld dans l'expression qui sert à construire la colonne LogLoyerAfficheOld:

Code SQL :

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

IIf([LocDebutPeriode]>[DecDateDebut],[LogLoyerPrec],IIf([LocDebutPeriode]=[DecDateDebut],[LogLoyerOldOld],[LogLoyerOld])) AS LogLoyerAfficheOld

Si LogLoyerAfficheOld est calculée à partir d'une colonne LogLoyerOld elle aussi calculée dans le même SELECT, alors la requête ne peut être exécutée. C'est ça le SQL !

Ais-je mis le doigt sur le problème ?
_

[Domi2]

Bonjour =JBO=,

chapeau bas aux aventuriers qui ont osé braver le code SQL

Je sais, c'est pas vraiment "ma tasse de thé"...

C'est un peu l'enfer, car je dois établir une différence entre un élément d'une période "actuelle" et le même élément d'une période "précédente".

Le problème, c'est que l'élément "précédent" peux prendre 3 valeurs différentes. J'y arrive, mais évidemment, cela me fait multiplier les calculs...

Si LogLoyerAfficheOld est calculée à partir d'une colonne LogLoyerOld elle aussi calculée dans le même SELECT, alors la requête ne peut être exécutée.

Jusqu'à présent elle fonctionnait...

Les modifications dans les calculs que j'ai faites en dernier ne se situent pas là, mais en amont, dans les colonnes "DecRevenuLocatifBrut" et "DecRevenuLocatifBrutOld". J'ai rajouté des références à 3 champs chaque fois, et c'est là que ça coince...

C'est sûr que je peux très nettement simplifier en enregistrant une partie des champs calculés dans les tables, mais bon... Ca ne me plaît qu'à moitié...

Merci de ton aide.

Domi2

[=JBO=]

J'ai bien 2 solutions envisageable, me semble-t-il !

Enregistrer en "dur" les résultats des 2 plus gros champs calculés dans ma table, mais ça ne me plaît pas trop...

Ou essayer de faire les calculs dans des fonctions avec un recordset et appeler le résultat dans la requête, mais je crains pour les performances...

Il y a au moins une 3ème solution qui consisterait à créer une nouvelle requête basée sur ta requête originelle, pour y placer les calculs...

C'est facile à réaliser et les performances ne s'en trouvent pas affectées.
_

[Invité]

Si LogLoyerAfficheOld est calculée à partir d'une colonne LogLoyerOld elle aussi calculée dans le même SELECT, alors la requête ne peut être exécutée. C'est ça le SQL !

Ais-je mis le doigt sur le problème ?
_

Oui, mais cette requête, si j'ai bien compris ce qu'a dit Domi2, s'exécute bien, jusquà ce qu'il rajoute autre chose...

[=JBO=]

Bonjour GAYOT !

Si LogLoyerAfficheOld est calculée à partir d'une colonne LogLoyerOld elle aussi calculée dans le même SELECT, alors la requête ne peut être exécutée. C'est ça le SQL !

Ais-je mis le doigt sur le problème ?

Oui, mais cette requête, si j'ai bien compris ce qu'a dit Domi2, s'exécute bien, jusquà ce qu'il rajoute autre chose...

Jusqu'à présent elle fonctionnait...

Voilà qui me laisse perplexe !
Aurais-je raison de croire aux miracles ? _
_

[Invité]

Comme se sont les éléments ajoutés ou modifiés qui posent le problème, il va peut être falloir quand même que Domi2 passe par une requête intermédiaire ou par la création d'une table temporaire.
Pour le fun c'est pas top, mais pour l'efficacité.....

[Domi2]

Re,

Il y a au moins une 3ème solution qui consisterait à créer une nouvelle requête basée sur ta requête originelle, pour y placer les calculs...

J'ai déjà fait un essai, mais elle reste trop complexe...

Je ne l'ai peut-être pas faite de manière assez réfléchie, il faut que je creuse encore cette piste...

Comme se sont les éléments ajoutés ou modifiés qui posent le problème, il va peut être falloir quand même que Domi2 passe par une requête intermédiaire ou par la création d'une table temporaire.
Pour le fun c'est pas top, mais pour l'efficacité.....

C'est la solution que je suis en train d'essayer...

Et je suis d'accord avec toi... Pas fun peut-être, mais efficace sûrement...

Bon, le week-end Pascal s'annonçant, je reprendrais ça un peu plus tard...

Je vous tiens au courant...

Domi2

[vodiem]

salut Domi2, Chtulus, GAYOT, =JBO=, que du beau monde...

il fût un temps jadis où j'eus avoir cela...
la solution fût simple: SIMPLIFIER.

si je ne me trompe (cela remonte maintenant...) cela dois provenir d'une limitation de 65535 caractères dans l'interpréteur QBE (pas de stockage mais interprétation) et si je me souviens toujours... cette limite ne subsiste plus si tu passes la requete en VBA.

donc essaie de renommer, utiliser les alias pour ne pas dépasser cette limite, voir simplifier le calcul par ex: SupprimerAidesX() est souvent utilisé avec beaucoup de paramètres si tu pouvais changer funcX(a,b,c,d) en: func(e,X) ou "funcB(a,b,c) as calcBase, funcY(calcBase,e,X)" qui serait plus rapide.
...

[Invité]

Bonjour Vodiem

J'avais pensé également à la limite de caractère, mais on s'en apercevait vite car quand on basculait en mode Sql, la requête était escamotée du "surplus" et plantait tout.
Pour s'en sortir il fallait par exemple passer de Access 2000 à Access Xp ou 2002 qui acceptaient plus de caractères.

Je ne pense donc pas que le problème de Domi2 vienne de là.

Par contre Economiser les caractères, Eviter les noms à rallonge et Simplifier les calculs ne peuvent ne peuvent être que bénéfiques dans tous les cas.

[Domi2]

Bonjour à tous,

@vodiem

Merci pour tes conseils... La requête utilise déjà 3 fonctions, et effectivement, en utiliser d'autres serait une piste...

@GAYOT

Eviter les noms à rallonge

J'essaie autant que faire se peut d'éviter. Mais dans le cas précis, j'ai de nombreux éléments qui sont presque identiques (par exemple 8 "revenus locatifs", 2 uniquements dans cette requête), que c'est pas trop évident...

Dans la mesure du possible, j'ai tenté de faire une "moyenne" entre un nommage court et un nommage qui veuille bien encore me "rappeler" quelque chose, mais je suis bien conscient que c'est pas "top"...

Pour l'instant, j'en suis là dans mes essais et réflexions :

Passer par une sous-requête ne fonctionne pas (enfin, moi, je n'y arrive pas, c'est pas tout à fait la même chose...).

Calculer les revenus locatifs par fonction, c'est ok, mais relativement compliqué il me semble. Et pour les performances, j'ai un peu soucis... Il me reste à étudier la possibilité de faire ces calculs dans une sous-requête.

La meilleure solution pour l'instant, reste la table temporaire... Simple et efficace, ça fonctionne.

Je vais mettre en "balance" cette solution et une sauvegarde "en dur" des certains champs calculés dans les tables. Pour l'instant, car j'ai besoin d'avoir un résultat, au moins temporaire, mardi...

Et continuer de travailler sur les pistes données par vodiem et =JBO=.

Merci à tous et excellentes Fêtes Pascales !

Domi2