Discussion :

[stefposs]

Salut à tous,

bon voilà j'ai fait une requête avec du not like à foison pour isoler tous les enregistrements d'une table qui ne ressembleraient pas à des noms de rues (d'avenue, de boulevard, de sentiers...)... Ce qui forcément génère un grand nombre de cas possibles.
Donc quand je lance la requête, Access me dit "Query is Too Complex".
Cela doit être du à un trop grand nombre de "and not like..."
Comment faire pour contourner le problème élégamment ?

Est-ce que je pourrais créer une nouvelle table contenant tous les cas que je voudrais isoler de TABLE ? Quelle requête faire alors ?

Merci de votre aide

Voici la requête :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
UPDATE TABLE SET TABLE.TYPE_V = "LD"
WHERE (TABLE.[TYPE_V] Is Null
AND ((TABLE.[STREET]) Like "LD*"
OR (TABLE.[STREET]) Like "LIEU DIT*"
OR (TABLE.[STREET]) Not Like "1E*"
AND (TABLE.[STREET]) Not Like "2E*"
AND (TABLE.[STREET]) Not Like "3E*"
AND (TABLE.[STREET]) Not Like "4E*"
AND (TABLE.[STREET]) Not Like "5E*"
AND (TABLE.[STREET]) Not Like "6E*"
AND (TABLE.[STREET]) Not Like "7E*"
AND (TABLE.[STREET]) Not Like "8E*"
AND (TABLE.[STREET]) Not Like "9E*"
AND (TABLE.[STREET]) Not Like "PREMIER*"
AND (TABLE.[STREET]) Not Like "DEUXIEME*"
AND (TABLE.[STREET]) Not Like "TROISIEME*"
AND (TABLE.[STREET]) Not Like "QUATRIEME*"
AND (TABLE.[STREET]) Not Like "CINQUIEME*"
AND (TABLE.[STREET]) Not Like "SIXIEME*"
AND (TABLE.[STREET]) Not Like "SEPTIEME*"
AND (TABLE.[STREET]) Not Like "HUITIEME*"
AND (TABLE.[STREET]) Not Like "NEUVIEME*"
AND (TABLE.[STREET]) Not Like "DIXIEME*"
AND (TABLE.[STREET]) Not Like "ALLEE*"
AND (TABLE.[STREET]) Not Like "ANCIEN*"
AND (TABLE.[STREET]) Not Like "AVENUE*"
AND (TABLE.[STREET]) Not Like "BOULEVARD*"
AND (TABLE.[STREET]) Not Like "CARREFOUR*"
AND (TABLE.[STREET]) Not Like "CHAUSSEE*"
AND (TABLE.[STREET]) Not Like "CHEMINEMENT*"
AND (TABLE.[STREET]) Not Like "CHEMIN*"
AND (TABLE.[STREET]) Not Like "CITE*"
AND (TABLE.[STREET]) Not Like "CLOS*"
AND (TABLE.[STREET]) Not Like "CONTOUR DE*"
AND (TABLE.[STREET]) Not Like "CORNICHE*"
AND (TABLE.[STREET]) Not Like "COTE*"
AND (TABLE.[STREET]) Not Like "COURS*"
AND (TABLE.[STREET]) Not Like "COUR*"
AND (TABLE.[STREET]) Not Like "DESCENTE*"
AND (TABLE.[STREET]) Not Like "DIGUE*"
AND (TABLE.[STREET]) Not Like "DOMAINE*"
AND (TABLE.[STREET]) Not Like "ESCALIER*"
AND (TABLE.[STREET]) Not Like "ESPACE*"
AND (TABLE.[STREET]) Not Like "ESPLANADE*"
AND (TABLE.[STREET]) Not Like "FAUBOURG*"
AND (TABLE.[STREET]) Not Like "GALERIE*"
AND (TABLE.[STREET]) Not Like "GIRATOIRE*"
AND (TABLE.[STREET]) Not Like "GRAND BOULEVARD*"
AND (TABLE.[STREET]) Not Like "GRAND CHEMIN*"
AND (TABLE.[STREET]) Not Like "GRAND PASSAGE*"
AND (TABLE.[STREET]) Not Like "GRAND PLACE*"
AND (TABLE.[STREET]) Not Like "GRAND RUE*"
AND (TABLE.[STREET]) Not Like "GRANDE RUE*"
AND (TABLE.[STREET]) Not Like "HENT*"
AND (TABLE.[STREET]) Not Like "IMP *"
AND (TABLE.[STREET]) Not Like "IMPASSE*"
AND (TABLE.[STREET]) Not Like "JARDIN*"
AND (TABLE.[STREET]) Not Like "KROAS HENT*"
AND (TABLE.[STREET]) Not Like "KROAZ HENT*"
AND (TABLE.[STREET]) Not Like "KROAZHENT*"
AND (TABLE.[STREET]) Not Like "LE PARC*"
AND (TABLE.[STREET]) Not Like "LES PARC*"
AND (TABLE.[STREET]) Not Like "LEVEE *"
AND (TABLE.[STREET]) Not Like "LOTISSEMENT*"
AND (TABLE.[STREET]) Not Like "LOTISS *"
AND (TABLE.[STREET]) Not Like "LOT *"
AND (TABLE.[STREET]) Not Like "MAIL*"
AND (TABLE.[STREET]) Not Like "MONTEE*"
AND (TABLE.[STREET]) Not Like "NOUVEAU *"
AND (TABLE.[STREET]) Not Like "NOUVELLE *"
AND (TABLE.[STREET]) Not Like "PARC *"
AND (TABLE.[STREET]) Not Like "PAS *"
AND (TABLE.[STREET]) Not Like "PASSAGE*"
AND (TABLE.[STREET]) Not Like "PASSATGE*"
AND (TABLE.[STREET]) Not Like "PASSERELLE*"
AND (TABLE.[STREET]) Not Like "PENTE *"
AND (TABLE.[STREET]) Not Like "PETIT CHEMIN*"
AND (TABLE.[STREET]) Not Like "PETIT SENTIER*"
AND (TABLE.[STREET]) Not Like "PETITE ALLEE*"
AND (TABLE.[STREET]) Not Like "PETITE AVENUE*"
AND (TABLE.[STREET]) Not Like "PETITE COTE*"
AND (TABLE.[STREET]) Not Like "PETITE COUR*"
AND (TABLE.[STREET]) Not Like "PETITE IMPASSE*"
AND (TABLE.[STREET]) Not Like "PETITE PLACE*"
AND (TABLE.[STREET]) Not Like "PETITE ROUTE*"
AND (TABLE.[STREET]) Not Like "PETITE RUELLE*"
AND (TABLE.[STREET]) Not Like "PETITE RUE*"
AND (TABLE.[STREET]) Not Like "PETITE SENTE*"
AND (TABLE.[STREET]) Not Like "PETITE TRACEE*"
AND (TABLE.[STREET]) Not Like "PETITE VOIE*"
AND (TABLE.[STREET]) Not Like "piste*"
AND (TABLE.[STREET]) Not Like "PIAZZA*"
AND (TABLE.[STREET]) Not Like "PL *"
AND (TABLE.[STREET]) Not Like "PLACE *"
AND (TABLE.[STREET]) Not Like "PLACEN *"
AND (TABLE.[STREET]) Not Like "PLACETA *"
AND (TABLE.[STREET]) Not Like "PLACETTA *"
AND (TABLE.[STREET]) Not Like "PLACETO *"
AND (TABLE.[STREET]) Not Like "PLACETTE *"
AND (TABLE.[STREET]) Not Like "PLACIS *"
AND (TABLE.[STREET]) Not Like "PLACIE *"
AND (TABLE.[STREET]) Not Like "PLAN *"
AND (TABLE.[STREET]) Not Like "PONT *"
AND (TABLE.[STREET]) Not Like "PORT *"
AND (TABLE.[STREET]) Not Like "PORTE *"
AND (TABLE.[STREET]) Not Like "PROMENADE*"
AND (TABLE.[STREET]) Not Like "QUAI*"
AND (TABLE.[STREET]) Not Like "RACCOURCI*"
AND (TABLE.[STREET]) Not Like "REMPART*"
AND (TABLE.[STREET]) Not Like "RESIDENCE*"
AND (TABLE.[STREET]) Not Like "RES *"
AND (TABLE.[STREET]) Not Like "ROND POINT*"
AND (TABLE.[STREET]) Not Like "ROUTE*"
AND (TABLE.[STREET]) Not Like "RPT *"
AND (TABLE.[STREET]) Not Like "RTE*"
AND (TABLE.[STREET]) Not Like "RUELLE " & "*"
AND (TABLE.[STREET]) Not Like "RUETTE " & "*"
AND (TABLE.[STREET]) Not Like "RUE " & "*"
AND (TABLE.[STREET]) Not Like "SENTIER " & "*"
AND (TABLE.[STREET]) Not Like "SENTE" & "*"
AND (TABLE.[STREET]) Not Like "SENT" & "*"
AND (TABLE.[STREET]) Not Like "SQUARE" & "*"
AND (TABLE.[STREET]) Not Like "SQ" & "*"
AND (TABLE.[STREET]) Not Like "TRAVEE" & "*"
AND (TABLE.[STREET]) Not Like "TRAVERSE*"
AND (TABLE.[STREET]) Not Like "TRAV*"
AND (TABLE.[STREET]) Not Like "VENELLE*"
AND (TABLE.[STREET]) Not Like "VIEILLE ROUTE*"
AND (TABLE.[STREET]) Not Like "VIEUX CHEMIN*"
AND (TABLE.[STREET]) Not Like "VOIERIE*"
AND (TABLE.[STREET]) Not Like "VOIE*"
AND (TABLE.[STREET]) Not Like "VOI*"));

[random]

tu ajoutes une fonction selon le modèle joint

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Function veriflieu(x As String) As Boolean
If LCase(x) Like ("boulevard*") Then
veriflieu = True
ElseIf LCase(x) Like ("rue*") Then
veriflieu = True
Else
veriflieu = False
End If
End Function

nb il est préférable de mettre les vérifs dans l'ordre des fréquences

ensuite dans ton sql

Code :

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

1
2
3
select monchamp
from matable 
where veriflieu(monchamp) = true (or false)

[stefposs]

Salut et grand merci pour ta fonction qui remplit parfaitement mon besoin :-)

[random]

hum entre temps j'ai trouvé une solution plus souple
crée une table lieux avec une colonne typelieu 1e;2e

et lance

Code :

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

1
2
3
4
5
6
7
 
SELECT matable.street
FROM matable 
WHERE  matable.street    Not In 
(SELECT a.street 
FROM matable as a , lieux
WHERE  typelieu=Mid([street],1,Len([typelieu])));

[stefposs]

Ah ! encore mieux ! :-)

J'avais commencé à chercher du côté d'une table avec le "NOT In" mais je n'arrivais pas à trouver comment reproduire les LIKE/NOT LIKE...

J'avoue que je ne me serai jamais penché sur le MID comme dans ta solution.
Excellent !

Juste une question :

Code :

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

1
2
SELECT a.street 
FROM matable AS a , lieux

"a" correspond à quoi ??

[random]

à un alias

quand une table est utilisé plusieurs fois dans une requête ou qu'elle a un nom stupide on la rebaptise

select a.champ1,b.champ2
from unetableavecunnomàcoucherdehors_ etj'aivupire as a, autre table as b
...

[stefposs]

Ok capito ! :-)