Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Best Match Suche
zurück: Inhalte zwischen Listboxen verschieben weiter: Access-Versionen und die bedingte Kompilierung Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Tutorial Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
Bitsqueezer
Office-VBA-Programmierer


Verfasst am:
19. Nov 2011, 11:46
Rufname:

Best Match Suche - Best Match Suche

Nach oben
       Version: (keine Angabe möglich)

Hallo zusammen,

die meisten Suchmethoden beinhalten i.d.R. nur die Suche nach einem bestimmten Begriff, der dann entweder irgendo in, am Anfang, am Ende oder genau mit dem Suchtext gefunden werden kann/übereinstimmen muß (so auch mein Suchformular hier (AW: 1. Suchformular in 10 Minuten erstellt)).

Die Methode hier verwendet nun eine VBA-Funktion, um eine Art "Google-Suche" zu simulieren, dabei werden alle Suchwörter in ein Textfeld eingegeben, mit Leerzeichen getrennt, mit beliebiger Groß-/Kleinschreibung. Die VBA-Funktion ist sehr klein und gibt einen Score-Wert zurück, wie oft jedes Suchwort im Suchtext gefunden werden konnte.

Der ganze Code wird in ein Standardmodul geschrieben und als Public definiert, damit SQL darauf zugreifen kann. Der Code beschränkt sich auf das folgende:
Code:
Option Compare Database
Option Explicit

Private lngPoints As Long
Private varWord As Variant
Private strWords() As String
Private varSearch As Variant
Private strSearches() As String

Public Function ValMatch(ByVal strSearchWords As String, ByVal strSearchText As String) As Long
    strWords = Split(strSearchWords, " ")
    strSearches = Split(strSearchText, " ")
    lngPoints = 0
    For Each varWord In strWords
        For Each varSearch In strSearches
            If varSearch Like "*" & varWord & "*" Then lngPoints = lngPoints + 1
        Next varSearch
    Next varWord
    ValMatch = lngPoints
End Function
Die benötigten Variablen und Arrays sind absichtlich auf Modulebene deklariert, da Module sofort instantiiert werden und damit stehen die Variablen immer zur Verfügung. Als Private definiert, damit sie nur im Modul genutzt werden können und nicht mit anderen Variablennamen irgendwo im sonstigen Programm kollidieren. Der Vorteil hierbei ist, da die ValMatch-Funktion u.U. tausende Male in einer Abfrage aufgerufen wird, müssen die Variablen nicht bei jedem Aufruf erst im Speicher neu erstellt werden, sie werden einfach wiederverwendet. Das spart ein wenig Zeit bei der Ausführung.

Erwartungsgemäß dauert die Abfrage natürlich relativ lange, mit zunehmender Anzahl Datensätze und Anzahl Suchwörtern, die man eingibt, entsprechend länger. In der angehängten Beispieldatenbank, die eine Liste von Werkzeugen enthält (entnommen einer Excel-Werkzeugliste von KS-Tools) und ca. 17.000 Datensätze umfaßt, braucht die Ausführung einer Abfrage mit einem Suchwort ca. 20 Sekunden. Das hatte ich bei der Erstellung dieser Funktion auch erwartet. Trotzdem ist das Ergebnis in der angehängten Datenbank sehr schnell, was mich auch etwas verblüfft hat.

Zunächst habe ich die Funktion direkt in eine Abfrage gestellt (qryMatchresult) und dann auch gleich die Sortierung auf den ermittelten Match-Score eingestellt. Durch die zusätzliche Sortierung dauert die Abfrage länger, weil die Funktion einmal bei SELECT und einmal bei ORDER BY aufgerufen wird, auch wenn sie bei SELECT bereits auf den in ORDER BY ermittelten Wert zurückgreifen kann (das cached Access selbst, weil die Funktionen ja übereinstimmen). Trotzdem muß zweimal der Weg zu VBA gegangen werden, was die Ausführung verlangsamt. Also habe ich eine Query "qryMatchresult" geschrieben, die nur auf ein Eingabefeld im Formular den Score für alle Datensätze holt und sonst nichts macht, so daß die Ausführung hier so kurz wie möglich ist. Die umgebende Query "qryResult" benutzt dann die von der ersten Query erstellte Zwischentabelle, um die Werte zu sortieren und auszufiltern, was so sehr viel schneller geht. Trotzdem kommt man um das einmalige Ausführen der Basisquery nicht herum, die nun einmal für jeden Datensatz die VBA-Funktion wenigstens einmal aufrufen muß.

Der nächste Punkt ist das Formular. Der erste Test war natürlich, ein Endlosformular über "qryResult" zu erstellen mit dem benötigten Eingabefeld. Die Abfrage ist erwartungsgemäß langsam, startet aber bereits beim Öffnen des Formulars, was unschön ist, weil der User schon warten muß, obwohl noch nichts eingegeben wurde. Damit es auch optisch besser aussieht, habe ich also ein Endlos-Unterformular auf die Query erstellt, das keine RecordSource erhält und beim Öffnen des Hauptformulars unsichtbar geschaltet ist, so daß das Formular sofort öffnet und nur das Eingabefeld zu sehen ist (außerdem hat das Unterformular immer den Vorteil, daß beim Scrollen der Datensätze kein häßliches Flackern entsteht - warum das bei direktem Hauptformular als Endlosformular flackert, wissen wohl nur die Access-Programmierer...).

