 | Unser Angebot für Sie! Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'! |
| | | | | |
Zusammenfassung
Entfernen Sie doppelte Datensätze aus den Tabellen Ihrer Datenbank.
Techniken
Abfragen
Voraussetzungen
Access 2000 oder höher
Beispieldateien
DuplikateLoeschen.mdb
Shortlink
637
Duplikate suchen und löschen
André Minhorst, Duisburg
Alle Jahre wieder bekommt es der gemeine Access-Entwickler mit einem Rutsch frischer inkonsistenter Daten zu tun, die es auseinanderzupflücken gilt. Wo war also nochmal die Abfrage zum Auffinden und Löschen der doppelten Daten? Falls diese nicht zur Hand ist oder Sie so etwas noch gar nicht im Repertoire haben, finden Sie in diesem Beitrag alles zum Thema Duplikate in Access-Tabellen.
Doppelte Datensätze und doppelte Felder haben in einer relationalen Datenbank nichts zu suchen. Sie führen immer zu Fehlern und müssen daher dringend eliminiert werden.
Viele der potenziellen Probleme lassen sich zwar durch ein gutes Datenmodell von vornherein ausschließen, einige Fehlerquellen sind sich jedoch nicht so einfach auszuschalten.
Wenn beispielsweise mehrere Mitarbeiter einer Firma kontinuierlich Kundendaten und Bestellungen erfassen, dann treten geradezu zwangsläufig Datenduplikate auf.
Das typische Beispiel ist die Aufnahme einer telefonischen Bestellung für einen Kunden. Manchmal vergisst der Mitarbeiter nachzusehen, ob der Kunde vielleicht schon angelegt ist - und schon ist derselbe Kunde zweimal in der Datenbank enthalten.
Das kann schwerwiegende Folgen haben. Wenn der Kunde beispielsweise die Rechnung zur ersten Bestellung auch nach der dritten Mahnung noch nicht bezahlt hat, dann sollte er eigentlich nicht mehr oder nur gegen Vorkasse beliefert werden. Ein Datenbanksystem, das für diesen Fall geeignete Vorkehrungen trifft, kann erhebliche Zahlungsausfälle vermeiden.
Aber auch jede Umsatzauswertung, Rabattberechnung und Akquisemaßnahme ist fehlerträchtig, wenn die Daten eines Kunden auf mehrere zusammenhanglose Datensätze verteilt sind.
Da sich Duplikate einerseits nicht vermeiden lassen, sie andererseits aber zu schwerwiegenden Problemen führen können, sollten Sie sich angewöhnen, die in einer Datenbank gespeicherten Daten in regelmäßigen Abständen zu untersuchen.
Die folgenden Abschnitte beschreiben, wie Sie Ihre Daten nach Duplikaten durchforsten und diese gegebenenfalls löschen können.
Duplikate per Assistent suchen
Für die Suche nach Duplikaten ist eine Auswahlabfrage mit Gruppierungen bestens geeignet. Access verfügt über einen Abfrage-Assistenten, mit dem Sie solche Abfragen komfortabel anlegen können.
Wir zeigen die Funktionsweise des Assistenten anhand der Tabelle tblKunden aus der Beispieldatenbank.
Um den Assistenten zu starten, gehen Sie wie folgt vor:
- Aktivieren Sie das Datenbankfenster.
- Wählen Sie den Befehl Einfügen|Abfrage aus der Menüleiste (bis Access 2003) beziehungsweise den Ribboneintrag Erstellen|Andere|Abfrage-Assistent.
- Führen Sie einen Doppelklick auf dem Eintrag Abfrage-Assistent zur Duplikatsuche durch, damit Access den Assistenten startet (s. Abb. 1).
- Markieren Sie auf der ersten Dialogseite die Tabelle tblKunden, in der nach Duplikaten gesucht werden soll (s. Abb. 2).
- Klicken Sie auf die Schaltfläche Weiter.
Auf der zweiten Dialogseite zeigt der Assistent eine Liste der Felder der Kundentabelle an. Wählen Sie in diesem Dialog das Feld aus, das der Assistent nach Duplikaten durchsuchen soll (s. Abb. 3).
Abb. 1: Starten des Assistenten zur Duplikatsuche
Abb. 2: Legen Sie auf der ersten Dialogseite fest, welche Tabelle auf Duplikate untersucht werden soll.
Abb. 3: In diesem Dialog wählen Sie die Felder aus, die der Assistent auf Duplikate untersuchen soll.
Wenn Sie mehr als ein Feld auswählen, dann reiht Access die Felder aneinander und ermittelt nur dann Duplikate, wenn sich die Werte in allen Feldern wiederholen. Sie könnten beispielsweise die Felder Firma und Kontaktperson auswählen, um nach Wiederholungen einer Kontaktperson in der gleichen Firma zu suchen. Wenn eine Kontaktperson zwar mehrfach, aber in verschiedenen Firmen vorkommt, dann würde Access dies richtigerweise nicht als Duplikat melden.
Dieser Zusammenhang ist darauf zurückzuführen, dass der Assistent eine Abfrage mit Gruppierungen erstellt. Er gruppiert nach allen Feldern, die Sie auf der zweiten Dialogseite ausgewählt haben. Für das Beispiel wählen Sie zunächst nur das Feld Firma aus.
Nach einem Klick auf die Schaltfläche Weiter zeigt der Assistent auf der dritten Dialogseite eine Liste mit den noch verbliebenen Feldern an. Hier wählen Sie die Felder aus, in denen zwar nicht nach Duplikaten gesucht werden soll, die Sie aber trotzdem im Abfrageergebnis sehen möchten (s. Abb. 4).
Abb. 4: Wenn Sie auf der dritten Dialogseite keine Felder auswählen, dann enthält das Abfrageergebnis die Anzahl der Wiederholungen einer Firma.
Wenn Sie hier keine Felder auswählen, dann zeigt Access im Abfrageergebnis nur die Firmen und die Anzahl der Duplikate an. Bei der Anzeige zusätzlicher Felder wiederholt Access die Anzeige der Firma und blendet zusätzlich die weiteren Felder ein (s. Abb. 5).
Abb. 5: Dieses Abfrageergebnis zeigt jede Firma so oft an, wie Wiederholungen in der zugrunde liegenden Tabelle gespeichert sind. Außerdem sehen Sie die Felder, die auf der dritten Dialogseite des Assistenten ausgewählt wurden.
Auf der letzten Dialogseite des Assistenten können Sie einen Namen für die Auswahlabfrage zur Duplikatsuche eingeben. Außerdem können Sie die Abfrage in der Datenblatt- oder der Entwurfsansicht anzeigen lassen.
Schauen Sie sich die vom Assistenten zur Duplikatsuche generierten Abfragen ruhig einmal in der Entwurfsansicht an. Sie können so einiges über das Gruppieren von Daten in Auswahlabfragen lernen.
Duplikate löschen
Die Suche nach Duplikaten ist nur die halbe Arbeit. Wenn Sie Duplikate gefunden haben, dann benötigen Sie eine Strategie für den Umgang mit ihnen.
Sollte die untersuchte Tabelle eine Mastertabelle innerhalb einer 1:n-Beziehung sein, dann müssen Sie zunächst die verknüpfte Datensätze bearbeiten. In der Regel werden Sie sie per Aktualisierungsabfrage mit einem der gefundenen Duplikate verknüpfen.
So können Sie beispielsweise das Problem der eingangs erwähnten Bestellungen lösen. Wählen Sie zunächst ein Exemplar der mehrfach vorkommenden Kundendaten aus und ordnen Sie diesem Datensatz alle verknüpften Bestellungen zu. Anschließend können Sie alle anderen Duplikate des Kundendatensatzes löschen.
Die optimale Vorgehensweise für die Behandlung verknüpfter Datensätze hängt stark von der jeweiligen Anwendung ab und soll an dieser Stelle nicht weiter behandelt werden. Der folgende Abschnitt beschreibt das Löschen von Duplikaten am Beispiel der Kundentabelle.
Für das Löschen von Duplikaten stellt Access leider keinen Assistenten zur Verfügung. Der Grund hierfür besteht wahrscheinlich darin, dass sich das Löschen von Duplikaten nicht so einfach verallgemeinern lässt.
Wie weiter oben beschrieben wurde, können Sie zwar die doppelten Firmen aus der Datenbank ermitteln, aber welches der Duplikate soll übrig bleiben beziehungsweise welche Duplikate sollen gelöscht werden?
Wir stellen Ihnen ein Verfahren vor, das willkürlich alle Duplikate bis auf eines löscht. Dies funktioniert aber nur, wenn sich die Duplikate eindeutig identifizieren lassen. Im Fall der Kundentabelle geschieht dies anhand des AutoWert-Feldes KundeID.
Wenn Sie Duplikate aus Ihren eigenen Tabellen löschen wollen, dann verwenden Sie statt KundeID das Primärschlüsselfeld der Tabelle. Sollte eine Tabelle kein Primärschlüsselfeld haben, müssen Sie eines anlegen. Sie können dieses Feld nach dem Löschen der Duplikate ebenfalls wieder entfernen.
Als Erstes legen Sie eine Abfrage für alle Kunden an, die nicht gelöscht werden sollen.
Gehen Sie wie folgt vor:
- Legen Sie eine neue Abfrage an.
- Wählen Sie die Tabelle tblKunden als Datenherkunft aus.
- Fügen Sie die Spalten KundeID und Firma zum Entwurfsbereich hinzu.
- Wählen Sie den Befehl Ansicht|Funktionen (unter Access 2007 im Ribbon unter Entwurf|Einblenden/Ausblenden|Summen) aus, um die Zeile Funktionen in den Entwurfsbereich einzublenden.
- Wählen Sie für das Feld KundeID die Funktion Min und für Firma die Funktion Gruppierung.
- Schalten Sie das Kriterium Anzeigen für das Feld Firma aus.
- Speichern Sie die Abfrage unter dem Namen qryKundenNichtLoeschen (s. Abb. 6).
Abb. 6: Diese Abfrage ermittelt die IDs der Kunden, die nicht gelöscht werden sollen.
Wenn Sie die Abfrage ausführen, gruppiert Access die Kunden nach dem Feld Firma. Damit ist sichergestellt, dass für jede Firma nur ein Datensatz in das Abfrageergebnis aufgenommen wird. Die Min-Funktion wählt hierzu die Firma mit dem kleinsten Wert im Feld KundeID aus.
Das Abfrageergebnis enthält nur die IDs, also die Werte aus den Primärschlüsselfeldern der Kundentabelle. Damit sind die Kunden, die nicht gelöscht werden sollen, eindeutig anhand ihres Primärschlüssels identifiziert (s. Abb. 7).
Abb. 7: Das Ergebnis der Abfrage enthält 91 IDs.
Aber wie ermitteln Sie nun die Kunden, die gelöscht werden sollen? Einfach ausgedrückt sind das alle anderen. Um die zu ermitteln, benötigen Sie eine zweite Abfrage.
Bevor Sie diese anlegen, wechseln Sie in die SQL-Ansicht und kopieren die SQL-Anweisung in die Zwischenablage von Windows.
Gehen Sie dann wie folgt vor:
- Legen Sie eine neue Abfrage an.
- Wählen Sie die Tabelle tblKunden als Datenherkunft aus.
- Übernehmen Sie das Sternchen (*) als Platzhalter für alle Datenfelder in den Entwurfsbereich.
- Übernehmen Sie außerdem das Feld KundeID in den Entwurfsbereich.
- Fügen Sie als Kriterium für die Spalte KundeID den Text Nicht In ( ) ein.
- Fügen Sie den Inhalt der Zwischenablage in die beiden Klammern ein.
- Leeren Sie das Kästchen Anzeigen für die Spalte KundeID.
- Speichern Sie die Abfrage unter dem Namen qryKundenLoeschen.
Das Kriterium dieser Abfrage ist auf den ersten Blick etwas ungewöhnlich. Es handelt sich hierbei um eine Mengenoperation, die über den In-Operator durchgeführt wird.
Das Kriterium ermittelt alle IDs, die nicht in der Menge enthalten sind, die durch die SELECT-Anweisung angegeben wird. Da die SELECT-Anweisung aber identisch mit der Abfrage der nicht zu löschenden Kunden ist, ermittelt die Abfrage eben alle anderen Kunden. Das sind nach den Gesetzen der Mengenlehre die zu löschenden Kunden (s. Abb. 8).
Abb. 8: Diese Abfrage ermittelt alle Kunden, die gelöscht werden sollen.
Anhand der Datensatzanzahl der beiden Abfrageergebnisse können Sie das leicht nachprüfen. Die Anzahl der nicht zu löschenden Datensätze beträgt 91, die der zu löschenden 19 (s. Abb. 9). Das macht zusammen 110 Datensätze, also genau die Anzahl der Datensätze in der Tabelle tblKunden.
Abb. 9: Hier sehen Sie die zu löschenden Kunden in der Datenblattansicht.
Löschvorgang durchführen
Um das Löschen durchzuführen, wechseln Sie wieder in die Entwurfsansicht der Abfrage.
Wählen Sie den Befehl Abfrage|Löschabfrage aus der Menüleiste, um die Auswahl- in eine Löschabfrage umzuwandeln (unter Access 2007: Ribboneintrag Entwurf|Abfrage|Löschen).
Abb. 10 zeigt die Entwurfsansicht dieser Löschabfrage. Wenn Sie nun in die Datenblattansicht wechseln, zeigt Access erneut die zu löschenden Datensätze an.
Abb. 10: Die Löschabfrage zum Entfernen der Duplikate in der Entwurfsansicht
Wenn Sie die Abfrage aber ausführen, dann löscht Access alle Duplikate aus der Kundentabelle.
|
