Création d'une base access pour suivi récurrences mensuelles

**thibaud.noumea** · 09/04/2018, 17h38

Bonjour,

Je souhaite développer un base access pour créer des nouveaux sites (formulaires) et créer des états des récurrences mensuelles qui seront envoyés trimestriellement à chacun de nos clients pour leur facturer.

Pour simplifier j’ai trois tables

Tsites
TfacturationA
TfacturationB

Dans la table Tsites les champs sont :

pays
numéro de site
entité à facturer
option1 (oui/non)
option2 (oui/non)
option3 (oui/non)
option4 (oui/non)

dans la table TfacturationA les champs sont

pays
entité à facturer
option1 x euros/mois
option2 y euros/mois

dans la table tfacturationB les champs sont

pays
entité à facturer
option3 z euros/mois
option4 w euros/mois

Le prix mensuel des options est différent par pays et par entité à facturer.

Je n’arrive pas à trouver l’astuce dans une requête pour faire en sorte que pour un site donnée si tsites.option1=oui alors il faut afficher tfacturationA.option1 (multiplié par trois car j’envoie trimestriellement) et la même logique pour toutes les options bien sûr.

Si je mets dans critere sur tfacturationA.option1 que tsites.option1= oui alors si tsite.option1=non et tsite.option2=oui alors je n’ai pas de résultat or il faut bien facturer option2 même si option1 est pas activée. En fait je voudrais que tfacturation1.option1 soit nul si tsites.option1=non…pour toutes les options

Y a-t-il un moyen de faire un SI ou quelque chose dans ce style ? Note : je ne maitrise pas le langage SQL, j’utilise les assistants création d’Access.

Ensuite je vais devoir faire un formulaire pour créer de manière simple et conviviale des nouveaux sites.

Enfin une fois que j’ai le résultat de ma requête je souhaiterais en faire des états pour chaque pays/entité à facturer, c’est une question qui se posera plus tard mais est-ce possible de cacher les champs de ma requêtes ou toutes les résultats sont nuls pour ces champs ? C’est pour diminuer la taille du tableau final à envoyer au client..exemple si option 2 est pas activé pour tous les sites de son pays je ne souhaite pas qu’il y ait une colonne option2 avec que des 0 dedans.

Sinon connaissez-vous par hasard un logiciel gratuit ou avec CAPEX uniquement (achat logiciel en une fois sans récurrence annuel) qui pourrait satisfaire à mon besoin ?

Merci de votre support.

**marot_r** · 09/04/2018, 18h10

Bonjour.

Pour l'outil gratuit, j'ai fait quelques essais légers avec APEX d'Oracle et j'ai trouvé cela intéressant.
Moins pratique qu'Access surtout pour la partie débuggage mais intéressant néanmoins.
Et nécessite sans doute plus d'apprentissage pour faire l'application que tu veux.

Tu peux aussi regarder les versions Express Edition de SQL Server de de Visual Studio de Microsoft.
On est dans une toute autre catégorie qu'Access mais ce sont de bons outils qui te permettent de passer à la version non Express directement.
Cela nécessite beaucoup plus d'apprentissage pour faire l'application que tu veux.

