Calculer des cumuls de véhicules couverts sur 5 années glissantes

**bambi12047** · 26/11/2012, 18h35

Bonjour à tous

Je suis en train de réaliser une base de données sous Access 2003.

J'ai plusieurs tables mais mon problème ne concerne que les suivantes :

Contrat(code_contrat, ID_client, ID_commercial)

Véhicule vendu (clé_véhicule, code_contrat, date_début, nombre véhicule)

Clause(ID_contrat, code_contrat, date_ début, durée)

J'ai tous les mois des véhicules vendus qui rentrent dans ce que l'on appellera "un parc ".

On considère que le 1er mois le parc est constitué en moyenne de la moitié des véhicules vendus sur le mois. puis au mois 2 de la moitié des véhicules vendus au mois 2 plus tout ceux vendus au mois 1.

La durée de prise en compte des véhicules vendus dans le parc est la durée présent dans la table clause lorsque les premiers véhicules rentrent dans le parc.

ex : Prenons par exemple une durée de 3 ans

Année 2010

janv : 10 véhicules Parc de janvier = 10/2
Fev : 10 véhicules Parc de février = 10 + 10/2
Mar : 10 véhicules Parc de Mars = 10 + 10 + 10 / 2
"
"
"
déc : 10 véhicules Parc de décembre : 11*10 + 10/2

(le nombre de véhicules vendus n'est pas forcement constant je l'ai mis a 10 a chaque fois pour l'exemple)

une fois la durée de 3 ans écoulée le parc continue à varier et les premiers véhicules commencent à vider le parc alors que d'autres continuent d'arriver :

2013

Janv : 10 véhicules Parc de janvier = Parc de décembre 2012 - parc de
janvier 2010 + 10 / 2

et ainsi de suite...

Sachant que la durée peux être de 5 ans je voudrais pouvoir étudier le parc de N-5 à N+5 donc 10 années glissantes.

Est ce que quelqu'un aurait une idée pour calculer cela ? Je ne vois pas comment faire sans devoir réaliser une requête qui calcule pour chaque mois la valeur du parc couvert (soit 10*12 requêtes)

en faisant pour chaque mois : le nombre vendu /2 + la somme des nombres vendu antérieur depuis la date début de prise en compte dans le parc.

J’espère avoir été compréhensible :-)

Merci d'avance pour votre aide.

Bonne soirée

**78chris** · 26/11/2012, 18h54

Bonjour

Si je comprends bien au bout de 5 ans ton parc a été alimenté par la quantité vendues sur 12 mois X 4 ans + quantité 11 mois + quantité dernier mois/2

Mais les véhicules sortent du parc au bout de la durée inscrite dans la clause.

Cette durée est-elle en mois ?

Pourquoi y a t'il une date de début dans les deux tables ?
Y a t'il plusieurs clauses par véhicule ?

Il manque des infos pour comprendre

**bambi12047** · 27/11/2012, 10h13

Bonjour,

en effet en 5 ans le parc est alimenté de la quantité vendues sur 12 mois X 4 ans + quantité 11 mois + quantité dernier mois/2

Et au bout de 5 ans (mois par mois) les véhicules sortent du parc alors que d'autre continue de rentrer.

La durée de couverture dans le parc est en année. il y a une date de début dans la table véhicule qui fait référence à la date d'entrée dans le parc (des véhicule du mois de janvier 2010 par exemple... ) et une date dans clause qui fait référence à la modification du contrat

Ces modifications peuvent être soit des changements de durée de "couverture" dans le parc soit d'autre changement du contrat, comme le prix pour la période par exemple.

un contrat peux avoir plusieurs clauses et un contrat concerne des véhicules vendus.

Vous faut-il d'autres informations?

**78chris** · 27/11/2012, 22h31

Bonjour

Reste quelques questions : dans véhicule vendu je vois nombre vehicule alors que je pensais qu'il y a avait un seul véhicule par enregistrement.

La durée réelle n'est pas claire pour moi : c'est 5 ans pour tous les véhicules ou bien elle figure dans une clause et peut avoir été allongé ou réduite par une ou plusieurs autres clauses ?

Sur le principe, si 5 ans et 1 véhicule par enregistrement je ferais sur la table vehicule_vendu une requête

Code :

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

select count(*) as anciens from vehicule_vendu where date_début>=AjDate("m";-60;Date()) and date_début<=AjDate("m";-1;Date()) group by 1

puis une autre requête

Code :

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

select count(*) as nouveaux from vehicule_vendu where date_début>AjDate("m";-1;Date()) group by 1

puis une requête

Code :

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

1
2
SELECT [Requete1]![anciens]+[Requete2]![nouveaux]/2 AS Parc
FROM Requete1, Requete2;

Sinon il faudrait remplacer les 60 par durée X 12...
et count par sum du nombre de véhicules si plusieurs véhicules

A adapter

**bambi12047** · 28/11/2012, 15h03

Bonjour et merci beaucoup pour votre aide.

Il y a un nombre-véhicule car les enregistrements ne concernent pas un véhicule unique.

La plupart du temps il s'agit déjà d'un nombre de véhicules par mois (avec une date de fin de mois par exemple)

D'autre part la durée de couverture dans le parc dépend de la durée de la clause lors de l'entrée des véhicule.

