Il est de bon ton de se souhaiter la bonne année. Mais vous, et vous seul, pourrez faire en sorte que cette année soit bonne, meilleure que celle qui vient de s'écouler. Apprenez à ne compter que sur vous, car personne n'est plus qualifié que vous-même pour bâtir, réparer ou améliorer votre propre vie. Personne ne fera les choses à votre place. D'ailleurs, tout ce que les autres peuvent faire, c'est souhaiter que vous le fassiez. Et ne croyez pas que tout ceux qui vous entourent vous apporteront des solutions : certains font juste partie de vos problèmes. Transformez vos résolutions en actes, et dans douze mois, retournez-vous et souriez-vous fièrement : C'était long. C'était difficile. Mais ça y est : 2017 était une bonne année, merci Moi.

Les requêtes

Paramètres des requêtes

Quand on clique sur le bouton droit dans la partie grise de la requête, on peut choisir Paramètres.

Il est alors demandé d'écrire son paramètre : par exemple : "Quelle couleur", et de définir un type de donnée pour ce paramètre. Admettons que nous laissons text.

Pour info, voici un exemple SQL d'une requête simple, pourvue de 2 paramètres :

PARAMETERS [Quelle couleur] Text, [Quelle taille] Short;
SELECT T_Objet.Objet, T_Objet.Objet, T_Objet.Couleur, T_Objet.Taille
FROM T_Objet;

Remarque : Short est en fait un entier, en syntaxe SQL

Quand on lance la requête, ce paramètre nous est demandé, et nous devons entrer une chaîne de caractères, mais que la requête fait-elle avec ce paramètre ? Rien. La requête s'exécute normalement, comme s'il n'y avait pas de paramètre. Ce qui est logique, parce qu'on ne sairait définir ce qui devrait changer. En fait, il faut se référer au DAO, et utiliser la propriété PARAMETERS pour lire ou modifier ce paramètre afin de personnaliser la requête. Celà me paraît compliqué et relativement inutile

Propriétés des requêtes

Pour faire apparaître les propriétés de la requête, il faut cliquer avec le bouton droit de la souris dans la grande partie grise ou on met les tables, et choisir propriétés

Requête sur un autre .MDB

Propriété Base source : Il suffit d'indiquer de quelle base il s'agit

Critères expressions

Exemple

Explications

Dans("Belgique","France") Renvoie tous les enregiostrements France et Belgique
Pas "France" No comment
Année([DateCommande]) > 1994 Enregistrements postérieurs à 1994
[F-H]* Enregistrements commençant par E,F,G ou H
B*D Enregistrements commençant par B et finissant par D (Bernard)
[*]ABC permet d'utiliser l'étoile comme caractère fixte. Renvoie l'enregistrement *ABC
b?n Un seul caractère alphanumérique
No# Un seul chiffre (No1, No2, ...)
[!A-C]* Enregistrements ne commençant pas par A ni B ni C (A tester)
b[ae]lle Renvoie belle et balle
b[a-e]ll balle, bblle, bclle, bdlle, belle
b[!ae]lle Tout sauf balle et belle

Utilisation de fonctions dans l'affichage des champs

Affichage des noms des clients en majuscules

SQL :

SELECT UCase([NomClient]) AS NomEnMajuscule
FROM T_Client;

Correspondance QBE :

Nom du champ :

NomEnMajuscule: Majuscule([NomClient])

Cet autre exemple affiche "Ce client commence par D" dans le cas ou le nom du client commence par la lettre D. Autrement, il affiche simplement le nom du client :

CommenceParD: VraiFaux([NomClient] Comme "D*";"Ce client commence par D";[NomClient])

Possibilité de critères conditionnels

Par exemple, nous voudrions, dans la plage des critères que si X = 1, il faut nous donner tous les clients qui s'appellent Dupont, sinon, il faut tous les afficher :

Like IIf(X=1;"Dupont";"*")

ATTENTION : On ne peut pas demander ainsi conditionnellement les enregistrement qui ne contiennent rien de cette manière :

Like IIf(X=1;"Dupont";"Is Null")

Car il appellerait simplement les cliens qui s'appelleraient Is Null. Et si on enlève les guillemets on a une erreur

Rechercher un champ qui contient un caractère réservé

Par exemple, si on cherche tous les champs qui sont égaux à *, il sera nécessaire d'écrire

=[*]

Voici la liste : * , # , ? , [ (qui devient [[] ) , ] , ! , -

Forcer un titre de colonne élégant

Pour forcer un titre de colonne correct, sans qu'il soit indiqué la formule utilisée, il faut rajouter le titre voulu suivi de 2 points, comme ceci : Total: [Sous-total]+[Port]

Premières valeurs

