Duplikate finden und löschen
veröffentlicht: redmond's inside ACCESS, Ausgabe 06/2001
Welcher Datenbankentwickler kennt dieses Problem nicht: Es müssen Daten aus einer anderen Anwendung importiert werden. Diese Daten enthalten sehr viele Duplikate, die man sehr einfach mittels der Abfrageassistenten zur Duplikatsuche ermitteln und anzeigen lassen kann. Doch das automatische bzw. komplette Entfernen der Duplikate ist anhand dieser Abfrage nicht oder nur sehr zeitintensiv möglich.
Der Abfrage-Assistent zur Duplikatsuche liefert zwei verschiedene Ergebnisse, je nach den gewählten Einstellungen beim Erstellungsvorgang:
- Eine Gruppierungsabfrage, die jeweils den ersten gefundenen Datensatz aller mehrfach vorkommenden Datensätze und die Anzahl seiner Duplikate anzeigt.
- Eine Auswahlabfrage, die alle mehrfach vorkommenden Datensätze anzeigt.
Den erste Fall, die Gruppierungsabfrage, kann man reproduzieren, indem keine zusätzlichen Felder (dritter Schritt des Assistenten) zur Anzeige hinzufügen werden. Dieses Ergebnis kann nicht bearbeitet werden, da in einer Gruppierungsabfrage die Datensätze für die Bearbeitung gesperrt sind. In Abbildung 1 sehen sie das Ergebnis und, dass die Schaltfläche zur Neuanlage eines Datensatzes in der Navigationsleiste deaktiviert ist.
Den zweiten Fall, die Auswahlabfrage, erzeuget man durch die Auswahl zusätzlicher Felder, die im Ergebnis angezeigt werden sollen. Auf diese Weise erhält man nicht nur die Duplikate, sondern alle Datensätze, die mehrfach vorkommen. Dadurch kann deren Inhalt nicht in einem Arbeitsschritt gelöscht werden. In dem Fall hätte man zwar keine Duplikate mehr, allerdings würden auch alle Masterdatensätze gelöscht werden. Die Abbildungen 2 verdeutlicht beide Ergebnisse:
Abbildung 1: Anzeige der Duplikate und deren Anzahl
Abbildung 2: Anzeige aller mehrfach vorkommenden Datensätze
Duplikate mit einer Abfrage löschen
An dieser Stelle soll Ihnen nicht vorenthalten werden, dass es eine Abfrage gibt, die nur einen einzigen der mehrfach vorkommenden Datensätze anzeigt. Trotzdem hat diese Variante gleich mehrere Nachteile. Für Duplikate, die mehr als zweimal vorkommen, muss dies Abfrage dementsprechend mehrfach ausgeführt werden. Außerdem ist die Abfrage so aufwendig für den Computer, dass sie sich für die Bearbeitung großer Datenmengen nicht eignet. Bei dem Versuch die Abfrage auf eine Tabelle mit mehr als 10.000 Datensätzen anzuwenden, musste die Ausführung nach 10 ergebnislosen Minuten per Task-Manager beendet werden.
Trotzdem eignet sich die Abfrage hervorragend für kleinere Datenmengen mit vielen Duplikaten. Die Syntax lautet wie folgt:
SELECT [Tabelle].[IndexFeld]
FROM [Tabelle]
WHERE [Tabelle].[IndexFeld]
In (
SELECT First([Tabelle].[IndexFeld]) AS [Index#]
FROM tbl_Address
GROUP BY [Tabelle].[Feld1], [Tabelle].[Feld2]
HAVING Count(*)>1;
);
Das Ergebnis der Abfrage ist eine Auswahl der ersten gefundenen Datensätze, die mehrfach vorkommen. Alle Datensätze können komplett markiert und mit der Entfernen-Taste gelöscht werden. Danach muss die Abfrage wiederholt werden, bis kein Ergebnis mehr angezeigt wird, dann sind alle Duplikate beseitigt. Auf der Beispiel-CD befindet sich unter anderem auch ein Beispiel zu dieser Abfrage.
Duplikate automatisiert löschen
Um Duplikate komfortabel beziehungsweise automatisiert zu löschen, kommt man nicht drum herum sich intensiv mit der SQL-Syntax auseinander zu setzen oder VBA zur Hilfe zu nehmen. Die Lösungen mittels VBA sind meist sehr zeitaufwendig und besonders spezialisiert auf ein vorliegendes Problem. Allerdings lohnt sich der Aufwand, wenn das Löschen von Duplikaten in einer bestimmten Tabelle eine regelmäßig genutzte Funktion ist. Investiert man noch ein bisschen mehr Energie, kann man eine universell einsetzbare Lösung konstruieren. Wie solch eine Lösung aussehen kann, wird im Folgenden gezeigt.
Das kleine Modul soll so weit gehen, dass man sogenannte Profile zur Elimierung von Duplikaten anlegen und speichern kann. Mit den Einstellungen dieser Profile kann man dann komfortabel experimentieren und sich die jeweiligen Ergebnisse anzeigen lassen. Den Ergebnissen der Abfrageassistenten sieht man von außen oft nur schwer die Eintellungen an, z.B. über welche Feldkombination ein Datensatz eindeutig sein soll.
Die schrittweise Erarbeitung des Konzept würde den Rahmen dieses Artikel sprengen. Aus diesem Grund kann man in Abbildung 1 das fertige Formular der Lösung sehen, anhand dessen die weiteren Schritte erläutert werden.
Abbildung 3: Formular zur Definition der Duplikatabfragen
Durch die Navigationsleiste und dem Feld zur Beschreibung der Duplikatdefinition wird deutlich, dass in diesem Formular mehrere Profile angelegt werden können. Das Feld zur Erfassung der [Ziel-Tabelle] zeigt die Absicht dieses Modul universell einsetzen zu können. Der Inhalt der Kombinationsfelder [Index-Feld] und [zusammengesetzter Index] ergibt sich aus der Auswahl der Ziel-Tabelle. Sie werden zur Laufzeit mit der Feldliste der bezogenen Ziel-Tabelle belegt. Das Feld [Ziel-Tabelle] ist ein Kombinationsfeld, dessen Inhalt von der verwendeten Datenbank abhängt. Listing 1 zeigt zwei Prozeduren, die Erste erzeugt dynamisch, beim Öffnen des Formulars, den Inhalt des Kombinationsfeldes [Ziel-Tabelle]. Da die Inhalte der restlichen Kombinationsfelder von dieser Auswahl abhängig sind, müssen deren Feldlisten, also die Eigenschaft RowSource, nach Änderungen im Feld [Ziel-Tabelle] angepasst werden (siehe 2. Prozedur Listing 1).
Das Kombinationsfeld [Index-Feld] speichert ein Feld mit eindeutigem Inhalt, meist den PrimaryKey beziehungsweise den Primärschlüssel. Ein solches Feld ist für unsere Lösung zwingend erforderlich, es muss jedoch nicht unbedingt der Primärschlüssel sein. Wenn die Tabelle keinen oder einen zusammengesetzten Primärschlüssel hat, muss ein zusätzliches Feld mit einem eindeutigen Index manuell angelegt werden, am Besten vom Datentyp Autowert. Dieses Feld ist deshalb so wichtig, weil nur der erste Datensatz aller Duplikaten angezeigt werden soll. Aus diesem Grund muss ein Datensatz einen eindeutigen Index haben.
Als Grundlage des Einstellungs-Formulars (Abbildung 3) sind folgende zwei Access-Tabellen nötig:
Tabelle 1:Entwurf der Profiltabelle
Feldname |
Beschriftung |
Datentyp |
Länge |
Duplicate# |
Adresse-Nr |
Autowert |
|
Description |
Beschreibung |
Text |
255 |
SourceTable |
Tabelle |
Text |
64 |
IndexField |
Index-Feld |
Text |
64 |
Tabelle 2: Entwurf der Detailtabelle (Unterformular)
Feldname |
Beschriftung |
Datentyp |
Länge |
Index# |
Index |
Autowert |
|
Duplicate# |
Adresse-Nr |
Zahl |
Long Integer |
Field |
Zus. Index |
Text |
64 |
Das Unterformular zur Definition des zusammengesetzten Indexes bezieht sich auf die Duplikatsuche und hat nichts mit dem Index-Feld zu tun. Die Auswahl in Abbildung 1 zum Beispiel bedeutet, dass Duplikate über die Inhalte der Felder Nachname, PLZ und Wohnort gesucht und angezeigt beziehungsweise gelöscht werden sollen. Damit sind nicht Duplikate in jedem einzelnen Feld sondern über die Kombination der Felder gemeint. So zum Beispiel ist es ein Unterschied, ob man über die Kombination PLZ - Wohnort, Nachname - Wohnort, oder nur nach Duplikation im Feld Wohnort sucht. Mit diesen Einstellungen kann man in unserer Lösung bequem experimentieren.
Listing 2 beinhaltet eine in sich abgeschlossene Funktion zur Erstellung einer Auswahlabfrage zur Ermittlung von mehrfach vorkommenden Datensätzen. Die Abfrage wird dynamisch aus den Einstellungen des Formulars beziehungsweise den zugrundeliegenden Access-Tabellen (Tabelle 1 und 2) erzeugt. Diesen Schritt haben beide Schaltflächen [Vorschau] und [Löschen] gemein. Mit dem Unterschied, dass das Ergebnis bei der Vorschau angezeigt wird und beim Löschen als Grundlage für die Löschroutine (Listing 3) weiterverwendet wird. Das Ergebnis liefert die jeweiligen Spalten des zusammengesetzten Indexes und eine zusätzliche Spalte mit eindeutigen Index der Tabelle (siehe Abbildung 4). Jeder Datensatz repräsentiert genau EINE von jeder mehrfach vorkommenden Daten-Kombination. Die Syntax lautet wie folgt:
SELECT First([Address#]) AS [Index#], [LastName], [PostCode], [City]
FROM [tbl_Address]
GROUP BY [LastName], [PostCode], [City]
HAVING Count(*)>1;
Die Angabe der Felder Lastname, Postcode und City im SELECT-Abschnitt des SQL-Befehls sind dabei nur für die Vorschau notwendig. Die Wesentliche Änderung zum SQL-Befehl, der vom Abfrageassistenten erzeugt wird, ist die Verwendung der FIRST-Funktion auf den eindeutigen Index der Tabelle im SELECT-Abschnitt. Dadurch wird nur der erste Datensatz angezeigt, auf den die HAVING-Klausel zutrifft. Die HAVING-Klausel ist im Grunde genommen nichts anderes wie die WEHRE-Klausel, mit dem Unterschied, dass sich WHERE auf Felder des SELECT-Abschnittes beziehen und HAVING auf den GROUP BY-Abschnitt. Dementsprechend bedeutet HAVING Count(*) > 1: alle gruppierten Datensätze, die mehr als ein mal vorkommen, sollen angezeigt werden. Ein Ergebnis dieser Abfrage sehen Sie in Abbildung 4.
Abbildung 4: Ergebnis der modifizierten Duplikatabfrage
Beim Löschvorgang (Listing 3) wird zunächst die Funktion zur Erstellung der modifizierten Duplikatabfrage aufgerufen. Anschließend wird auf dieser Grundlage eine Löschroutine beziehungsweise Löschfunktion ausgeführt, bis alle Datensätze dieser Auflistung gelöscht sind. Datensätze, die mehr als zwei mal vorkommen sind davon allerdings nicht betroffen, deshalb muss die Löschfunktion so oft wiederholt werden, bis die Abfrage zur Anzeige von Duplikaten keine Datensätze mehr beinhaltet.
Die Listings sind so stark kommentiert, dass auf eine detaillierte Erklärung an dieser Stelle verzichtet wird. Alle Listings müssen im Klassenmodul des Hauptformulars platziert sein.
Listing 1: Code beim Öffnen und der Interaktion im Formular
Private Sub Form_Open(Cancel As Integer)
Dim dbs As Database '* DataBaSe
Dim tdf As TableDef '* TablDeF
Dim dmy As String '* DuMmY
dmy = ""
'* Aktuelle Datenbank referenzieren
Set dbs = CurrentDb()
'* Jede Tabelle in der Auflistung durchgehen!
For Each tdf In dbs.TableDefs
'* Wenn keine System-Tabelle (beginnen mit MSYS), ...
If Left(tdf.Name, 4) <> "MSYS" Then
'* Wenn schon Eintrag vorgenommen wurde, ...
If Len(dmy) <> 0 Then
'* ... dann zurerst Semikolon anhängen
dmy = dmy & ";"
End If
'* ... dann an String Tabelle anhängen!
dmy = dmy & tdf.Name
End If
Next
'* Daten-Herkunft für Liste des Feldes [Ziel-Tabelle] setzen
Me![SourceTable].RowSourceType = "Value List"
Me![SourceTable].RowSource = dmy
'* Feldliste zur aktuellen Auswahl [Ziel-Tabellen] einstellen
If Not IsNull(Me![SourceTable]) Then
'* Für Feld [Index-Feld] (eindeutiger Index)
Me![IndexField].RowSourceType = "Field List"
Me![IndexField].RowSource = Me![SourceTable]
'* Für Feld [zusemmengesetzter Index] im Unterformular
Me![sbf_UniqueFields]![Field].RowSourceType = "FieldList"
Me![sbf_UniqueFields]![Field].RowSource = Me![SourceTable]
End If
RunExit:
Set dbs = Nothing
Set tdf = Nothing
End Sub
Private Sub SourceTable_AfterUpdate()
'* Feldliste zur geänderten aktuellen Auswahl [Ziel-Tabellen]
'* neu einstellen.
If Not IsNull(Me![SourceTable]) Then
'* Für Feld [zusemmengesetzter Index] im Unterformular
Me![IndexField].RowSource = Me![SourceTable]
Me![sbf_UniqueFields]![Field].RowSource = Me![SourceTable]
End If
End Sub
Listing 2: Funktionen zur Erstellung und Anzeige der Duplikatabfrage
Private Function Create_SQLDuplicate() As Boolean
On Error GoTo RunError
'* DAO-Objekt-Variablen definieren
Dim dbs As Database
Dim rst As Recordset
'* Hilfs-Variablen definieren
Dim sql As String
Dim v_IndexField As String
Dim v_SourceTable As String
'* Funktion initialisieren
Create_SQLDuplicate = False
'** Plausibilitätskontrolle **
'* Wenn [Ziel-Tabelle] und [Index-Feld] leer, dann Abbruch!
If IsNull(Me![IndexField]) Then GoTo RunExit
If IsNull(Me![SourceTable]) Then GoTo RunExit
'* Database der aktuellen Datenbank erstellen
Set dbs = CurrentDb()
'* SQL-Befehl mit Liste der Felder, dessen
'* Kombination eindeutig sein muss!
'* [zusammengesetzter Index]
sql = ""
sql = sql & "SELECT *"
sql = sql & " FROM"
sql = sql & " [sys_UniqueFields]"
sql = sql & " WHERE"
sql = sql & " [Duplicate#]=" & Me![Duplicate#]
sql = sql & ";"
'* Tabellen mit Feldern des [zusammengesetzter Index] öffnen
Set rst = dbs.OpenRecordset(Name:=sql, _
Type:=dbOpenDynaset)
'* Wenn kein Datensatz für [zusammengesetzten Index]
'* definiert wurde, dann Abbruch!!
If rst.RecordCount = 0 Then GoTo RunExit
'*** SQL-Befehl zur Anzeige doppelter Datensätze erstellen ***
'*************************************************************
sql = ""
'** SELECT-Abschnitt erzeugen
sql = sql & "SELECT"
'* [Index-Feld] (eindeutiger Index) anzeigen
sql = sql & " FIRST([" & Me![IndexField] & "]) AS [Index#]"
sql = sql & ","
'* Anzeigen der Feld-Kombination (Lastname, Postcode, City)
rst.MoveFirst
While Not rst.EOF
sql = sql & " [" & rst![Field] & "]"
sql = sql & ","
rst.MoveNext
Wend
'* letztes Komma abschneiden
sql = Left(sql, Len(sql) - 1)
'** FROM-Abschnitt erstellen
sql = sql & " FROM"
sql = sql & " [" & Me![SourceTable] & "]"
'** GRUPPIERUNG-Abbschnitt erzeugen
sql = sql & " GROUP BY"
rst.MoveFirst
While Not rst.EOF
sql = sql & " [" & rst![Field] & "]"
sql = sql & ","
rst.MoveNext
Wend
'* letztes Komma abschneiden
sql = Left(sql, Len(sql) - 1)
'* HAVING-Klausel (also GROUP BY-Filter), erzeugen
sql = sql & " HAVING"
sql = sql & " COUNT(*)>1"
sql = sql & ";"
'*** Abfrage erzeugen, als SQLDuplicate abspeichern ***
dbs.CreateQueryDef Name:="SQLDuplicate", _
SQLText:=sql
'* DAO-Objekt schliessen
dbs.Close
'* Funktion erfolgreich!
Create_SQLDuplicate = True
RunError:
Select Case Err.Number
Case 0
'* kein Fehler aufgetreten
Case 3012
'* Abfrage existiert schon
'* Abfrage löschen
DoCmd.DeleteObject ObjectType:=acQuery, _
ObjectName:="SQLDuplicate"
'* Nochmal versuchen
Resume
Case Else
MsgBox Err.Description, vbCritical, "Fehler-No. " & Err.Number
End Select
RunExit:
'* DAO-Objekt-Variablen terminieren
Set dbs = Nothing
Set rst = Nothing
End Function
Private Sub btn_PreView_Click()
Dim tof As Boolean
'* Funktion zur Erstellung der Duplikatanzeige aufrufen!
tof = Create_SQLDuplicate()
'* Wenn Abfragen-Erstellung fehlgeschlagen, dann Abbruch!
If tof = False Then Exit Sub
'* Abfrage zur Duplikatanzeige ausführen
DoCmd.OpenQuery QueryName:="SQLDuplicate", _
View:=acViewNormal, _
DataMode:=acEdit
End Sub
Listing 3: Funktionen zum Löschen der Duplikate
Public Function Delete_Duplicate() As Boolean
'* DAO-Objekt-Variablen definieren
Dim dbs As Database
Dim src As Recordset
Dim trg As Recordset
'* Funktion initialisieren
Delete_Duplicate = False
Set dbs = CurrentDb()
'* [Ziel-Tabelle] öffnen
Set trg = dbs.OpenRecordset(Name:=Me![SourceTable], _
Type:=dbOpenTable)
'* Abfrage mit Duplikaten öffnen
Set src = dbs.OpenRecordset(Name:="SQLDuplicate", _
Type:=dbOpenDynaset)
'* Wenn keine Duplikate, dann Abbruch
If src.RecordCount = 0 Then GoTo RunExit
'* Indexfeld setzen
trg.Index = "PrimaryKey"
'* zu erstem Duplikat-Datensatz springen
src.MoveFirst
While Not src.EOF
'* Datensätze mit Index des Duplikats suchen
trg.Seek Comparison:="=", _
Key1:=src(0)
'* Wenn gefunden, dann löschen
If Not trg.NoMatch Then trg.Delete
'* Nächsten Duplikat-Datensatz
src.MoveNext
Wend
'* Funktion erfolgreich
Delete_Duplicate = True
RunExit:
'* DAO-Objekt-Variablen terminieren
Set dbs = Nothing
Set src = Nothing
Set trg = Nothing
End Function
Private Sub btn_Action_Click()
Dim tof As Boolean
'* Funktion zur Erstellung der Duplikatanzeige aufrufen!
tof = Create_SQLDuplicate()
'* Wenn Abfragen-Erstellung fehlgeschlagen, dann Abbruch!
If tof = False Then Exit Sub
'* Schleife solange ausführen, bis Rückgabe gleich FALSE
'* d.h. Abbruch, da keine Duplikate mehr vorhanden!
While tof = True
'* Doppelte Datensätze löschen
tof = Delete_Duplicate
Wend
'* Meldung über Erfolg ausgeben
DoCmd.Beep
MsgBox "Fertig"
End Sub