Discussion :

[Gorzyne]

Bonjour,

Je cherche à rapprocher deux tables mais de façon "floue" c'est à dire sans égalité parfaite
Pourriez vous m'aider sur la marche à suivre pour ce type de comparaison ?


J'envisage de calculer une distance entre les deux bases (type Levenstein) ou utiliser de l'expression régulière .pattern

Mais pour ça je peux pas faire de croisement standard égalité (JOIN)
En fait c'est comme ci je devais moi même une sorte de pseudo "JOIN" fait maison

Donc en partant du principe que j'ai une fonction JOINmaison(a,b,c,d), avec mes arguments a,b,c,d allant chercher dans deux tables tbl1 et tbl2, comment je peux enrober tout ça dans du code pour que ça croise
y-a-t-il plusieurs façon de faire ? la plus facile ? la plus rapide ?

Je demande pas qu'on m'aide à écrire la fonction, juste qu'on m'explique comment utiliser ma fonction dans un environnement Access, désolé je viens plutôt d'Excel et j'ai l'habitude juste de boucler sur ce qui m'intéresse mais j'ai l'impression qu'en Access c'est pas tout à fait la même syntaxe

Cdt,
Gorz

[marot_r]

Bonjour.

Si tu n'as pas un gros volume de données tu peux essayer avec un proudit croisé qui va te générer la matrice T1 X T2.

Code sql :

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

select [t1].*, [t2].*, Resultat as TaFonction([T1].[TonChamp], [T2.TonChamp]) where TaFonction([T1].[TonChamp], [T2.TonChamp])<=UneValeur

Évidement cela monte très vite donc à manier avec précaution car on tombe rapidement en dépassement de capacité de la BD (max 2 Go).

La solution que j'ai faite une fois consiste à faire la jointure par programme VBA et de traiter le resultat immédiatement.

L'algorithme est le suivant :

Code :

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

1
2
3
4
5
6
7
pour chacun des enregistrements de T1
   pour chacun des enregistrements de T2
     si TaFonction([T1].[TonChamp], [T2].[TonChamp]) <=Une valeur
        'Faire quelque chose avec ce cas
     fin si
  fin pour
fin pour.

Cela peut être TRÉÉÉÉÉS long mais comme tu traites 2 enregistrements à la fois donc tu ne satures pas ta BD.

Si tu as une clef primaire pour chacune des tables pour le résultat tu peux faire un truc du genre :

Avec un seul match possible dans l'autre table :

Table T1
Tes champs T1
ClefT2

Table T2
Tes champs T2
ClefT1

Avec plusieurs matchs possibles :

Table Match
ClefT1
ClefT2

A+

[Gorzyne]

ok merci ta deuxième solution est exactement ce que je veux faire... mais c'est là le point: comment je fais "pour chacun des enregistrements" et pour aller taper les bons fields... j'ai l'impression qu'il n'y a pas d'incrément ni de for each possible, qu'il faut passer par une sorte de .movenext (je viens d'Excel)

ça sera long mais ça prendra le temps qu'il faut tant pis si je dois laisser tourner un ordi la nuit

[marot_r]

Bonjour, voici un exemple de code qui fait les boucles à adapter à ton besoin.

Je vais supposer que tu utilises la soluion N x N pour la jointure et que tu utilises 2 champs pour ta jointure
Si tu as plus de 2 champs laisse-le moi savoir, on peut parcourrir la listes des champs avec un for each.