Il est possibles à l'aide de la propriété de la requête "Premières Valeurs" de n'afficher que les N premiers enregistrements.

Exemple :

Admettons que nous ayons une table "T_Representant", et que cette table contienne les champs "NomRepres" et "ChiffreAffaire". Nous désirons seulement les 3 représentants dont le chiffre d'affaire est le plus élevé :

Si on avait voulu les 10% de l'ensemble des représentants les plus efficaces (en fait, en ignorant le nombre de représentants, on veut les 10% les meilleurs, soit un seul s'ils sont 10, soit 50 s'ils sont 500), il suffit de suivre la même démarche, mais de mettre 10% à la place de 3.

Relations

Lors de la création d'une requête, lors de l'insertion des tables, les relations déjà définies sont montrées. Il est possible au sein d'une requête de créer d'autres relations (mais sans intégrité référentielle, bien sûr). Il existe 2 types de relations

Pour ce 2ème type de relation, il est nécessaire de double-cliquer sur la relation, et de définir le 2ème ou le 3ème cas : Par exemple, si on a 2 tables : Client et Commande : Il s'agit de créer une relation qui permet de visualiser tous les clients, mêmes ceux qui n'ont jamais commandé.

Requête création et modification de définition de table

Création

Il est possible de créer dynamiquement une nouvelle table autrement qu'en passant par un module qui utilise les objets : Un utilisant les particularités de SQL. Il suffit de demander une nouvelle requête, pas de table, mais aller directement dans le code SQL, et écrire le code.

Exemple :

CREATE TABLE Amis
([N° ami] int,
[Nom de famille] text,
[Prénom] text,
[Téléphone] text,
CONSTRAINT [Index1] PRIMARY KEY ([N° ami]));

Modification

Cette requête modifie la table existante Amis, et y ajoute un champ "Salaire", de type Texte (255 par défaut)

ALTER TABLE Amis ADD COLUMN Salaire text;

Requêtes imbriquées :

Exemple 1

Il faut une requête qui affiche tous les articles dont le prix est supérieur à la moyenne des prix.

    1. Créer une requête sur la table Article avec comme unique champ "Prix" et un regroupement "Moyenne" sur ce prix
    2. Copier le code SQL de cette requête
    3. Créer la requête basée sur la même table article, avec tous les champs
    4. Dans le champs Prix, indiquer ">" , ouvrir la parenthèse , Coller le code SQL , fermer la parenthèse, et voilà.

Exemple 2

Voici le code SQL qui affiche tous les clients qui sont plus agés que la moyenne des ages :

SELECT T_Client.Prenom, T_Client.Age

FROM T_Client

WHERE (((T_Client.Age)>(SELECT Avg(T_Client.Age) AS MoyenneDeAge

FROM T_Client;)));

Représentation QBE d'une requête similaire :

Pour qu'une telle requête fonctionne, il est bien évident que la sous requête ne doit renvoyer qu'une valeur. Dans le cas contraire, Access renvoie une erreur explicative

Requête paramétrée

