Discussion :

[Asahenn]

Bonjour à tous !

J'ai quelques problèmes à mettre en place le système suivant :

J'ai un ensemble de clients qui possèdent un ou plusieurs comptes bancaires. Ces comptes bancaires peuvent être dans différentes devises (Euro, Dollar américain, Dollar Canadien...).

J'aimerai garder un historique de la masse totale d'argent de mes clients, que je convertis dans une seule monnaie (l'euro ici) pour mes statistiques.

Cela implique donc de garder un historique du montant sur chaque compte (et sa devise qui est inchangée) mais également de garder l'historique des cours des monnaies sinon je ne peux pas savoir ce que valait la masse d'argent il y a un an.

Voilà comment j'ai modélisé cela sur Access :



Les historiques sont simplement géré en rentrant le montant sur le compte à une date donnée (dans l'application que je veux créer cela sera fait une fois par mois ou par trimestre, donc relativement peu souvent). Pareil pour les devises
Si ces relations ne sont pas bonnes ou si il y a une meilleure manière de gérer cela je peux ouvrir un sujet dans modélisation ; ici c'est sur la création des requêtes que j'ai du mal.

Ce que je veux faire, c'est un relevé trimestriel de chaque compte. Ça veut dire faire un état avec le montant sur chaque compte dans sa devise par exemple au 31/03, 30/06... puis la conversion en euro et faire un total de tous les comptes en euro. (Donc d'abord groupé par trimestre puis par compte)

Pour cela je prendrai la dernière date rentrée dans le trimestre (par exemple si sur le compte A il y a 1000€ au 21/02 et 1200€ au 28/03 je considère qu'il y a 1200€ sur le compte pour le trimestre.

En revanche pour les devises je prendrai la dernière date rentrée par rapport à la date maximum du trimestre (par exemple si il y a un taux pour le dollar rentré le 25/03 et un le 02/04 je prend celui du 25 pour le premier trimestre) mais un taux rentré à une date antérieure devrait aussi être pris en compte (même si cela ne devrait pas arriver à l'utilisation, c'est ce qui me paraît le plus logique).

N'ayant jamais fais de requêtes aussi complexes je ne sais pas par où commencer, si je dois faire des sous requêtes, quelle partie traiter dans l'état et quelle partie dans la requête (d'habitude je fais mes regroupements et tris avec les états ça permet d'avoir une requête pour plusieurs états je trouve cela pratique).

Merci à ceux qui ont prit le temps de me lire !

[bertiny]

Bonjour
En réalité pour ce genre de situation il est souvent préférable de proposer une ébauche de la requête que tu souhaites faire. A partir de là on améliore jusqu'à obtenir ce que tu espères avoir.

Cordialement

[Asahenn]

Bonjour

Merci bertiny pour ta réponse

Je suis d'accord que simplement décrire ce qu'on veut faire sans rien n'avoir tenté peut donner l'impression que j'ai eu la grosse flemme et que je n'ai même pas essayé. J'avais testé deux trois trucs ; mais je me suis dis que poster un départ de solution risquait d'orienter les lecteurs vers une manière de faire et d'arriver à une requête qui fonctionne mais pas forcément la plus optimale.

Du coup voilà où j'en suis :

J'arrive à grouper par trimestre dans une requête et récupérer uniquement les derniers enregistrements pour le semestre à l'aide de Max(date_histo_compte). Seulement si j'ajoute n'importe quel autre champ de la table tHisto_compte (comme par exemple le montant ce qui serait bien pratique ) alors il me renvoie tous les enregistrements et plus uniquement les derniers du trimestre.
J'ai pensé à faire ensuite une jointure entre ma table triée et ma table complète pour avoir toutes les infos mais je ne peux pas ajouter la clé primaire sinon même problème.



Pour ceux qui préfèrent le SQL :

Code SQL :

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

1
2
3
4
SELECT Format([date_histo_compte],"yyyy_q") AS Trimestre, Max(tHisto_Compte.date_histo_compte) AS MaxDedate_histo_compte, tCompte.numero_compte, tClient.nom_cli
FROM (tClient INNER JOIN tCompte ON tClient.N° = tCompte.fk_client_compte) INNER JOIN tHisto_Compte ON tCompte.N° = tHisto_Compte.fk_compte_histo_compte
GROUP BY Format([date_histo_compte],"yyyy_q"), tCompte.numero_compte, tClient.nom_cli
ORDER BY Max(tHisto_Compte.date_histo_compte);

Et là ce n'est que la première étape, je n'imagine pas quand il faudra aller récupérer le cours de la devise correspondant en parallèle

Merci à ceux qui sauront m'aider !

[bertiny]

Bonsoir
je pense que c'est mieux de le faire en deux requêtes: une sous-requête RQ1 et la requête qHistoGroupé
- sous-requête RQ1

Code SQL :

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

1
2
3
4
SELECT Format([date_histo_compte],"yyyy_q") AS Trimestre, Max(tHisto_Compte.date_histo_compte) AS Dedate_histo_compte, tCompte.numero_compte, tClient.nom_cli, tCompte.N°
FROM (tClient INNER JOIN tCompte ON tClient.N° = tCompte.fk_client_compte) INNER JOIN tHisto_Compte ON tCompte.N° = tHisto_Compte.fk_compte_histo_compte
GROUP BY Format([date_histo_compte],"yyyy_q"), tCompte.numero_compte, tClient.nom_cli, tCompte.N°
ORDER BY Max(tHisto_Compte.date_histo_compte);

c'est elle qui te renvoie les valeurs que tu a affichée sur la requête proposée. j'ai juste ajouté la clé primaire de la table tCompte.

- la requête qHistoGroupé. C'est elle que tu veux faire je pense.
Celle sur laquelle tu ajoute les champs que tu veux de la table tHisto_Compte

Code SQL :

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

1
2
SELECT RQ1.Trimestre, RQ1.Dedate_histo_compte, RQ1.numero_compte, RQ1.nom_cli, tHisto_Compte.montant_histo_compte
FROM RQ1 INNER JOIN tHisto_Compte ON RQ1.N° = tHisto_Compte.N°;

Cordialement.

[Asahenn]

Merci bertiny pour ton aide !

Cependant, après essai j'ai l'impression que ça ne marche pas. Les montants affichés par la requête finale qHistoGroupé sont parfois faux.

Je pense avoir compris pourquoi :

Dans la 1ère requête, on fait le tri pour garder uniquement la dernière entrée de l'historique pour le trimestre, on ajoute à ce tri le N° (clé primaire) de la table des Comptes (et non pas de l'historique).
Du coup, quand on fait la jointure dans la deuxième requête, on fait correspondre la clé primaire de la table Compte à celle de la table historique et ça n'a pas trop de sens, la jointure à faire est celle entre la clé primaire de la table Compte (tCompte.N°) et la clé étrangère de la table historique (tHisto_Compte.fk_compte_histo_compte).

Cependant, si j'essaie d'obtenir le champ contenant la clé étrangère dans ma première requête, le tri ne fonctionne plus (tous les enregistrements s'affichent au lieu d'avoir seulement les derniers de chaque semestre).

[bertiny]

Bonjour
Regarde ceci:
RQ1:

Code SQL :

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

1
2
3
4
SELECT Format([date_histo_compte],"yyyy_q") AS Trimestre, Max(tHisto_Compte.date_histo_compte) AS Dedate_histo_compte, Max(tHisto_Compte.N°) AS N°
FROM tHisto_Compte
GROUP BY Format([date_histo_compte],"yyyy_q")
ORDER BY Max(tHisto_Compte.date_histo_compte);

qHistoGroupé:

Code SQL :

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

1
2
SELECT RQ1.Trimestre, RQ1.Dedate_histo_compte, RQ1.N°, tHisto_Compte.montant_histo_compte, tCompte.numero_compte, tClient.nom_cli
FROM (tClient INNER JOIN (tCompte INNER JOIN tHisto_Compte ON tCompte.N° = tHisto_Compte.fk_compte_histo_compte) ON tClient.N° = tCompte.fk_client_compte) INNER JOIN RQ1 ON tHisto_Compte.N° = RQ1.N°;

[Asahenn]

Bonjour,

Rajouter Max(tHisto_Compte.N°) fonctionne dans un premier temps : du moment qu'on ajoute tous les enregistrements dans l'ordre. Il faut aussi ajouter les numéros de compte (de la table compte) sinon la requête renvoie la date maximum par trimestre et non pas par trimestre par compte.

Mais Max(tHisto_Compte.N°) renvoie simplement le N° maximum pour le trimestre, donc si on a un enregistrement avec comme clé primaire (N°) 18 au 25/09 et un autre N° 19 au 23/09, Max(tHisto_Compte.N°) va renvoyer 19 donc ça ne va pas.

[bertiny]

Bonjour
en réalité, si N° est un NuméroAuto, on ne devrait pas avoir de problème. Vu que l'historique est enregistré au fur et à mesure. En réalité je ne connais pas le type de données de tes champs et il faut peut-être me les présenter.

[Asahenn]

Effectivement je ne les ai pas donné : toutes mes clés primaires sont des numéros auto, les clés étrangères (fk_xxx) sont des entiers longs, le reste est soit date, soit numérique, soit texte court.

J'ai finis par trouver une solution en cherchant sur StackOverflow, trouver le dernier enregistrement d'une table par mois/trimestre/année me paraissait quand même être une problématique de base du SQL ; il doit forcément y avoir quelqu'un ayant eu la même question ici mais avec des mots clés différents.

Voici les requêtes :

Requête "RQ1_bis"

Code SQL :

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

1
2
3
4
 
SELECT Format([date_histo_compte],"yyyy_q") AS Trimestre, Max(tHisto_Compte.date_histo_compte) AS MaxDedate_histo_compte, tCompte.numero_compte
FROM tCompte INNER JOIN tHisto_Compte ON tCompte.N° = tHisto_Compte.fk_compte_histo_compte
GROUP BY Format([date_histo_compte],"yyyy_q"), tCompte.numero_compte;

Requêtes "Histo_Groupé"

Code SQL :

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

1
2
3
 
SELECT RQ1_bis.Trimestre, tCompte.numero_compte, tHisto_Compte.date_histo_compte, tHisto_Compte.montant_histo_compte
FROM (RQ1_bis INNER JOIN tHisto_Compte ON RQ1_bis.MaxDedate_histo_compte = tHisto_Compte.date_histo_compte) INNER JOIN tCompte ON (tCompte.N° = tHisto_Compte.fk_compte_histo_compte) AND (RQ1_bis.numero_compte = tCompte.numero_compte);

Notons que là j'ai utilisé le "numero_compte" mais j'aurai plutôt du utiliser le champ fk_compte_histo_compte dans RQ1 et faire ma jointure là-dessus. C'est mieux de travailler sur les clés primaires.

Le principe c'est de faire mon tri sur uniquement les champs pertinents dans la première requête (la date et le numéro de compte) puis de faire une jointure là dessus pour récupérer tous les champs dont j'ai besoin à côté (montant, nom du client etc...).
Le soucis de cette méthode c'est que je ne fais pas mon INNER JOIN sur une clé primaire donc il peut y avoir plus d'un enregistrement (si il y a deux entrées pour la même date et le même compte dans l'historique).
Apparemment il y a une solution à ce problème en SQL mais pas utilisable en SQL access ; à base de "OVER" et "PARTITION BY".

Je pourrai pour cela définir les deux champs "date_histo_compte et fk_compte_histo_compte" comme une clé primaire composite. Cela obligerait la table à n'avoir qu'un seul enregistrement pour un compte et une date donnée.

Voilà une partie du problème est réglée maintenant il faut y mettre les devises ; je vais voir si je m'en sors tout seul.

[bertiny]

Je suis content de le savoir. En ce qui concerne l'historique du même jour, si tu voudrais uniquement la dernière de chaque trimestre même s'il ya deux du même jour alors je pense que la dernière à être enregistrée est retrouvée à partir de ta clé primaire NuméroAuto. C'est la plus grande valeur correspondante au deux dates du même jour. D'où l'importance d'inclure non seulement la clé primaire mais aussi le numéro du compte évidemment.
Si je serai utile n'hésite pas.
Cordialement

[Asahenn]

Oui pour départager les inégalités ça peut être utile effectivement, merci pour ton aide en tout cas bertiny !

[bertiny]

As-tu trouvé une solution précise ou alors ça ne passe toujours pas ?