Table tblJointure
Clef (Autonum, ce champ n'est pas obligtaoire mais j'ai au fil des annéées découvert que c'était pratique d'avoir une clef primaire sur toutes les tables même si au départ on ne s'en sert pas.)
ClefT1
ClefT2

Avec un index unique composé de ClefT1 et ClefT2 pour s'assurer de ne pas mettre 2 fois la même jointure.
En relation avec tblT1 sur ClefT1 pour s'assurer que la clef appartient aux valeurs autorisées
En relation avec tblT2 sur ClefT2 pour s'assurer que la clef appartient aux valeurs autorisées

L'index et les relations ne sont pas absolument indipensables dans ce cas mais là aussi parfois on a besoin plus tard et les créer à postériori peut s'avérer assez pénible alors que le faire à la création ne prend que qelques secondes.

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
private sub JoindreTable()
   dim db as dao.database:set db=currentdb
   dim rT1 as dao.recordset:set rT1=db.openRecordset("tbl1")
   dim rT2 as dao.recordset:set rT2=db.openRecordset("tbl2")
   dim rJointure as dao.recordets : set rJointure=db.openRecordset("tblJointure") 'penser à vider tblJointure avant de commencer.
 
   do while not rT1.eof 'Parcours la table 1
      rT2.movefisrt
 
      do while not rT2.eof 'Parcours la table 2
 
          if estJoint(rT1![TonChamp1], rT1![TonChamp2], rT2![TonChamp1], rT2![TonChamp2]) then
              'Ajoute les 2 clefs à la table des résultats
              rJointure.addnew
              rJointure![ClefT1]=rT1![Clef]
              rJointure![ClefT2]=rT2![Clef]
              rJointure.update
          end if
 
          rT2.movenext
      loop
 
      rT1.movenext
   loop
 
   rT2.close:set rT2=nothing 'Ferme et libère explicitement la mémoir utilisée par l'objet
   rT1.close:set rT1=nothing 'Ferme et libère explicitement la mémoir utilisée par l'objet
   db.close:set db=nothing 'Ferme et libère explicitement la mémoir utilisée par l'objet
end sub

Code :

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

1
2
3
4
5
6
7
private function estJoint(prmChampT11 as variant, prmChampT12 as variant, prmChampT21 as variant, prmChampT22 as variant) as boolean
   dim result as boolean
 
   ' ici du code qui compare les champs des 2 tables et fini par result=true si ils sont joints et par result=false si ils ne sont pas joints.
 
   estJoint=result
end function

Pour améliorer les performances et selon les données on peut peut-être ne pas commencer au début de T2 et ne pas aller jusqu'au dernier enregistrement de T2. Cela dépend de tes critères pour la jointure.

A+

[Gorzyne]

Génial meci beaucoup je pense essayer courant de semaine prochaine
pour répondre à ta question oui je vais faire du join multichamp, ça sera genre 4champS à comparer de chaque côté

Gorz

[marot_r]

OK dans ce cas il faudrait peut-être mieux faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
private function estJoint(prmRT1 as dao.recordset, prmRT2 as dao.recordset) as boolean
   dim result as boolean
 
   ' ici du code qui compare les champs des 2 tables et fini par result=true si ils sont joints et par result=false si ils ne sont pas joints.
   '  ex : 
   if prmRT1![Champ1]=prmRT2![Champ1] and prmRT1![Champ2]=prmRT2![Champ2] and prmRT1![Champ3]=prmRT2![Champ3] and prmRT1![Champ4]=prmRT2![Champ4] then
         result=true
       else
         result=false
   end if
 
   estJoint=result
end function

l'appel devite :

Code :

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

estJoint(rT1, rT2)

[Gorzyne]

bon j'ai commencé à tester mais.. ouch ça va à deux à l'heure, 2h de calcul à la rigueur, mais pas 2 jours, enfin pas pour ce que j'ai à faire ici
par contre ta deuxième méthode a l'air encore plus lente que la première (d'appeler la fonction avec le recordset directement)
et sinon y a pas moyen de passer par un array je pense que c'est beaucoup plus rapide ? je sais que j'avais fait ça sous Excel en passant par des arrays ça m'avait divisé mon temps par 10

[marot_r]

Désolé je ne sais pas ce que sont les array en Excel donc impossible de juger.

Combien de lignes as-tu à marrier ?

Si tu es dans les capacités de EXcel, peut-être devrais-tu passer par là.

A+

[Gorzyne]

