[ACCESS] requete sur un champ avec combinaison

**digitman** · 08/06/2007, 23h32

Bonsoir,
je suis un débutant dans access (j'utilise Access 2003)
bon j'explique mon problème
j'ai une table de prénoms réference qui contient une liste de prénoms que j'ai préparé
Exemple :
ANABELLE
DENISE
MICHEL
FRANCIS
CHRYSTELLE
PIERRE
DANIELLE
LAURENT

et j'ai une base de clients sur laquelle je dois selectionner les clients dont le prenom existe dans ma base de référence y compris les prenoms de couples
Exemple de liste de clients que je souhaite chercher :
ANABELLE
DENISE ET MICHEL
CHRYSTELLE ET FRANCIS
PIERRE
LAURENT

Voici la requete que j'ai realisé :
SELECTclient.prenom
FROM client, Prenoms
WHERE (([client]![prenom]=[Prenoms]![prenom]));

- la table client c'est la table qui contient la liste de mes clients
- la table Prenoms c'est la table qui contient la liste de mes prénoms référence
le problème c'est que ma requete me donne que la liste de prénoms simples c'est a dire qu'il me donne pas les noms des couples
Resultat :
ANABELLE
PIERRE
LAURENT

et la requete m'a fait perdre les enregistrements suivants :
DENISE ET MICHEL
CHRYSTELLE ET FRANCIS

j'espere bien trouver bien une solution à ce problème
merci d'avance

**philben** · 09/06/2007, 08h23

bonjour,

Une solution consiste à utiliser l'opérateur LIKE :

Code :

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

1
2
3
4
SELECT Distinct client.prenom
FROM client, Prenoms
WHERE (([client]![prenom] like "*" & [Prenoms]![prenom] & "*"));

n'est pas adaptée si vous avez beaucoup d'enregistrements car surement lente.

cordialement,

Philippe

**digitman** · 09/06/2007, 11h35

merci bien pour votre réponse mais justement j'ai beaucoups d'enregistrements que ce soit dans la table client et dans la table prenoms de reference

**philben** · 09/06/2007, 11h37

bonjour,

a tester quand même, la notion de vitesse est toute relative !

Philippe

**digitman** · 09/06/2007, 11h40

oui je suis en train de le tester en ce moment et la requete tourne depuis 5 mn et ça n'a rien donné

**philben** · 09/06/2007, 12h08

plus rapide c'est plus cher

et il faut répondre à quelques questions :

1) C'est pour un traitement unique ou régulier ?
2) l'utilisateur attend la réponse ?
3) y'a t'il plus de 2 prénoms ?
4) le motif de séparation des prénoms est-il toujours < Et > ?
5) nb enregistrements de la table client ?
6) nb enregistrements de la table prénoms ?

cordialement,

philippe

**digitman** · 09/06/2007, 12h15

voici les reponses
1) C'est pour un traitement unique ou régulier ? = Usage regulier
2) l'utilisateur attend la réponse ? = OUI
3) y'a t'il plus de 2 prénoms ? = dans certains cas
4) le motif de séparation des prénoms est-il toujours < Et > ? = OUI
5) nb enregistrements de la table client ? = variable mais toujours plus que 20000 Enregistrement
6) nb enregistrements de la table prénoms ? = entre 7000 et 10000

merci bien

**philben** · 09/06/2007, 19h16

re bonjour,

désolé, j'ai pas pu plus rapide qu'une dizaine de secondes pour 2 prénoms maxi et 20 000 clients et 10 000 prénoms.

cordialement,

Philippe

**digitman** · 09/06/2007, 21h27

oui ça pourra m'aider bien sur pour deux prenoms

merci d'avance

**philben** · 09/06/2007, 22h07

Principe :
Créer les deux fonctions suivantes dans un module standard :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Public Function FirstPrenom(sSrc As String) As String
   Dim n As Integer
   n = InStr(sSrc, " Et ")
   If n Then
      FirstPrenom = Left$(sSrc, n - 1)
   Else
      FirstPrenom = sSrc
   End If
End Function
 
Public Function SecondPrenom(sSrc As String) As String
   Dim n As Integer
   n = InStr(sSrc, " Et ")
   If n Then SecondPrenom = Mid$(sSrc, n + 4)
End Function