Dem Eingabefeld dann noch schnell einen Event mitgegeben, der bei Änderung des Eingabefeldes dann das Unterformular sichtbar macht und die RecordSource setzt, sofern noch nicht geschehen, ansonsten ein Requery auslöst.

Das Erstaunliche an diesem Ergebnis ist, daß Access nun plötzlich die Abfrage in Windeseile durchzuführen in der Lage ist, nichts mehr von 20 Sekunden. Ich denke, ein Teil wird sicher daran liegen, daß eine gespeicherte Abfrage einen Ausführungsplan erstellt, der mit gespeichert wird, so daß eine gespeicherte Abfrage immer sehr schnell ausgeführt wird. Allerdings muß auch die gespeicherte Abfrage jedesmal die VBA-Funktion ausführen, da ja die Eingabewörter immer anders sind. Trotzdem schafft es Access hier irgendwie, die Ergebnisse bedeutend schneller zu präsentieren, im Bereich von 1-2 Sekunden, egal, was man eingibt. Möglich, daß Access die zugrundeliegende Tabelle bei geöffnetem Formular im Speicher cached, um die Daten schneller zu verarbeiten, aber dann müßte wenigstens die erste Abfrage langsamer sein - ist sie aber nicht. Warum also genau Access plötzlich schneller abfragen kann, kann ich nicht genau sagen. Aber wie soll ich sagen: Hauptsache, es funktioniert!...Smile

Jedenfalls kann man mit dieser Methode nun nach mehreren Begriffen in der Suchspalte suchen und findet im Ergebnis die Zeilen mit dem besten Suchergebnis nach oben sortiert. Weitere Sortierung (zum Beispiel nach Artikelnummer oder -name) kann man der Query natürlich jederzeit hinzufügen, könnten allerdings auch wieder Performance kosten.

Das angehängte Beispiel ist sehr simpel gehalten und sollte keine Probleme bereiten, es in beliebige eigene Datenbanken einzubauen und anzupassen. Übrigens: Die Spalte "ArticleName" ist nicht indiziert, möglicherweise wird das ganze sogar noch schneller funktionieren, wenn man den Artikelnamen indiziert.

Viel Spaß beim Experimentieren...

Christian



BestMatch.zip
 Beschreibung:
BestMatch Suche in den Dateiformaten Access 2000,2002,2003,>=2007

Download
 Dateiname:  BestMatch.zip
 Dateigröße:  1.06 MB
 Heruntergeladen:  106 mal

Köbi
Angefressener Amateur


Verfasst am:
19. Nov 2011, 23:42
Rufname:
Wohnort: Schweiz


AW: Best Match Suche - AW: Best Match Suche

Nach oben
       Version: (keine Angabe möglich)

Hallo Christian

Eine überzeugende und leicht verständliche Lösung. Vielen Dank.

Ich habe etwas experimentiert und mir erlaubt, Dein Beispiel etwas zu verändern. Um bei Deinem Beispiel zu bleiben:
- Neu ist "qrySuchgrundlage". Hier werden die nötigen Daten gesammelt und mit den Alias-Namen "ID" und "Suchwert" benannt.
Diese Abfrage muss den individuellen Anforderungen angepasst werden. Wichtig ist, dass die Alias-Namen nicht geändert werden.
Die "qryMatchresult" baut dann auf dieser Abfrage auf.

- Das UFo ist von Anfang an sichtbar und hat als Datenherkunft die "qrySuchgrundlage".

Damit werden zuerst einmal einfach alle DS angezeigt. Das UFo ist von Anfang an sichtbar. Einige Felder sind ausgeblendet.

Wer Deine Lösung in seine eigene DB integrieren möchte, müsste eigentlich nur noch Dein Modul "modSearch" und die Abfragen:
- qryMatchresult
- qryResult
- qrySuchgrundlage
und die beiden Formulare:
- frmMatch
- frmMatch_SF
in die eigene DB importieren.

_________________
Gruss
Köbi



BestMatch02_03_V2.zip
 Beschreibung:

Download
 Dateiname:  BestMatch02_03_V2.zip
 Dateigröße:  809.8 KB
 Heruntergeladen:  113 mal

Bitsqueezer
Office-VBA-Programmierer


Verfasst am:
20. Nov 2011, 02:31
Rufname:

AW: Best Match Suche - AW: Best Match Suche

Nach oben
       Version: (keine Angabe möglich)

Hallo Köbi,

gute Idee! Durch Deine Erweiterung werden mehrere Suchspalten zusammengefaßt zu einer und damit funktioniert die gleiche Suche auch quer über verschiedene Datenspalten.

In Deinen Demodaten sind sogar rund 43.000 Datensätze drin, und interessanterweise verlangsamt sich die Abfrage kein bißchen... dabei hast Du noch nicht einmal einen Primärschlüssel oder sonst einen Index gesetzt.

Gruß

