Tabellen in Access verknüpfen: die Beziehungen, an denen später jede Abfrage hängt
Primärschlüssel, Fremdschlüssel und referenzielle Integrität in der richtigen Reihenfolge, und warum eine fehlende Verknüpfungslinie das Ergebnis vervielfacht.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Abfrage liefert ein Ergebnis, nur nicht das richtige
Der typische Verlauf beginnt harmlos. Zwei Tabellen sind angelegt, die Daten stehen drin, und die erste Abfrage soll Kunden und Aufträge nebeneinanderstellen. Das Ergebnis hat statt vierhundert Zeilen plötzlich achtzigtausend, oder es hat dreißig Zeilen, obwohl es mehr sein müssten. Beides sind keine Fehler von Access, sondern die richtige Antwort auf eine Frage, die anders gestellt war als gemeint.
Dahinter steckt fast immer, dass die Beziehungen nie angelegt wurden. Access kann eine Abfrage auch ohne sie bauen, es weiß dann nur nicht, welche Zeile zu welcher gehört, und stellt jede mit jeder zusammen. Umgekehrt wird bei angelegter Verknüpfung standardmäßig nur ausgegeben, wozu es auf beiden Seiten einen Partner gibt, weshalb Kunden ohne Auftrag im Ergebnis fehlen. Wer diese beiden Verhaltensweisen kennt, erkennt am Ergebnis sofort, woran es lag.
Der zweite Teil des Problems zeigt sich später und ist unangenehmer. Ohne referenzielle Integrität entstehen mit der Zeit Datensätze, die auf nichts mehr verweisen: Aufträge zu einem gelöschten Kunden, Positionen zu einem Auftrag, den es nicht gibt. Microsoft nennt als Zweck der referenziellen Integrität ausdrücklich, verwaiste Datensätze zu vermeiden und Verweise synchron zu halten. Sind sie einmal entstanden, lässt sich die Integrität nicht mehr nachträglich einschalten, ohne vorher aufzuräumen, und das Aufräumen ist Handarbeit.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Eine Abfragezeile, die das ganze Modell sichtbar macht
Wenn du im Abfrageentwurf zwei verknüpfte Tabellen zusammenstellst, erzeugt Access daraus die folgende Anweisung. Sie steht in der SQL-Ansicht jeder Abfrage und lohnt einen Blick, denn jeder Bestandteil beantwortet eine eigene Frage und zeigt, welche Entscheidung aus dem Beziehungsfenster hier gerade wirkt.
Der Aufbau
SELECT k.Firma, a.Auftragsnummer FROM Kunde AS k INNER JOIN Auftrag AS a ON k.KundeID = a.KundeID WHERE a.Storniert = False; - 01 Felder aus zwei Tabellen in einer Zeile
SELECT k.Firma, a.AuftragsnummerGenau dafür ist die ganze Übung da: Der Firmenname steht in der Kundentabelle, die Auftragsnummer in der Auftragstabelle, und im Ergebnis stehen sie nebeneinander. Der Punkt zwischen Tabellenkürzel und Feldname ist keine Formsache, sondern die Antwort auf die Frage, aus welcher Tabelle ein Feld stammt. Bei gleichnamigen Feldern in beiden Tabellen ist er verpflichtend.
- 02 Die Seite mit dem Primärschlüssel
FROM Kunde AS kKunde ist hier die Tabelle mit den wenigen Datensätzen und dem Primärschlüssel, in der jede Firma genau einmal steht. Diese Seite ist die 1-Seite der Beziehung. Das Kürzel nach AS ist nur eine Abkürzung für den Rest der Anweisung und ändert nichts an der Tabelle, es macht die Zeile aber lesbar.
- 03 Die Seite mit dem Fremdschlüssel und die Art der Verknüpfung
INNER JOIN Auftrag AS aAuftrag ist die n-Seite, dort steht derselbe Kunde beliebig oft. Das Wort INNER entscheidet über das Ergebnis: Es liefert ausschließlich Paare, zu denen es auf beiden Seiten einen Partner gibt. Kunden ohne Auftrag fallen damit heraus. Wer sie braucht, ändert im Abfrageentwurf die Verknüpfungseigenschaften auf alle Datensätze der Kundentabelle, woraus Access ein LEFT JOIN macht.
- 04 Das Schlüsselpaar, das die Beziehung ausmacht
ON k.KundeID = a.KundeIDHier steht die eigentliche Beziehung: der Primärschlüssel der einen Tabelle gegen den Fremdschlüssel der anderen. Diese Zeile erscheint automatisch, wenn die Verknüpfung im Beziehungsfenster hinterlegt ist. Fehlt sie, entsteht ein Ergebnis, in dem jeder Kunde mit jedem Auftrag kombiniert wird, und das ist der Grund für die berüchtigten Abfragen mit zehntausenden Zeilen.
- 05 Der Filter, und auf welcher Seite er wirkt
WHERE a.Storniert = FalseEin Filter auf die n-Seite verkleinert die Auftragsmenge und damit das Ergebnis. Genau deshalb ist die Seite wichtig, auf der er steht: Ein Filter auf die Auftragstabelle in Verbindung mit einer Verknüpfung, die eigentlich alle Kunden zeigen sollte, macht diese Verknüpfung wirkungslos, weil Kunden ohne passenden Auftrag am Filter scheitern. Solche Bedingungen gehören dann in die Verknüpfung selbst und nicht in die WHERE-Zeile.
Wenn es nicht funktioniert
Das siehst du
Beim Anlegen der Beziehung meldet Access, dass die referenzielle Integrität nicht erzwungen werden kann, oder die Verknüpfung lässt sich gar nicht erst ziehen.
Warum
Dafür gibt es zwei übliche Gründe. Erstens passen die Datentypen nicht zueinander: Der Primärschlüssel ist ein AutoWert, das Fremdschlüsselfeld dagegen ein Textfeld oder eine Zahl in einer anderen Feldgröße. Zweitens stehen in der Tabelle bereits Datensätze, deren Fremdschlüssel auf nichts verweist, und genau die will die referenzielle Integrität verhindern.
Was hilft
Zuerst den Datentyp angleichen: Zum AutoWert gehört auf der anderen Seite ein Zahlenfeld in der Feldgröße Long Integer. Danach die verwaisten Datensätze suchen, in Access am schnellsten über eine Abfrage zur Inkonsistenzsuche, die genau die Datensätze der einen Tabelle findet, zu denen es in der anderen keinen Partner gibt. Diese Datensätze entweder einem gültigen Hauptdatensatz zuordnen oder löschen, danach lässt sich die Beziehung anlegen.
Das siehst du
Die Abfrage liefert deutlich weniger Datensätze als erwartet. Kunden, zu denen es noch keinen Auftrag gibt, fehlen im Ergebnis vollständig.
Warum
Die Verknüpfung ist eine gewöhnliche Verbindung, die nur Paare zeigt, zu denen es auf beiden Seiten einen Partner gibt. In der erzeugten Anweisung steht dafür INNER JOIN. Das ist der Standardfall und meistens auch der gewünschte, nur eben nicht dann, wenn eine Liste aller Kunden mit ihrem Auftragsstand entstehen soll.
Was hilft
Im Abfrageentwurf auf die Verknüpfungslinie doppelklicken und in den Verknüpfungseigenschaften die Variante wählen, die alle Datensätze der Kundentabelle enthält und nur die passenden der Auftragstabelle. Prüf danach die Bedingungen: Steht in der Kriterienzeile eine Bedingung auf ein Feld der Auftragstabelle, fallen die Kunden ohne Auftrag wieder heraus, weil ein leeres Feld die Bedingung nicht erfüllt.
Das siehst du
Die Abfrage liefert ein Vielfaches der erwarteten Zeilen, oft mehrere zehntausend, und jeder Kunde erscheint mit jedem Auftrag.
Warum
Zwischen zwei Tabellen im Abfrageentwurf fehlt die Verknüpfungslinie. Access hat dann keine Angabe darüber, welche Zeile zu welcher gehört, und stellt jede Zeile der einen Tabelle mit jeder Zeile der anderen zusammen. Bei dreihundert Kunden und vierhundert Aufträgen sind das hundertzwanzigtausend Zeilen, und das Ergebnis sieht auf den ersten Blick nur nach viel aus und nicht nach falsch.
Was hilft
Beziehungen im Beziehungsfenster anlegen, dann übernimmt Access die Verknüpfungen in jede neue Abfrage automatisch. Für eine bestehende Abfrage die Linie im Entwurf von Hand zwischen den beiden Schlüsselfeldern ziehen. Kommt eine dritte Tabelle dazu, prüf, ob auch sie eine Linie hat: Eine einzige fehlende Verbindung genügt, um das gesamte Ergebnis zu vervielfachen.
Das siehst du
Ein Kunde lässt sich nicht löschen, Access verweigert den Vorgang. Oder umgekehrt: Beim Löschen eines Kunden verschwinden auch alle seine Aufträge, ohne dass jemand das wollte.
Warum
Beides ist die referenzielle Integrität bei der Arbeit, und der Unterschied liegt an einer einzigen Option. Ohne Löschweitergabe lehnt Access das Löschen ab, solange abhängige Datensätze existieren, weil sonst verwaiste Datensätze entstünden. Mit aktivierter Löschweitergabe an verwandte Datensätze löscht Access nach Microsofts Beschreibung automatisch alle Datensätze, die auf den Primärschlüssel verweisen.
Was hilft
Entscheide fachlich, nicht technisch. Bei Auftragspositionen zu einem Auftrag ist die Löschweitergabe richtig, denn eine Position ohne Auftrag ist wertlos. Bei Aufträgen zu einem Kunden ist sie falsch, denn diese Aufträge sind Geschäftsvorfälle mit Aufbewahrungspflicht. Für ausgeschiedene Kunden ist deshalb ein Kennzeichen wie inaktiv der richtige Weg und nicht das Löschen des Datensatzes.
Das siehst du
Beim Anlegen einer weiteren Beziehung oder eines weiteren Indexfelds meldet Access, dass zu viele Indizes vorhanden sind, obwohl du sichtbar nur wenige angelegt hast.
Warum
Access erlaubt nach den Spezifikationen 32 Indizes je Tabelle, und Microsoft weist ausdrücklich darauf hin, dass darin auch die Indizes enthalten sind, die intern zum Verwalten von Tabellenbeziehungen erstellt werden. Jede Beziehung verbraucht also einen dieser Plätze, ohne in der Indexliste als eigener Eintrag aufzufallen. Dazu kommen Indizes, die Access automatisch für Felder anlegt, deren Name auf bestimmte Zeichenfolgen endet.
Was hilft
Die vorhandenen Indizes der Tabelle durchsehen und alle entfernen, die keine erkennbare Aufgabe haben. Danach prüfen, ob die Tabelle wirklich so viele Beziehungen braucht: Eine Tabelle mit einem Dutzend Fremdschlüsseln ist meistens ein Zeichen dafür, dass sie zwei verschiedene Dinge gleichzeitig abbildet und geteilt werden sollte. Falls das Modell tatsächlich so aussehen muss, ist der Umzug auf einen Datenbankserver der nächste Schritt.
Von zwei Tabellen zur belastbaren Beziehung
- 01 Hat jede Tabelle einen Primärschlüssel, der sich nie ändert?
- 02 Liegt das Fremdschlüsselfeld auf der Seite mit den vielen Datensätzen?
- 03 Passen die Datentypen der beiden Felder zueinander?
- 04 Ist die Verknüpfung im Beziehungsfenster hinterlegt und nicht nur in einer Abfrage?
- 05 Ist die referenzielle Integrität aktiviert, und gibt es keine verwaisten Datensätze mehr?
- 06 Ist bewusst entschieden, was beim Löschen des Hauptdatensatzes passieren soll?
Die Reihenfolge, in der du Beziehungen anlegst
Sechs Schritte, und die ersten drei entscheiden über den Rest. Wer sie einhält, legt die Beziehungen einmal an und fasst sie nie wieder an.
Gib jeder Tabelle einen Primärschlüssel, bevor du irgendetwas verbindest
Microsoft beschreibt den Primärschlüssel als Feld, das einen eindeutigen Bezeichner für jeden Datensatz bereitstellt. In der Praxis ist das fast immer ein Feld vom Typ AutoWert, das Access selbst vergibt. Widerstehe der Versuchung, eine fachliche Angabe wie die Kundennummer oder die Artikelbezeichnung dafür zu nehmen, denn fachliche Angaben ändern sich, und ein Schlüssel sollte das nicht tun.
Leg das Fremdschlüsselfeld auf der richtigen Seite an
Bei einer 1:n-Beziehung wandert der Schlüssel immer auf die Seite mit den vielen Datensätzen. Der Kunde bekommt kein Feld für seine Aufträge, sondern jeder Auftrag bekommt ein Feld für seinen Kunden. Diese Richtung ist die häufigste Verwechslung überhaupt, und sie fällt erst auf, wenn ein Kunde einen zweiten Auftrag bekommt.
Achte darauf, dass die Datentypen zusammenpassen
Ein AutoWert ist intern eine Ganzzahl. Das Fremdschlüsselfeld muss deshalb ein Zahlenfeld in der Feldgröße Long Integer sein und kein Textfeld. Passt das nicht, verweigert Access die Beziehung mit einer Meldung, die viele Leute an dieser Stelle für ein Programmproblem halten. Sie ist keins, sie ist der Hinweis auf genau diesen Punkt.
Zieh die Verknüpfung im Beziehungsfenster, nicht in der Abfrage
Eine im Beziehungsfenster hinterlegte Verknüpfung wirkt in jeder späteren Abfrage, in jedem Formular und in jedem Bericht, weil Access sie beim Zusammenstellen automatisch vorschlägt. Eine Verknüpfung, die du nur in einer einzelnen Abfrage ziehst, gilt für diese eine Abfrage. Das ist der eigentliche Unterschied zwischen einmal richtig und zwanzigmal wieder.
Schalte die referenzielle Integrität ein, solange die Tabellen noch klein sind
Ist sie aktiviert, lehnt Access nach Microsofts Beschreibung jede Operation ab, die sie verletzen würde. Es lässt sich also kein Auftrag zu einem nicht vorhandenen Kunden anlegen. Später einzuschalten ist mühsam, weil Access sich zu Recht weigert, solange verwaiste Datensätze existieren, und die musst du dann erst einzeln finden.
Entscheide bewusst über die beiden Weitergaben
Microsoft nennt zwei Optionen dazu: die Aktualisierungsweitergabe an verwandte Felder, bei der eine Änderung am Primärschlüssel automatisch überall nachgezogen wird, und die Löschweitergabe an verwandte Datensätze, bei der mit dem Datensatz auch alle abhängigen gelöscht werden. Die erste ist bei einem AutoWert ohne Wirkung und stört nicht. Die zweite ist ein scharfes Werkzeug und gehört nur dorthin, wo die abhängigen Datensätze wirklich wertlos sind, sobald der Hauptdatensatz verschwindet.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Verknüpfen heißt in Access zweierlei
Das Wort ist doppelt belegt, und die Verwechslung kostet regelmäßig einen halben Tag. Die eine Bedeutung ist die Beziehung zwischen zwei Tabellen derselben Datenbank, angelegt im Beziehungsfenster über ein Feldpaar aus Primär- und Fremdschlüssel. Darum geht es auf dieser Seite, und das ist die Bedeutung, die fast immer gemeint ist, wenn jemand fragt, wie man Tabellen verknüpft.
Die andere Bedeutung ist die verknüpfte Tabelle: eine Tabelle, die nicht in der Datei liegt, sondern anderswo, und auf die Access nur verweist. Das kann eine zweite Access-Datei sein, eine Tabelle in einem SQL Server, eine Tabellenkalkulationsdatei oder eine Textdatei. Microsoft beschreibt diesen Fall für den SQL Server in einem eigenen Artikel und trennt dabei ausdrücklich zwischen Importieren, bei dem eine Kopie entsteht, und Verknüpfen, bei dem die Daten am ursprünglichen Ort bleiben.
Im Alltag treffen beide Bedeutungen zusammen, und zwar in der geteilten Datenbank. Dort liegen die Tabellen in einer Datei auf einer Freigabe, und jede Arbeitsplatzkopie mit den Formularen und Berichten enthält verknüpfte Tabellen darauf. Die Beziehungen zwischen diesen Tabellen sind in der Datei mit den Daten hinterlegt. Wer das einmal auseinanderhält, versteht auch, warum eine Beziehung zwischen zwei verknüpften Tabellen in der Oberflächendatei angelegt werden kann, ohne dass sie dort referenzielle Integrität erzwingt.
Die drei Beziehungsarten und wann welche gilt
Der Normalfall ist die 1:n-Beziehung. Ein Kunde hat beliebig viele Aufträge, ein Auftrag gehört zu genau einem Kunden. Microsoft beschreibt den Aufbau so, dass der Primärschlüssel der 1-Seite als Fremdschlüssel in die Tabelle der n-Seite aufgenommen wird. Neunzig Prozent aller Beziehungen in einer Bürodatenbank sind von dieser Art, und wer sie beherrscht, kommt damit sehr weit.
Die m:n-Beziehung ist der Fall, in dem beide Seiten viele Partner haben können: Ein Auftrag kann mehrere Produkte enthalten, und ein Produkt kann in vielen Aufträgen vorkommen. Access kann das nicht direkt abbilden, und Microsoft beschreibt die Auflösung über eine dritte Tabelle, die oft als Zuordnungstabelle bezeichnet wird und die eine Beziehung in zwei 1:n-Beziehungen zerlegt. In dieser Tabelle stehen die beiden Fremdschlüssel und alles, was zu genau dieser Kombination gehört, etwa die Menge und der Einzelpreis.
Die 1:1-Beziehung ist selten und sollte es auch bleiben. Microsoft weist darauf hin, dass zusammengehörige Angaben normalerweise in dieselbe Tabelle gehören. Die sinnvollen Anwendungsfälle sind eng: sehr viele Felder, die selten gebraucht werden und deshalb ausgelagert werden, oder ein Teil der Angaben, der aus Gründen der Vertraulichkeit getrennt liegen soll. Wer eine 1:1-Beziehung entwirft, sollte sich einmal fragen, warum die Felder nicht einfach in einer Tabelle stehen.
Referenzielle Integrität und die beiden Weitergaben
Die referenzielle Integrität ist das eigentliche Versprechen, das eine Datenbank einer Tabelle voraushat. Ist sie für eine Beziehung aktiviert, lehnt Access nach Microsofts Beschreibung jede Operation ab, die sie verletzen würde. Es lässt sich kein Auftrag mit einer Kundennummer anlegen, die es nicht gibt, und kein Kunde löschen, solange Aufträge an ihm hängen. Der Zweck ist ausdrücklich, verwaiste Datensätze zu vermeiden und Verweise synchron zu halten.
Dazu gehören zwei Optionen, die getrennt eingeschaltet werden. Die Aktualisierungsweitergabe an verwandte Felder sorgt dafür, dass eine Änderung am Primärschlüssel automatisch in allen Feldern nachgezogen wird, die darauf verweisen. Bei einem AutoWert als Schlüssel ist sie ohne praktische Wirkung, weil sich dieser Wert nicht ändert, und sie schadet auch nicht. Wer dagegen eine fachliche Kennung als Schlüssel benutzt, braucht sie zwingend, und das ist gleichzeitig ein guter Grund, es nicht zu tun.
Die Löschweitergabe an verwandte Datensätze ist die scharfe Variante. Ist sie aktiv, löscht Access mit dem Hauptdatensatz automatisch alle Datensätze, die auf dessen Primärschlüssel verweisen. Für Positionen zu einem Auftrag ist das richtig, weil eine Position ohne Auftrag keinen Sinn ergibt. Für Aufträge zu einem Kunden ist es falsch, weil damit Geschäftsvorfälle verschwinden, für die es Aufbewahrungspflichten gibt. Die Regel dafür lautet: Weitergeben nur dort, wo der abhängige Datensatz ohne den Hauptdatensatz keine eigene Bedeutung hat.
Was Beziehungen für alles Spätere bedeuten
Der offensichtliche Gewinn liegt in den Abfragen. Sind die Beziehungen hinterlegt, zieht Access die Verknüpfungslinien beim Zusammenstellen einer Abfrage automatisch mit und erzeugt die passende Anweisung. Das erspart nicht nur Arbeit, sondern verhindert genau die beiden Fehler, an denen die meisten Auswertungen scheitern: die vervielfachte Ergebnismenge bei fehlender Verknüpfung und die zu kleine bei einer Verknüpfung, die nur Paare zeigt.
Der zweite Gewinn liegt in den Formularen. Eine bestehende 1:n-Beziehung ist die Voraussetzung dafür, dass Access ein Hauptformular mit einem Unterformular anbietet, in dem zu einem Kunden gleich seine Aufträge stehen und ein neuer Auftrag automatisch die richtige Zuordnung bekommt. Ohne Beziehung müsste diese Zuordnung von Hand gesetzt werden, und genau dort entstehen die falschen Verweise, die später als verwaiste Datensätze auffallen.
Der dritte Gewinn zeigt sich, wenn die Datenbank aus Access herauswächst. Ein Modell aus Tabellen, Schlüsseln und Beziehungen ist genau das, was auch ein Datenbankserver erwartet. Microsoft beschreibt die Migration der Tabellen auf einen SQL Server als Weg, bei dem Formulare und Berichte in Access bleiben und über verknüpfte Tabellen weiterarbeiten. Wer die Beziehungen von Anfang an sauber angelegt hat, macht diesen Schritt in Tagen. Wer eine flache Tabelle mitbringt, macht ihn gar nicht, sondern baut vorher das Modell, das von Anfang an gefehlt hat.
Dazu passende Kurse
Wenn Abfragen bei euch regelmäßig zu viele oder zu wenige Datensätze liefern, sind Access-Trainings zu Datenmodell und Abfragen der kürzeste Weg zur Ursache im Modell.
Soll die Auswertung am Ende in einem Dokument oder einer Präsentation landen, findest du bei cmt Office-Kurse für den sauberen Aufbau von Daten .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Vielen Dank für die sehr informative und unterhaltsame Schulung!
Häufige Fragen
Welchen Datentyp braucht das Fremdschlüsselfeld?
Warum fehlen in meiner Abfrage die Kunden ohne Auftrag?
Kann ich referenzielle Integrität nachträglich einschalten?
Wie bilde ich eine Beziehung ab, bei der beide Seiten viele Partner haben?
Soll ich die Löschweitergabe aktivieren?
Wie viele Beziehungen verträgt eine Tabelle?
Quellen
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Office-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Das eigene Datenmodell einmal von Grund auf entwerfen
Bei cmt entwirfst du im Access-Kurs Tabellen, Schlüssel und Beziehungen an einem eigenen Fall und siehst dabei, welche Abfragen daraus mühelos werden und welche Fehler gar nicht mehr entstehen können.