Pour obtenir une requête paramétrée (précédée d'une boîte de dialogue demandant le choix du paramètre)

Exemple :

on veut le résultat d'une certaine requête entre le 01/01/93 et le 31/01/93 : dans la ligne critère de la colonne date, on met :

Entre [Date du début de la période:] Et [Date de la fin de la période:]

Paramétrage avec fonctions

Il est également possible, pour paramétrer une requête d'utiliser des fonctions (prédéfinies ou personnalisées). Par exemple, à la place de [Date du début de la période:], il est possible de créer une fonction personnalisée qui retourne une date et de l'y installer à la même place : Entre SaisieDateDebut Et [Date de fin]

Requête Union

Exemple 1

Admettons 2 tables : Client et Fournisseur. Nous voulons une requête qui affiche certaines données de l'une et l'autre table en une fois, Il faut donc créer une requête Union, qui contiendra les champs dans le même ordre. Ils n'ont pas besoin d'avoir la même orthographe. Ce sera simplement l'orthographe de la première requête de l'Union qui sera retenue.

SELECT [NomFournisseur], [Pays]
FROM [T_Fournisseur]
WHERE Pays = "Suisse"
UNION
SELECT [NomClient], [Country]
FROM [T_Client]
WHERE Country = "Suisse";

Exemple 2

nous désirons dans une seule colonne, l'ensemble des noms et des prénoms d'une table T_Client que voici :

NomClient

Prenom

defawes michel
Sardou michel

Voici le code SQL qui permet de regrouper les deux colonnes dans une seule :

SELECT T_Client.Prenom
FROM T_Client
UNION
SELECT T_Client.NomClient
FROM T_Client;

Voici le résultat :

Prenom

defawes
michel
Sardou

Ajout de valeurs complémentaires dans une liste modifiable

Il est possible d'ajouter certaines valeurs dans une liste modifiable, qui ne se trouvent pas dans la table sous-jacente, grâce à la requête UNION.

IDPersonne

NomPersonne

Prenom

4

allen woody

1

defawes michel

5

delon alain

Pour commencer, voici une requête qui se base sur la table Personne, et qui retourne une valeur littérale, la même pour chaque enregistrement.

Voici le code SQL :

SELECT "Tout le monde" AS LeTitre
FROM Personne;

Et voici le résultat :

LeTitre

Tout le monde
Tout le monde
Tout le monde

Et maintenant, si on lie cette requête à une requête qui affiche tous les noms avec UNION, on obtiendrait ceci :

Code SQL :

SELECT "Tout le monde" AS LeTitre
FROM Personne
UNION
SELECT NomPersonne
FROM Personne;

Resultat :

LeTitre

allen
defawes
delon
Tout le monde

Requêtes analyse croisée

Une requête analyse croisée est l'équivalent du tableau croisé dynamique dans Excel. Elle permet d'obtenir des statistiques par ligne et par colonnes. La seule restriction par rapport à Excel est que dans Access, on ne peut avoir qu'un champ de regroupement par colonne

Une requête analyse croisée peut se fonder sur plusieurs tables

Elle se compose de 3 éléments minimum (3 champs minimum donc également) :

Dans notre exemple :

Dans l'exemple simple suivant, nous avons la table suivante :

IDJouet

Libelle

Sexe

AgeMin

Genre

AvecPile

ResteStock

PU

1

Ours en peluche Fille

66

Poupées et peluches

Non

9

CHF 16.50

2

Train électrique Garçon

8

Divers

Oui

2

CHF 189.00

3

Boomerang Garçon

6

Dînette

Non

20

CHF 12.90

4

assiette Fille

2

Dînette

Non

80

CHF 5.00

5

MarioLand Garçon

8

Jeux Vidéo

Non

4

CHF 89.90

6

Monopoly Mixte

12

Jeux de société

Non

6

CHF 99.00

7

7 familles Mixte

5

Jeux de société

Non

8

CHF 6.75

8

Goldorak Garçon

6

Poupées et peluches

Oui

10

CHF 29.00

9

Barbie à la mer Fille

4

Poupées et peluches

Non

2

CHF 21.00

10

voiture électrique Garçon

6

Divers

Oui

1

CHF 229.00

11

Casino Deluxe Mixte

18

Jeux Vidéo

Non

0

CHF 109.90

12

Casimir Mixte

5

Poupées et peluches

Non

2

CHF 19.90

13

Playmobil Mixte

3

Divers

Non

13

CHF 9.90

14

Voiture télécommandée Garçon

10

Divers

Oui

4

CHF 65.00

15

ballon Mixte

2

Divers

Non

25

CHF 8.60

A partir de là, nous aimerions obtenir la statistique suivante : Combien d'articles nous reste-t-il pour chaque catégorie de jouet, et pour chaque sexe :

Pour ce faire, nous avons besoin de la requête style Analyse Croisée suivante :

Qui est représenté par le code SQL Suivant :

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock
SELECT T_Jouet.Sexe
FROM T_Jouet
GROUP BY T_Jouet.Sexe
PIVOT T_Jouet.Genre;

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock Cette instruction est exclusive à la requête analyse croisée. Le titre de ce champ SommeDeResteStock n'apparaît pas dans le résultat (seulement les données). On peut donc utiliser les fonctions de regroupement habituelles : SUM, COUNT, MAX, MIN, AVG, Etc.
SELECT T_Jouet.Sexe Ce titre apparaître ligne par ligne, autant de fois qu'il existe de sexe. Dans notre exemple, nous aurions peut-être dû mettre le Genre en ligne, pour éviter d'avoit un tableau plus large que haut
FROM T_Jouet Tables de base servant à l'élaboration de la requête. On peut bien entendu avoir plusieurs tables (dans le cas ou par exemple, on désirerait avoir un regroupement des achats des clients par genre d'article, ce qui demanderait une table T_Client, T_Vente, T_VenteDetail et T_Article)
GROUP BY T_Jouet.Sexe Cette ligne permet de créer aurant de colonnes que nécessaires (une colonne par Sexe différent (ici, 3 colonnes : Garçon, Fille, Mixte))
PIVOT T_Jouet.Genre; Ce champ doit être unique (pas question d'avoir PIVOT Genre, AvecPile). C'est la détermination du nombre de colonnes et le titrage de chacune d'entre elle avec un genre différent

Il est possible d'affiner le tableau en demandant plusieurs champs comme en-tête de ligne (mais pas comme en tête de colonne représentées pas PIVOT). Par exemple, si on voulait presque la même statistique, on aurait ceci :

Dans cet exemple, nous avons 2 en-têtes de ligne : Genre et AvecPile (dans l'exemple précédent, Genre était une en-tête de colonne). Nous obtenons donc les articles restants pour chaque genre, et, AvecPile oui ou non. On constate donc que tout n'est pas représenté, simplement parce qu'il existe certains genres d'articles qui n'existent qu'avec ou sans piles (Le genre Dînette n'existe que sans pile, tandis que le genre Poupées et peluches comprend cerzains articles avec et d'autres sans piles)

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock
SELECT T_Jouet.Genre, T_Jouet.PileNecessaire
FROM T_Jouet
GROUP BY Genre, AvecPile
PIVOT T_Jouet.Sexe;

Le GROUP BY contient donc autant d'enregistrement que souhaité

Problème des noms de colonnes dynamiques

Comme on peut le constater, les noms de colonnes sont variables : Non seulement leur nom, mais aussi leur nombre (on peut imaginer un jour changer les sexes, et tout a coup avoir comme sexes : Petits garçons, petites filles, mixte, adultes).

Cela ne pose a priori pas de problème dans la requête.

Cela ne poserait pas de problème non plus dans un simple formulaire.

Mais dans le cas ou cette requête serait la source d’un sous-formulaire, le problème des champs dynamiques se pose.

Admettons le cas (peu probable, mais il faut bien donner un exemple), que nous concevions un formulaire principal, avec pour seul champ " PileIncluse ", qui serait le champ père dans le formulaire principal et le sous formulaire.

Lors du lancement du formulaire principal, Access va nous renvoyer un message d’erreur qui nous demande de fixer les en-têtes de colonnes dans la requête source (ce qui est compréhensible, puisque comme on a vu, les sexes peuvent changer)

Solution

Aller dans la requête, demander les propriétés de la requête, et dans la propriété En-têtes des colonnes, écrire : "Garçon";"Fille";"Mixte"

Ce qui force les titres des colonnes à devenir statiques

Requêtes regroupement

Ce genre de requête peut tout à fait être appliquée à des fins statistiques. Admettons une table Client, qui contient 2 champs : NomClient et Metier. Nous désirons connaître le nombre de client par métier. Il suffit donc de créer une requête avec regroupement, de demander deux fois le champs Metier, et de demander dans le premier un simple regroupement, et dans le deuxième, le regroupement Compte.

Requêtes actions

Suppression des demandes de confirmation lors de requêtes action

AFFICHAGE/OPTIONS/GENERAL et CONFIRMER LES SUPPRESSIONS et/ou CONFIRMER LES REQUETES ACTIONS

VB : Il suffit d'écrire avant l'appel de la requête :

DoCmd SetWarnings False

ATTENTION : Il FAUT, dès que ce n'est plus utile de déconnecter les Warnings, faire

DoCmd SetWarnings True

Parce que sinon, Access, ne demande plus de confirmations lors de sauvegardes de tables, formulaires, ou autre, MEME EN MODE CREATION !!!

Requête Ajout

Lors d'une requête AJOUT, il s'agit d'ajouter des données depuis une autre table. Il faut et il suffit que les noms des champs correspondent (par exemple, on peut très bien vouloir ajouter une table avec pour unique champ NOM à une autre table avec pour champs NOM et PRENOM). Ca marche même si les champs sources sont définis comme plus longs que les champs-cibles.

ATTENTION : Si, dans une table, on ajoute des données trop grandes, on aura même pas un message d'erreur (juste que des lignes vont être rajoutées)

Requête Suppression

Il s'agit d'une requête sélection dont le résultat sera retiré de la table

Requête Création de table

Le résultat de la requête sera tout simplement exporté vers une nouvelle table dont il faudra définir le nom.

Requête Mise à jour

Une requête Mise A Jour est une requête qui permet de modifier les enregistrements eux-mêmes à l'aide d'une formule et de certains critères. Par exemple, pour ajouter 10% au prix de chaque article "Charcuterie", il faut demander une requête ajout, taper =Charcuterie dans le champ "TypeArticle" et simplement effectuer une requête sélection, et le résultat sera créé dans une nouvelle table

ASTUCE :

Admettons un formulaire facture avec un sous formulaire FactureDetail. Pour mettre à jour automatiquement la réduction de stock la table Article avec les différentes quantités commandées dans le détail du formulaire, au lieu de faire de la programmation compliquée, il suffit de créer une requête de mise à jour comprenant les tables Facture, FactureDetail et Article. On aura besoin des champs IDFacture de facture, Qté de facture detail et ResteStock de Article. Le paramètre de IDFacture doit être le champ IDFacture du formulaire facture, et la mise à jour de ResteStock doit être [ResteStock]-[Qte].