Cette durée peux varier pour un contrat. Mais lorsqu'un véhicule rentre dans le parc la durée est celle qui est alors en vigueur, si celle ci change seront affectés uniquement les véhicules arrivés après le changement.

Je vais essayer de partir de vos requêtes en faisant des Sum().

En tout cas merci pour votre aide

Clt,

**bambi12047** · 06/12/2012, 18h51

Bonjour,

En utilisant des sum et avec ces deux requêtes j'arrive bien à trouver le parc du mois et de l'année en cours.

Je voudrais pouvoir afficher ce résultat pour tous les mois de chaque année en remontant sur cinq ans en arrière et 5 ans en avant (si l'ont dispose de prévision de vente de véhicule). Je ne vois pas comment faire sans devoir faire autant de requête que de mois à considérer soit 12 mois * 11 année.

Le résultat de ce parc pourrait être visualisé dans un état par exemple qui aurait la forme de l'image transmis en pièce jointe.

Merci pour votre aide.

**Nanou1972** · 04/02/2013, 12h46

Bonjour Bambi 12047,
est ce que tu as eu réponse à ta requête?
Fais-nous signe
Merci

**bambi12047** · 11/02/2013, 15h39

bonjour,

non je n'ai toujours pas trouvé la solution car je pense qu'il me faut travailler en sql et je suis débutant dans ce domaine.

Toute aide serait la bienvenue.

Merci

**dumas.blr** · 11/02/2013, 17h46

Bonjour bambi12047,

Je pense avoir une idée, un peu compliquée peut-être à mettre en oeuvre, mais qui te permettrait une restitution générique de tes données.
L'idée : associer le statut de tes véhicules sur un mois précis en fonction de la date dans le calendrier, de la date de début de ton contrat, et le la durée dans la clause.
Pour cela, il te faudrait :
- un calendrier mensuel
- une fonction, qui, en fonction du contrat et de la clause, te dit si la voiture est dans le parc pour le mois donné. Cette fonction peut-être développée facilement en VBA, et intégrable dans une requête. la valeur retournée serait la suivante : 0,5 si mois du début du contrat, 1 durant le contrat hors premier mois, 0 si le mois est > à début contrat + durée

Qu'en penses-tu ?

**bambi12047** · 12/02/2013, 09h42

Bonjour dumas.blr,

En effet cela me semble etre une très bonne idée mais qu'entends tu par un calendrier mensuel ? Enfin sous quelle forme sera-t-il généré? une requête? un formulaire?

En tout cas dans l'idée c'est bien cela !!

Merci beaucoup

**dumas.blr** · 12/02/2013, 10h05

Bonjour bambi12047,
Pour le calendrier, ce n'est pas compliqué.
Le plus simple est de créer par ex. une table t_annee contenant les années que tu veux suivre, une table t_mois contenant les mois de 1 à 12 (en chiffre ou en lettre, as you want), et une requête qui te génère le produit cartésien (c'est à dire prendre les 2 tables, sans faire de jointure et prendre les champs de chaque table), que tu pourra éventuellement mettre dans une table en dur.
par contre, je ne comprends pas bien la date de début qui est dans la table clause, et celle qui est dans la table contrat. C'est bien la date de début du contrat qui détermine l'arrivée dans le parc ?

**dumas.blr** · 12/02/2013, 14h23

Bonjour bambi12047,

J'ai fait un petit prototype à partir des informations que tu nous à communiqués, que je joins dans le post.

Il contient :

les tables "contrat", "clause" et "véhicule vendus"
2 tables "tbl_annee" et "tbl_mois"
5 requetes dont les 3 principales sont "calendrier", "extr_vente" et "Parc_vehicule_detail"
1 module "calcul"

En détail :

dans la table "contrat", j'ai affecté un n° séquentiel pour le code contrat
dans la table "clause", j'ai affecté un n° séquentiel pour l'ID contrat (qui ne sert à rien pour moi), le code contrat étant celui dans la table "contrat"; la date de début ne me sert à rien non plus.
dans la table "véhicule vendus", j'ai affecté un n° séquentiel pour Cle_vehicule , le code contrat étant celui dans la table "contrat"; la date de début sert de calcul pour connaitre le parc à date; la durée est exprimée en année.
la requête "calendrier" sert à générer le calendrier d'observation, et est basée sur les 2 tables "tbl_annee" et "tbl_mois"
Le module "calcul" contient la fonction "dans_parc" qui donne le coefficient du parc pour une cle_vehicule dans le mois (0, 0.5 ou 1), j'ai affecté arbitrairement la valeur 0.5 pour le premier mois de début et pour le dernier mois de fin.
la requête "extr_vente" sert à extraire les informations pertinentes des 3 tables "contrat", "clause" et "véhicule vendus"
la requête "Parc_vehicule_detail" est la requête la plus importante. Elle calcule pour chaque occurence du calendrier le parc présent pour chaque cle_vehicule et utilise la fonction "dans_parc".
Les 2 autres requêtes "Parc_mensuel" et "Parc_vehicule_detail_Analyse croisée" ne sont qu'une restitution (agrégée mensuelle et requête croisée) de la requête "Parc_vehicule_detail"

Je pense que ce prototype devrait correspondre à ce que tu recherches

Fais nous part de ton retour.

A+