Kontakt-Formular   Inhaltsverzeichnis   Druckansicht  

VisualBasic.tips

Startseite > Microsoft Access VBA > Duplikate finden und löschen

Duplikate finden und löschen

veröffentlicht: redmond's inside ACCESS, Ausgabe 06/2001

Download Beispieldatei

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:

  1. Eine Gruppierungsabfrage, die jeweils den ersten gefundenen Datensatz aller mehrfach vorkommenden Datensätze und die Anzahl seiner Duplikate anzeigt.
  2. 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 1: Anzeige der Duplikate und deren Anzahl

Abbildung 2: Anzeige aller mehrfach vorkommenden Datensätze

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

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

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

 

Seitenanfang