Et sache que tu n'as pas besoin d'avoir une licence de Access par poste (même si c'est ce que je recommande car cela simplifie grandement la vie).
Tu peux installer un runtime Access qui te permet d'éxécuter tes applis.
Tu n'as donc besoin que d'une licence d'Access pour le poste du développeur.

Pour revenir à ton problème.

Tu as à priori un soucis avec ta structure qui n'est pas adaptée à une base de données et donc aux requêtes.

Elle devrait probalement ressembler à cela :

TEntite
ClefEntite
Autres infos sur l'entité (ex : Nom, Adresse)

TSite
ClefSite
Autres infos sur le site (ex : Nom, Adresse)

TPays
ClefPays
Autres infos sur le pays (ex : Nom)

TOption
ClefOption
Autres infos sur l'option site (ex : Nom)

TOptionEntite
ClefOptionEntite
ClefEntite
ClefOption

En relation

avec TEntite
avec TOption

Sur leurs clefs respectives

TFacturation
ClefFacturation
ClefPays
ClefEntite
ClefSite
ClefOption
DateFacturation
Montant

En relation

avec TPays
avec TEntite
avec TSite
avec TOption

Sur leurs clefs respectives.

Et comme tu peux le voir une BD ne s'organise pas du tout comme un fichier Excel.

Les BD sont un peu unidimensionnelles. Elles travaillent avec des listes, des listes de listes, des listes de listes de listes ...
Dans une BD on s'efforce autant que faire ce peut de ne pas répéter l'information. Si on a besoin à plusieurs place on s'y réfère par sa Clef pour pouvoir aller la "pécher" au moment où on en a besoin.

Les Excel sont plus bidimensionnels. Ils travaillent généralement avec une lignes et X colonnes.
Il est très fréquent de répéter de l'information dans un fichier Excel et cela se fait assez facilement.

A+

**thibaud.noumea** · 12/04/2018, 20h33

Ok merci beaucoup Marot_r je vais essayer de de modifier tout cela comme tu le conseilles.

A+

**thibaud.noumea** · 07/05/2018, 16h41

Bonjour,

Je suis un peu perdu en fait pour créer ma structure de table (entité-assocation). Je ne sais pas si j'ai bien résumé mon objectif, ci-dessous et en pièce jointe un peu plus de détails. Pourriez vous me dire si je peux faire quelque chose sous access pour satisfaire mon besoin ?

Notre société T vends des matériels et systèmes pour équiper des commerces (sites). Nous travaillons sur plusieurs pays. Un numéro de site est unique par pays mais un numéro de site peut être identique sur un autre pays. Dans chaque pays il y a un ou plusieurs mainteneurs locaux qui sont en charge de maintenir ces produits. Chaque mainteneur local est en charge d'un certain nombre de sites. Deux mainteneurs ne peuvent pas maintenir un même site. Nous facturons ces mainteneurs des récurrences mensuelles sur les options activées sur ces matériels (qui eux refacturent au client final avec leur marge mais cela est hors scope) . Chaque station service active une ou plusieurs options sur nos matériels et chaque option a un prix de récurrences mensuel. Le prix des options dépend du client final et du pays. Certaines options sont fournies par notre société T mais d'autres peuvent provenir de nos fournisseurs / partenaires. Nous facturons toutes les options activées à nos mainteneurs locaux et pour les options qui ne nous appartiennent pas nos fournisseurs nous les facture à certains prix. Mon but est d'établir une base de données qui me permette pour chaque mainteneur local de générer un tableau de récurrences mensuel à leur facturer mais également de générer un tableau de récurrences que nos fournisseurs vont nous facturer (pour vérifier leur devis). Enfin à chaque nouveau site crée sur le terrain j'aimerais via un formulaire le rentrer dans la base manière intuitive (exemple via un bouton nouveau site puis en entrant le pays / le mainteneur /le numéro de site / les options activées sur le site) sans rentrer le prix par option dans ce formulaire puisque ce sera géré des données de me(s) table(s).

Pensez vous que ceci est gérable ? J'ai essayé de travailler sous excel au début via des macros et des fiches stations mais il faut être un AS du visual basic..Je vous met un extrait d'une feuille excel (autant de feuille que de mainteneurs locaux à facturer) de notre document de travail excel actuel "remanié" pour remplir ces taches actuellement ainsi cela peut vous aider à comprendre le résultat que je souhaiterais. En haut le tableau qu'on envoie pour facturer les mainteneurs et en bas ce que nos fournisseurs nous facture.
Merci beaucoup

**marot_r** · 08/05/2018, 13h23

Bonjour.

Ci-joint une mini-mini maquette pour ta BD. Je n'y ai mis que les champs absolument indispensables et il faudra que tu l'enrichisses.
Il est possible aussi qu'il te faille d'autres tables, là ce sont celles déduites des contraintes que tu as écrites.

J'ai utilisé deux astuces en particulier :

Ton entreprise doit être considéré comme un "Mainteneur" ce qui te permet d'être le fournisseur de tes produits.
J'ai mis une date de début et une date de fin car je pense que tes contracts de maintenance peuvent changer.
Même chose pour les prix de tes options.
La date de fin pourrait être 9999-12-31 si tu veux qu'elle reste valide tout le temps.
Il faudra faire un contrôle au moment de la saisie pour t'assurer que tu n'as pas de chevauchement de période.

Avec les dates, on ne peut pas implanter dans le modèle l'unicité de mainteneur et de site donc il faudra le vérifier au moment de la saisie.
Il faut t'assurer que pour la période, tu n'as pas d'autre mainteneur pour ce site pour cette période.

La table tblSitePaysMainteneurProduitOption est celle qui va te servir pour déterminer les options actives pour une période donnnée.
La table tblTarifProduitOption est celle qui va te donner le prix à appliquer.
Il y a un peu de travail à faire pour obtenir une facturation mais tu as les infos dont tu as besoin.
Attention tu pourrais avoir des périodes de facturation qui chevauchent des périodes de prix et d'option activées.

Pour l'interface de saisie, cela viendra quand tu auras un modèle de données correct et à peu prêt définitif.

Là j'ai supposé que tu ne mettais dans tblSitePaysMainteneurProduitOption que les options effectivements actives.

FacturationRecurente.zip

A+

**thibaud.noumea** · 09/05/2018, 13h37

Bonjour Marot,

Merci beaucoup pour ton aide. Je m'y atèle !

Cdt,

**thibaud.noumea** · 09/05/2018, 14h35

Marot petite question svp.

Pour les clés primaires/étrangères des tables, ce sera donc un numéro auto mais j'ai déjà toutes mes tables excel comme mis en exemple en pièce jointe et comme il y a des centaines de sites dans plusieurs pays je vais essayer d'importer mes tableaux excels mais comment je vais faire pour créer les clés primaires / etrangeres dans excel puis importer ces tables si les clés primaires sont attribuées automatiquement par access ?

Merci

**bertiny** · 12/05/2018, 09h09

En fait tu peux prendre cette option qui permet à access de créer une clef primaire lors de l'importation. çà sera forcément un NuméroAuto. Ensuite tu peux restructurer ta table comme tu le souhaites. Pour les clefs étrangères tu pourras les ajouter après l'importation ou créer des colonnes dans Excel où tu inscrits juste le nom du champ clef étrangère puis tu importes.

Cordialement.

**marot_r** · 13/05/2018, 01h38

En complément, tu n'est pas obligé d'utiliser des numéros auto pour tes clefs primaires si tu as déjà une clef primaire.

L'avantage des numéro auto c'est qu'Access s'occupe de trouver les prochains numéros pour toi.

Si tu ne les utilisent pas tu dois le programmer toi-même.

Tu peux aussi faire un traitement pour substituer tes clefs actuelles à la nouvelle clef calculée par Access.

Cela dépend de comment vont vivre tes données et qui est "maître" dans l'attribution des clefs primaires.

Si tu importe régulièrement des données d'Excel et que la clef est définie en Excel, en effet l'autonum n'est pas la solution.

Une clef peut être un code alphanumérique ou tout autre chose qui identifie ton enregistrement de manière unique.

A+

**thibaud.noumea** · 15/05/2018, 14h11

Bonjour,

Merci pour vos conseils sur les clés auto. J'ai réorganisé la base que vous m'avez fourni Marot pour limiter au maximum les clef auto et les champs que j'ai choisi en clé primaire seront unique. Je laisse des clés auto seulement sur les tables d'association.

Dans ma table tblSitePays j'ai du créer une double clé primaire car comme je mentionnais précédemment un numéro de site est unique seulement dans un pays donné cependant pays+numéro de site est unique. Est ce que c'est correcte la manière dont j'ai associé la table tblSitePays et tblSitePaysMainteneurProduitOption ? Je n’étais pas sur de comment gérer cela quand on a une clé primaire avec deux champs.

Autrement plus problématique je n'arrive pas à associer les tables tblOptionProduit, tblSitePaysMainteneurProduitOption et tblTarifProduitOption sur les champs NomProduit et NomOption, j'ai une erreur "index unique introuvable pour le champs référencé d'une table principale". J'ai beau chercher sur internet je ne vois pas comment résoudre cela.

Pour info j'ai essayé de supprimer ces relations sur la base que vous m'avez fourni Marot et si j'essaye de les recréer j'ai également cette erreur. Quelle est l'astuce

Voir PJ FacturationRecurente cles modifiees.zip

Merci beaucoup !

**marot_r** · 16/05/2018, 02h00

Bonjour.

"index unique introuvable pour le champs référencé d'une table principale"

Génralement c'est dans une relation 1 à N, les champs choisis dans la relation ne forment pas une clef primaire ou un index unique de la table du côté 1.

Parfois Access se croise les yeux donc il faut

Supprimer la relation.
Supprimer la clef primaire ou l'index unique.
Sauvegarder la table.
Rétablir la clef ou l'index.
Refaire la relation.

Pour le reste je n'ai pas Access 2007, donc si tu peux poster une copie d'écran de la fenêtre des relations avec toutes les tables que tu utilises, cela aiderrai.

A+
A+

**thibaud.noumea** · 16/05/2018, 18h03

Bonjour Marot,

Moi aussi je me croise les yeux

J'ai essayé mais ça ne passe pas..En PJ la screenshot + mdb si besoin.

thanks

Cdt,

**marot_r** · 19/05/2018, 20h03

Bonjour.

Je t'ai fait la modif, ça a fonctionné directement.

FacturationRecurente cles modifiees 2018-05-19 14h03.zip

A+

**thibaud.noumea** · 24/05/2018, 18h57

Bonjour Marot,

Merci ce message d'erreur va me rendre dingue je pense !

Pour info j'ai supprimé la table type de produit car nous n'avons que deux produits sur lesquelles on active des options avec des récurrences mensuelles. Je garde simplement la table produit qui servira.

Sinon j'ai modifié le prix en prix d'achat et prix de vente car comme je disais on facture les options à nos ditributeur locaux (mainteneurs qui eux refacturent le client) à un prix (prix de vente) et nos fournisseurs nous facture les options à un prix inférieur (prix d'achat). Pour les options que notre société fournies je mettrai un prix d'achat à 0 du coup.

Egalement j'ai pensé à rajouter une table client car le prix des options sont fonctions du client en fait. Dans un pays on peut avoir deux clients, en fonction du nombre de site équipés par nos produits le prix des options peuvent être différentes !

Il faut donc faire un lien entre la table client et la table ou il y a les prix j'imagine ? Je voulais passer par une table intermédiaire appellé tblMainteneurClient car un mainteneur peut travailler pour plusieurs clients mais j'ai ce fameux message d'erreur rencontré au dessus du coup j'ai lié NomClient de la table client à la table ou il y a les prix mais je sens que c'est pas correcte !

C'est frustrant de ne pas être indépendant car c'est intéressant mais j'apprend petit à petit !

Cf screenshot en PJ.

Merci Marot.

**marot_r** · 25/05/2018, 12h07

Bonjour.

Pour ta table de prix par client tu peux faire :

tblPrixClient
Clef
ClefClient
ClefProduit
Prix
DateDebutPrix
DateFinPrix

en relation avec tblClient

tblClient
Clef
ClefFournisseur (suppose un seul fournisseur par client).

en relation avec tblFournisseur

Ou

tblPrixClient
Clef
ClefDistributeur
ClefClient
ClefProduit
Prix
DateDebutPrix
DateFinPrix

Le report de la ClefDistributeur dans ta table t'évite à avoir à passer par la table client pour trouver tous les prix d'un distributeur.

A+

**thibaud.noumea** · 25/06/2018, 18h02

Bonjour Marot,

Concernant ma structure je pense qu’on commence à être pas mal. J’ai rajouté une table tblfournisseur (comprendre fournisseur de l’option) et une table tblNomEntitéAFacturer.

J’ai inséré des données dans les tables pour voir si cela fonctionnait cependant j’ai rencontré un probleme sur la table tblSitePays. En effet je voulais que le couple NomPayas et NumSite soit une clé primaire car ce couple est unique. Cependant je ne peux pas mettre deux fois le même nom de pays ou deux fois le même numéro de site. Je n’ai pas trouvé l’astuce, une clé primaire peut bien être un couple de champs ?

Sinon que pensez-vous de la structure de la base, j’ai l’impression que quelque chose ne va pas ou peut être amélioré..

Pour synthétiser :

Cette base de donnée doit me servir à suivre et envoyer les factures tous les trois mois correspondantes aux OPEX mensuels (charges d’exploitation) liés aux options activées sur chacun nos produits. Elle ne sert pas à suivre la vente des produits mais juste les options activées sur ces produits.

Les sites sont installés et maintenus par nos mainteneurs locaux (comprendre aussi distributeur local). Selon les pays nous envoyons la facture trimestriel soit à nos mainteneurs locaux soit directement au client final du dit pays c’est pour cela que j’ai rajouté une table tblEntitéàFacturer que je lie à la table tblSitePays (est-ce correct ? ). Cette dernière table contient donc soit le nom de nos distributeurs soit le nom du client final.

Le prix de l’option dépend du client final, c’est pour cela que NomClient est rajouté dans la table tblTarifProduitOption.

En PJ une screenshot et la base si besoin.

Merci Marot ! Hâte que ça prenne vie.

Cdt,

**marot_r** · 26/06/2018, 13h31

Bonjour.

une clé primaire peut bien être un couple de champs ?

Oui, il te suffit de selectionner les champs voulus et d'appuyer sur l'icone avec la clef.

Pour la structure, s'il te plais, poste une copie d'ecran des la fenetre des relations.

A+

**thibaud.noumea** · 02/07/2018, 15h13

Bonjour Marot,

En PJ la screenshot, je l'avais mis dans le .zip.

Cdt,

**thibaud.noumea** · 06/08/2018, 16h31

Bonjour !

Donc je pense avoir plus ou moins finalisé le design général de ma base. Il y aura certainement des ajustements/optimisation à faire et je m'en rendrais compte avec l'utilisation.

On passe aux requêtes

J'ai crée ma première requête dont le résultat est cohérent avec les données de mes tables ce qui me plait bien déjà

Les questions que je me pose pour le moment svp si vous pouvez m'aiguiller :

1/ Les prix de ventes (et d'achats) de mes options sont mensuels. Je facture tous les trois mois. Ainsi dans ma table excel que j'utilisais avant je multipliais le nombre de mois avec la tarif mensuel. Comment puis-je géré cela automatiquement pour les options dont les dates d'activation sont inférieures à trois mois dans access ? Au pire je peux toujours lors de ma création de site / options associées indiquer le nombre de mois à la main pour le prochain trimestre puis je les passerai à trois mois pour le trimestre suivant, c'est ce que je faisais sous excel mais c'est source d'erreur ou d'oubli.

2/ Pour le résultat final je vais avoir d'un tableau que j'avais attaché au début. Avec ma requête j'ai autant de ligne que d'option par site. Il faudrait que j'ai horizontalement : numéro de site, nom de site, date de création de site, nom option 1, nom option 2...Est ce que je pourrais faire cela rapidement ?

3/ on n' y est pas encore mais ensuite il faudra que j'imprime l'état du point 2/ pour chaque entité à facturer (qui est soit le distributeur local qui va refacturer au client final soit directement le client final en fonction des cas). Quand nous facturons le distributeur local il arrive qu'il travaille pour plusieurs clients finaux dans différent pays. Est ce que cela va passer par une requête/état générique couplé à une macro avec un for ... ou vais je créer toutes les requêtes/état à l'avance manuellement puis je les lancerais via une macro ?

Merci pour votre précieuse et indispensable aide !

En PJ un printscreen de mes relations et ma requete pour info.

Cdt,