Christian
Willi Wipp
Moderator


Verfasst am:
16. Mai 2012, 06:55
Rufname:
Wohnort: Raum Wiesbaden

AW: Best Match Suche - AW: Best Match Suche

Nach oben
       Version: (keine Angabe möglich)

Hi Alle,

bitte Nachfragen zu diesem Thema im Forum Access Programmierung / VBA
beim Thema Best Match Suche (Nachgefragt) stellen.

_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp

(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)
Bitsqueezer
Office-VBA-Programmierer


Verfasst am:
19. Jun 2012, 21:06
Rufname:


AW: Best Match Suche - AW: Best Match Suche

Nach oben
       Version: (keine Angabe möglich)

Hier noch eine Erweiterung der Best-Match-Suche, bei der eine Art "AND"- bzw. "OR"-Logik angewandt wird. Bisher wurde für jedes eingegebene Wort eine Punktzahl ermittelt, wenn es im Suchstring vorkam ("OR"), mit der zweiten Methode werden nur noch dann Punkte vergeben, wenn alle Suchwörter mindestens einmal vorkommen.

Zusätzlich sind noch diverse Filtervarianten drin, bisher war es nur "CONTAINS", jetzt sind es noch einen ganzen Satz weitere, die allerdings aufgrund der besonderen Art dieser Suche u.U. nicht die erwarteten Ergebnisse liefern. Man sollte dabei aber immer berücksichtigen, daß jedes Wort im Suchstring bewertet wird und nicht, wie bei üblichen Filtern, ein Wort im gesamten Suchstring einmal gesucht wird.

Die Anregung kam von Peter69 aus dem "Nachgefragt"-Thread.

Im Anhang ist die erste Demodatenbank um die neue Variante ergänzt, ohne die Erweiterung von Köbi, die müßte man da noch hineinbringen, wer es haben möchte (das habe ich gerade keine Zeit zu).

Viel Spaß beim Experimentieren.

Christian



BestMatchV3.zip
 Beschreibung:
Bestmatch mit "AND/OR" und diversen Filtervarianten, Format A2000-A2007

Download
 Dateiname:  BestMatchV3.zip
 Dateigröße:  1.15 MB
 Heruntergeladen:  120 mal

Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Access Tabellen & Abfragen: Suche in Access über 3 Tabellen 4 bountyhunter 387 18. Jan 2010, 12:01
bountyhunter Suche in Access über 3 Tabellen
Keine neuen Beiträge Access Tabellen & Abfragen: Suche Datensatz mit dem ältesten Anschaffungsdatum 2 Mareike87 1010 16. Jan 2010, 17:13
Mareike87 Suche Datensatz mit dem ältesten Anschaffungsdatum
Keine neuen Beiträge Access Tabellen & Abfragen: Problem bei SQL Suche bei leeren Feldern im Datensatz 1 KillyvsCibo 1296 04. Dez 2009, 17:32
MissPh! Problem bei SQL Suche bei leeren Feldern im Datensatz
Keine neuen Beiträge Access Tabellen & Abfragen: Suche Access Viewer 2 10IN01 588 23. Jul 2009, 13:19
10IN01 Suche Access Viewer
Keine neuen Beiträge Access Tabellen & Abfragen: Suche den größten gemeinsamen Teiler zweier zahlen 10 VR2008 1091 12. Jul 2009, 18:56
Willi Wipp Suche den größten gemeinsamen Teiler zweier zahlen
Keine neuen Beiträge Access Tabellen & Abfragen: Suche in 2 ähnlichen Tabellen über 1 Suchformular 4 Caym 491 30. Jun 2009, 13:34
Caym Suche in 2 ähnlichen Tabellen über 1 Suchformular
Keine neuen Beiträge Access Tabellen & Abfragen: Suche nach bestimmten Zeichen 3 Gast 6027 25. Jun 2008, 06:49
Gast Suche nach bestimmten Zeichen
Keine neuen Beiträge Access Tabellen & Abfragen: Access 2000 Auswahlabfrage - Suche Kriterien 1 Huje 3122 07. Feb 2008, 17:29
Marmeladenglas Access 2000 Auswahlabfrage - Suche Kriterien
Keine neuen Beiträge Access Tabellen & Abfragen: Suche und Ausgabe 2 jan2303 482 02. Feb 2008, 14:21
jens05 Suche und Ausgabe
Keine neuen Beiträge Access Tabellen & Abfragen: Löschen von Infos in best. Feldern von gewissen Records 4 janbroennimann 390 05. Jun 2007, 11:29
janbroennimann Löschen von Infos in best. Feldern von gewissen Records
Keine neuen Beiträge Access Tabellen & Abfragen: kombinierte Suche für access, excel UND powerpoint? 1 mariechen80 699 05. Jan 2007, 20:08
Nouba kombinierte Suche für access, excel UND powerpoint?
Keine neuen Beiträge Access Tabellen & Abfragen: Datensätze mit zig Variationen (Suche Denkanstoß!) 4 ee547 788 01. Nov 2006, 21:13
Nouba Datensätze mit zig Variationen (Suche Denkanstoß!)
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Microsoft Excel Tricks