Array c'est un tableau. Sous Excel c'est beaucoup plus rapide de charger un tableau en mémoire sous forme d'Array plutôt que de faire référence aux données dans les feuilles de calcul. L'accès aux données, par exemple avec .find est beaucoup plus lent qu'un simple parcours du tableau. Après la limite c'est la capacité à stocker en mémoire vive l'intégralité des données.

Dans le cas présent je suis sur 200k x 300k
En requête Access j'arrive à faire tourner une sorte de jointure avec "Comme" dans un temps très acceptable, peut être est il possible d'intégrer une fonction vba dans une requête SQL?

[marot_r]

Dans le cas présent je suis sur 200k x 300k

si c'est bien 200 000 x 300 000, ça me parait un peu gros pour une tableau en mémoire.

Quel est la longeur de ton enregistrement ou des données utilisées ?

Pour information, tu peux intégrer une fonction VBA dans une requête SQL. Il suffit de la déclarer comme "public".

Il est clair que mon algorithme est "de base" mais difficile de faire mieux sans connaître tes données.

Peut-être peux-tu réduire le nombre de lectures en te basant sur un interval ou un tri.

Si tu travailles en réseau, essaye de rappatrier tes données en local ça peut aider.

Sinon tu peux sans doute passer par Excel :

1. Tu exporte tes données dans Excel.
2. Tu marie les données dans Excel.
3. Tu importe le résultat du mariage.

Car, oui malgré toutes ses qualités, Access n'est pas pas la solution à tout :-).

A+

[Gorzyne]

J'ai en gros 3/4 champs sur lequel je dois travailler dans chaque base

sur 2 des champs je peux faire une requête JOIN, genre un n° de contrat et une date
sur les 1 ou 2 autres champs, je me débrouille en rajoutant un critère <Comme "*" & champ2 & "*"> ce qui est déjà pas mal, mais par exemple je voudrais également détecter un caractère de substitution, pour ça je compte recycler une fonction de distance (distance de Levenstein). Du coup au lieu de mon critère comme je voudrais avoir un truc genre <Distancelev(champ1,champ2)< 0.6742>

Je comprends que c'est faisable en mettant ma fonction en "public"

après je voudrais également comparer uniquement l'année de la date...
est ce qu'il y a une grosse perte de temps en passant d'un join sur deux dates, à un critère d'égalité des années de ces mêmes dates ?

[marot_r]

est ce qu'il y a une grosse perte de temps en passant d'un join sur deux dates, à un critère d'égalité des années de ces mêmes dates ?

Je ne sais pas mais j'imagine que oui car il va y avoir un calcul de plus.

Personnellement j'irai vers un "écrémage" en cascade :

• Faire les jointures que tu peux faire et mettre le résultat dans une table intermédiaire.
• Repartir de la table intermédiaire est appliquer les autres critères et stocker le résultat dans une nouvelle table intermédiaire.
• Et ainsi de suite jusqu'à obtention du résultat voulu.

Utiliser des tables intermédiaires plutôt que des chaînes de requêtes tend à améliorer les performances.

Attention à la taille de ta BD. La taille max est 2 Go est Access a tendandce à envoyer des messages bizares et sans aucun rapport avec la saturation lorsqu'il atteint cette limite.

Une parade consiste à stocker tes donnees dans des bases externes afin de toujours rester en dessous des 2 Go.

Attention aussi à bien vider tes tables intermédiaires avant de les utliser pour stocker tes résultats.

A+

[Gorzyne]

ouep pour l'instant la requête avec du 550k x 300k s'execute dans un temps raisonnable sur 4 JOIN, genre 5minutes max, il faut dire qu'en sortie on obtient quelques milliers de correspondances seulement, ça doit jouer dans le temps de calcul;
je vais déja essayer sur une sous base cette idée de mettre une public function dans un critère, si ça marche je suis prêt à laisser tourner un ordi même quelques heures, mon idée serait d'inclure une fonction de croyance au lieu du JOIN, c'est à dire une probabilité de jointure et ensuite fixer le seuil acceptable;