Pour des questions de performance, j'ai considéré que la colonne <Prénom> de <Clients> contient toujours un prénom et donc n'est jamais NULL (Si c'est le cas, il suffit de modifier en conséquence les fonctions ci-dessus).

Voici le requête (non éditable dans le QBE mais qu'en mode SQL) :

Code :

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

1
2
3
4
5
6
 
SELECT  clients.prenom
FROM (clients LEFT JOIN prenoms p1 ON 
FirstPrenom(clients.prenom)=p1.prenom) 
LEFT JOIN prenoms p2 ON SecondPrenom(clients.prenom)=p2.prenom
WHERE p1.prenom is not null or P2.prenom is not null;

Important : Il faut indexer sans doublon la colonne <prenom> de la table <prenoms>.

cordialement,

Philippe

**digitman** · 09/06/2007, 22h57

merci bien pour votre aide je testerai cette solution lundi
merci encore une fois

**philben** · 10/06/2007, 19h56

bonjour,

un petit exercice pour un accès encore plus rapide (< 2 secondes pour pouvoir accèder au dernier enregistrement) dans un formulaire lié :
-> Placer directement les 2 fonctions VBA dans la requete SQL.

cordialement,

philippe

**digitman** · 11/06/2007, 09h41

bonjour
voici l'erreur que j'ai eu

à noter que le champs prenom peut etre composé d'un seul prenom

**philben** · 11/06/2007, 18h49

bonjour,

car la colonne prenom de client contient des valeurs NULL (vide).

donc voir mon message de 22H07 :

Pour des questions de performance, j'ai considéré que la colonne <Prénom> de <Clients> contient toujours un prénom et donc n'est jamais NULL (Si c'est le cas, il suffit de modifier en conséquence les fonctions ci-dessus).

il suffit de passer un variant aux fonctions, retourner un variant et utiliser des fonctions sans $.

cordialement,

philippe

**digitman** · 11/06/2007, 21h02

aie ça commence a etre trop difficile pour moi je crois

dsl philben je crois que j'ai pas compris
j'ai enlvé les $ dans les fonctions que vous m'avez donné mais tjrs la meme erreur

**vodiem** · 12/06/2007, 02h19

si je comprends bien tu cherches les clients qui appartiennent à la liste de tes prénoms.
ta liste clients peut contenir 2 prénoms dans ce cas lié par "ET"

voici ma proposition (tout en requete):
1. constituer la liste des prénoms contenu dans la liste client (identifié par leur ID)
trois requetes sont nécessaire: le principe est simple une requete pour extraire le premier prénom avant le "ET" une requete pour extraire le second prénom après le "ET" en supprimant les champs "" et le cas null puis une troisième requete d'union des deux requetes précédente.
les tables utilisés avec leur champs respectifs:
"Clients": [ID], [PRENOMS CLIENT]
"Prénoms": [PRENOM]

Requete 1: "Premier Prénom"=

Code :

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

1
2
3
SELECT IIf(IsNull([PRENOMS CLIENT]),"",Left([PRENOMS CLIENT],IIf(InStr([PRENOMS CLIENT],"ET")=0,Len([PRENOMS CLIENT]),InStr([PRENOMS CLIENT],"ET")-1))) AS PRENOM, Clients.ID
FROM Clients
WHERE (((IIf(IsNull([PRENOMS CLIENT]),"",Left([PRENOMS CLIENT],IIf(InStr([PRENOMS CLIENT],"ET")=0,Len([PRENOMS CLIENT]),InStr([PRENOMS CLIENT],"ET")-1))))<>""));

Requete 2: "Second Prénom"=

Code :

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

1
2
3
SELECT IIf(IsNull([PRENOMS CLIENT]),"",Right([PRENOMS CLIENT],IIf(InStr([PRENOMS CLIENT],"ET")=0,0,Len([PRENOMS CLIENT])-InStr([PRENOMS CLIENT],"ET")-2))) AS PRENOM, Clients.ID
FROM Clients
WHERE (((IIf(IsNull([PRENOMS CLIENT]),"",Right([PRENOMS CLIENT],IIf(InStr([PRENOMS CLIENT],"ET")=0,0,Len([PRENOMS CLIENT])-InStr([PRENOMS CLIENT],"ET")-2))))<>""));

Requete 3: "Liste Prénoms"=

Code :

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

1
2
SELECT [Premier Prénom].PRENOM,[Premier Prénom].ID FROM [Premier Prénom]
UNION SELECT [Second Prénom].PRENOM,[Second Prénom].ID FROM [Second Prénom];

2. vérifier que la "Liste Prénoms" (liste des prénoms présent dans "clients") soit dans la table "Prénoms" (prénoms référence)
j'utilise pour cela la fonction 'DlookUp' et je retire les enregistrements "" (vide => cas ou prénom dans liste client n'est pas dans liste prénom référence)
Requete 4: "Résultat"=

Code :

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

1
2
3
SELECT DLookUp("[PRENOM]","Prénoms","[PRENOM]='"+[PRENOM]+"'") AS Résultat, [Liste Prénoms].ID
FROM [Liste Prénoms]
WHERE (((DLookUp("[PRENOM]","Prénoms","[PRENOM]='"+[PRENOM]+"'"))<>""));

il reste à adapter le "Résultat": l'essentiel étant l'ID des clients présent dans la liste, il faudrait conserver uniquement l'ID et rajouter un groupement dessus.

je ne sais pas si c'est suffisamment rapide pour tes besoins.

autrement: philben disait aussi de remplacer les types dans les fonctions:

Code :

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

Public FUNCTION FirstPrenom(sSrc AS String) AS String

devient:

Code :

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

Public FUNCTION FirstPrenom(sSrc AS Variant) AS Variant

et rajouter un test si sSrc est null par exemple:

Code :

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

1
2
3
4
5
6
Public Function FirstPrenom(sSrc As Variant) As Variant
   If IsNull(sSrc) Then
        FirstPrenom = ""
        Exit Function
   End If
...

bon courage!

**digitman** · 12/06/2007, 14h17

je crois que je suis pas fait pour access

merci les gars pour vos efforts
je cederai l'operation a un des collègues

**philben** · 12/06/2007, 20h24

bonjour,

pour faire simple et rapide, y'a plus besoin des fonctions VBA avec cette requete puisque le code est contenu dans la requete.
Fonctionne même avec des valeurs nulles dans la colonne <prenom> de <client>.

Code :

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

1
2
3
4
5
6
 
SELECT p.prenom
FROM (clients AS p LEFT JOIN prenoms AS p1 ON 
IIf(InStr(p.prenom," Et "),Left$(p.prenom,InStr(p.prenom," Et ")-1),p.prenom)=p1.prenom) 
LEFT JOIN prenoms AS p2 ON IIf(InStr(p.prenom," Et "),Mid$(p.prenom,InStr(p.prenom," Et ")+4))=p2.prenom
WHERE Not (p1.prenom Is Null And p2.prenom Is Null);

Cordialement,

Mod : Pourquoi [DELESTAGE] ?

Philippe

**vodiem** · 12/06/2007, 22h27

oui, bravo philippe

c'est ce que je voulais faire au départ mais je n'avais pas réussi à faire la double jonction sans perdre des données et je n'ai pas cherché plus loin car je me suis dit finalement que cela devait ralentir à charger deux fois la table pour les jonctions et en découpant ca aide aussi a mieux comprendre pour les débutants.

bien que c'est moins effrayant une seul requete, je suis pas sur que cela convaincra digitman de se réconcilier avec access.

enfin philben tu n'as pas fait ca pour rien; c'est une discution interessante et qui ne manquera pas à servir à d'autre.
ce serait dommage que cela passe en délestage.

**philben** · 12/06/2007, 23h50

bonjour Vodiem,

Tu as raison, la voie du découpage en sous-requetes est plus conventionnelle et plus pédagogique et je n'avais pas pensé à l'union...
1 partout, requête au centre !

Pour finir, voici une requete pour 3 prénoms qui permet de boucler la boucle, car en extrapolant la dernière jointure, on peut traiter 4, 5, etc... prénoms :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT p.prenom
FROM ((clients AS p LEFT JOIN prenoms AS p1 ON 
IIf(InStr(p.prenom," Et "),
Left$(p.prenom,InStr(p.prenom," Et ")-1),p.prenom)=p1.prenom)
 LEFT JOIN prenoms AS p2 ON 
IIf(InStr(p.prenom," Et "),
Mid$(p.prenom,InStr(p.prenom," Et ")+4))=p2.prenom) LEFT JOIN prenoms as P3 ON 
IIf(p.prenom Like "* Et * Et *",
Mid$(p.prenom,InStrRev(p.prenom," Et ")+4))=p3.prenom
WHERE Not (p1.prenom Is Null And p2.prenom Is Null And 
p3.prenom Is Null);

Bon j'espère que DigitMan est toujours là...

cordialement,

Philippe

[ACCESS] requete sur un champ avec combinaison

Requêtes et SQL.

Discussions similaires

Partager

Partager