1:n-Beziehung als Restriktion für Feldwerte

Wenn Sie ein Abonnement des Magazins 'Access im Unternehmen' besitzen, können Sie sich anmelden und den kompletten Artikel lesen.
Anderenfalls können Sie das Abonnement hier im Shop erwerben.

1:n-Beziehung als Restriktion für Feldwerte

Normalerweise verwenden Sie 1:n-Beziehungen, um die Datensätze zweier Tabellen zu verknüpfen und so Kombinationen aus diesen Datensätzen zu ermöglichen. Oder Sie nutzen die Beziehung für die Verknüpfung mit einer Lookup-Tabelle, welche die Werte für ein Feld liefert. Es geht aber noch anders: Wir wäre es, wenn Sie etwa für ein Feld, dass nur bestimmte Zahlenwerte annehmen können soll, auch eine per 1:n-Beziehung verknüpfte Tabelle hinterlegen? Dieser Beitrag zeigt, welchen praktischen Zweck dies hat und wie Sie dies umsetzen.

Im Beitrag HTML-Kreuztabelle 2: Werte bearbeiten (www.access-im-unternehmen.de/1162) haben wir eine Tabelle namens tblMaterialpreise verwendet, um die Preise für ein Material mit bestimmten Maßen, in diesem Fall Höhe und Breite, zu definieren. Um schon Werte für Höhe und Breite vorzudefinieren, die noch gar nicht in der Tabelle tblMaterialpreise enthalten sind, haben wir zwei Tabellen namens tblHoehen und tblBreiten erstellt. Diese haben ein Primärschlüsselfeld namens HoeheID/BreiteID, ein Feld für die Angabe des jeweiligen Wertes namens Hoehe/Breite und ein Fremdschlüsselfeld namens MaterialID zur Auswahl des Materials, auf das sich die Höhe oder Breite bezieht, enthalten (siehe Bild 1).

Bisherige Ausstattung der Tabellen tblMaterialpreise, tblHoehen und tblTiefen

Bild 1: Bisherige Ausstattung der Tabellen tblMaterialpreise, tblHoehen und tblTiefen

Eigentlich wollte ich der Tabelle tblMaterialpreise dann statt der bisher vorhandenen Felder Hoehe und Breite, welche die Angaben mit dem Datentyp Zahl in Millimeter gespeichert haben, zwei Fremdschlüsselfelder namens HoeheID und BreiteID hinzufügen, die jeweils mit der Tabelle tblHoehen beziehungsweise tblBreiten verknüpft werden sollten. Auf diese Weise hätte man dann über ein Nachschlagefeld die jeweils für den Preis ausschlaggebenden Maße auswählen können.

Wert als Primärschlüssel

Allerdings fiel mir dann auf, dass dies möglicherweise einfacher geht und auf eine andere Weise sogar noch Performance-Vorteile mit sich bringen könnte. Wenn ich die Verknüpfungen wie geplant realisieren würde, müsste Access immer, wenn die Werte der Tabelle tblMaterialpreise mit den Werten für die Höhe und Breite ausgegeben werden sollen, die entsprechenden Informationen über die Verknüpfung aus den Tabellen tblHoehen und tblBreiten abfragen. Das ist aber in diesem Fall gar nicht nötig.

Wenn wir uns ansehen, welche Werte das Feld Hoehe in der Tabelle tblHoehen und das Feld Breite in der Tabelle tblBreite annehmen soll, könnte man annehmen, dass jeder Wert in jeder der beiden Tabellen nur einmal vorkommen soll. Das ist jedoch nicht der Fall, denn jeder Wert kann für jede Kombination mit dem über das Fremdschlüsselfeld MaterialID maximal einmal vorkommen – bei zwei Materialien kann also auch zweimal der Wert 1.000 mm vorkommen.

Wenn die Unterscheidung über das Feld MaterialID nicht wäre und wir die Werte nur für ein einziges Material angeben müssten, könnten wir die Tabellen tblHoehen und tblBreiten mit dem Feld Hoehe beziehungsweise Breite als Primärschlüsselfeld ausstatten. Da wir allerdings das Feld MaterialID berücksichtigen müssen und wir einen zusammengesetzten, eindeutigen Index über die beiden Felder Hoehe und MaterialID beziehungsweise Breite und MaterialID haben, müssten wir aus diesen zusammengesetzten, eindeutigen Indizes einen zusammengesetzten Primärschlüssel machen. Das Ergebnis sieht wie in Bild 2 aus. Die noch in Indizes-Fenster vorhandenen zusammengesetzten, eindeutigen Indizes aus den Feldern Hoehe und MaterialID beziehungsweise Breite und MaterialID entfernen wir.

Ersetzen des einfachen Primärschlüsselfeldes durch einen zusammengesetzten Primärschlüssel aus den übrigen Feldern

Bild 2: Ersetzen des einfachen Primärschlüsselfeldes durch einen zusammengesetzten Primärschlüssel aus den übrigen Feldern

Verknüpfungen erstellen

Dann haben wir aber auch leider kein einfaches Primärschlüsselfeld mehr, mit dem wir die noch zu erstellenden Fremdschlüsselfelder der Tabelle tblMaterialpreise verknüpfen können. Aber das ist kein Problem: Wir erstellen einfach zwei Verknüpfungen!

Die erste legen wir auf dem gewohnten Wege an. Dazu nutzen wir das bereits in der Tabelle tblMaterialpreise vorhandene Feld Hoehe und wählen als neuen Datentyp in der Entwurfsansicht den Eintrag Nachschlagefeld aus. Hier wählen wir die Tabelle tblHoehen als Quelltabelle aus und das Feld Hoehe als einziges ausgewähltes Feld.

Für dieses Feld legen wir eine aufsteigende Sortierung fest. Interessanterweise finden wir in dem Schritt, der normalerweise die Möglichkeit anbietet, referenzielle Integrität zu definieren, keine solche Einstellung (siehe Bild 3).

Fehlende Einstellung zum Festlegen referenzieller Integrität

Bild 3: Fehlende Einstellung zum Festlegen referenzieller Integrität

Also verzichten wir zunächst darauf und schließen den Dialog über die Schaltfläche Fertigstellen. Das Gleiche erledigen für analog für das Feld Breite der Tabelle tblMaterialpreise und die Tabelle tblBreiten.

Schauen wir uns nun das Datenmodell im Beziehungen-Fenster an, sieht dies wie in Bild 4 aus. Nun soll der Benutzer etwa für das Feld Hoehe der Tabelle tblMaterialpreise nur diejenigen Felder der Tabelle tblHoehen auswählen können, die im Feld MaterialID den gleichen Wert wie die Tabelle tblMaterialpreise aufweist.

Datenmodell für die Zuordnung der Höhe und der Breite

Bild 4: Datenmodell für die Zuordnung der Höhe und der Breite

Um dies zu gewährleisten, müssen wir dann auch noch eine Beziehung zwischen den beiden Feldern MaterialID der Tabelle tblMaterialpreise und MaterialID der Tabelle tblHoehen beziehungsweise tblBreiten herstellen.

Wie erledigen wir das? Wenn wir es probieren wie beim herkömmlichen Erstellen einer Beziehung zwischen zwei Tabellen im Beziehungen-Fenster, also durch das Ziehen des Fremdschlüsselfeldes der einen Tabelle auf die Zieltabelle, erhalten wir die Meldung aus Bild 5.

Meldung bei vorhandener Beziehung

Bild 5: Meldung bei vorhandener Beziehung

Betätigen wir hier nun die Schaltfläche Ja, erscheint der Dialog Beziehung bearbeiten aus Bild 6. Hier fügen wir die zweite Beziehung hinzu, indem wir sowohl für die Tabelle tblHoehen als auch für die Tabelle tblMaterialpreise das Feld MaterialID auswählen.

Doppelte Verknüpfung herstellen

Bild 6: Doppelte Verknüpfung herstellen

Außerdem aktivieren wir hier die Option Mit referenzieller Integrität. Das Gleiche erledigen wir für die Tabellen tblBreiten und tblMaterialpreise.

Wenn wir nun einige weitere Datensätze zur Tabelle tblHoehen hinzufügen, die wir einem anderen Wert der Tabelle tblMaterialien zuordnen, sieht diese Tabelle wie in Bild 7 aus.

Höhen für verschiedene Spanplattendicken

Bild 7: Höhen für verschiedene Spanplattendicken

Was wollen wir damit erreichen? Nun, wir wollen sehen, ob wir damit die Auswahl im Nachschlagefeld der Tabelle tblMaterialpreise so einschränken können, dass nur die Werte der verknüpften Tabelle tblHoehen im Nachschlagefeld Hoehe angezeigt werden, die auch dem aktuell im Feld MaterialID ausgewählten Material entsprechen. Das ist nicht der Fall, wie Bild 8 zeigt.

Die Auswahl der Höhen bietet zu viele Datensätze an.

Bild 8: Die Auswahl der Höhen bietet zu viele Datensätze an.

Einschränkung per Abfrage

Diese Einschränkung bekommen wir nur durch die Erstellung einer geeigneten Abfrage hin. Diese sieht im Entwurf wie in Bild 9 aus. Wir fügen der Abfrage die beiden Tabellen tblMaterialpreise und tblHoehen hinzu. Die beiden vorhandenen Beziehungen werden automatisch in den Abfrageentwurf übernommen. Was noch fehlt, ist die Anpassung der Datensatzherkunft des Nachschlagefeldes Hoehe der Tabelle tblMaterialpreise. Diese wollen wir so einrichten, dass diese nur solche Einträge der Tabelle tblHoehen anzeigt, die den gleichen Wert im Feld MaterialID aufweist wie der aktuelle Datensatz der Tabelle tblMaterialpreise.

Formulierung der Datensatzherkunft des Nachschlagefeldes

Bild 9: Formulierung der Datensatzherkunft des Nachschlagefeldes

Das realisieren wir durch die Formulierung der Eigenschaft wie folgt:

SELECT Hoehe FROM tblHoehen WHERE tblMaterialpreise.MaterialID = tblHoehen.MaterialID;

Wenn wir nun in die Datenblattansicht der Abfrage wechseln, sind die Datensätze im Nachschlagefeld Hoehe zwar nach den Werten der Tabelle tblHoehen für einen der beiden angegebenen Werte von MaterialID gefiltert – allerdings immer nur für den Wert von MaterialID für den oben befindlichen Datensatz (siehe Bild 10).

Für diesen Datensatz zeigt das Nachschlagefeld die richtigen Werte an ...

Bild 10: Für diesen Datensatz zeigt das Nachschlagefeld die richtigen Werte an ...

Wenn wir zu einem Datensatz wechseln, der mit dem Eintrag Spanplatte 19 mm der Tabelle tblMaterialien verknüpft ist und hier das Nachschlagefeld für Hoehe öffnen, erhalten wir wieder die Höhen für den Eintrag Spanplatte 17 mm (siehe Bild 11).

... für den nächsten nicht mehr.

Bild 11: ... für den nächsten nicht mehr.

Das ändert sich allerdings, wenn wir nun auf die Schaltfläche F5 zum Aktualisieren klicken. Dies aktualisiert nicht nur die Abfrage, sondern offensichtlich auch die Datensatzherkunft des Nachschlagefeldes entsprechend des Wertes des zuletzt markierten Datensatzes der Abfrage – auch wenn der Datensatzmarkierer danach wieder auf den ersten Datensatz zurückfällt. Bild 12 zeigt, dass das Nachschlagefeld nun den richtigen Datensatz anzeigt.

Nach der Aktualisierung klappt es dann.

Bild 12: Nach der Aktualisierung klappt es dann.

Neuen Datensatz in der Abfrage anlegen

Damit könnte man später im Formular eventuell leben – die Datensatzherkunft ließe sich ja direkt ansteuern und aktualisieren, ohne dass der aktuelle Datensatz verlassen werden muss. Aber was geschieht, wenn wir nun einen über die Abfrage qryMaterialpreiseHoehenBreiten einen neuen Datensatz in der Tabelle tblMaterialpreise hinzufügen? Wenn wir den Datensatzzeiger auf einen neuen Datensatz einstellen, mit F5 aktualisieren und dann wieder zum neuen, leeren Datensatz wechseln, ist das Nachschlagefeld leer (siehe Bild 13). Kein Wunder, denn das Feld MaterialID ist leer und die Tabelle tblHoehen hat keine Datensätze, in denen das Feld auch leer ist.

Beim Einfügen eines neuen Datensatzes, bei dem man zuvor mit F5 die Datensatzherkunft des Nachschlagefeldes aktualisiert hat, ist dieses leer.

Bild 13: Beim Einfügen eines neuen Datensatzes, bei dem man zuvor mit F5 die Datensatzherkunft des Nachschlagefeldes aktualisiert hat, ist dieses leer.

Also wählen wir hier erst einen Wert für MaterialID aus und aktualisieren dann mit F5. Dadurch will Access den Datensatzzeiger auf den ersten Datensatz verschieben. Das gelingt allerdings nicht, da der neue Datensatz nicht vollständig ist – die beiden Felder Hoehe und Breite müssen mit Datensätzen der Tabellen tblHoehen und tblBreiten verknüpft werden. Also erhalten wir die Meldung aus Bild 14.

Fehlermeldung beim Verlassen eines unvollständigen Datensatzes

Bild 14: Fehlermeldung beim Verlassen eines unvollständigen Datensatzes

Nachdem wir diese bestätigt haben und nochmal das Nachschlagefeld aufklappen, finden wir dort die gewünschten Daten vor (siehe Bild 15). Da wir nun das gewünschte Ergebnis haben, nehmen wir zwei Schritte vor:

Korrekte Darstellung der gewünschten Daten nach Aktualisierung

Bild 15: Korrekte Darstellung der gewünschten Daten nach Aktualisierung

  • Erstens die Anpassung des Nachschlagefeldes für das Fremdschlüsselfeld Breite und
  • zweitens den Bau eines Formulars, das die hier vorkommenden Fehlermeldungen abfängt beziehungsweise die Daten der Nachschlagefelder aktualisiert, ohne Datensatz zu wechseln und somit die Fehlermeldungen überhaupt gar nicht anzeigt.

Den ersten Schritt erledigen wir wie in Bild 16. Wir fügen die Tabelle tblBreiten zum Abfrageentwurf hinzu und stellen die Eigenschaften im Bereich Nachschlagen wie in der Abbildung ein.

Erweiterung der Abfrage um die Tabelle tblBreiten und Einstellung des Nachschlagefeldes

Bild 16: Erweiterung der Abfrage um die Tabelle tblBreiten und Einstellung des Nachschlagefeldes

Formular erstellen

Für den zweiten Teil erstellen wir ein neues Formular namens frmMaterialpreiseHoehenBreiten und öffnen es in der Entwurfsansicht. Stellen Sie die Abfrage qryMaterialpreiseHoehenBreiten als Datenherkunft für das Formular ein. Außerdem legen Sie für die Eigenschaft Standardansicht den Wert Datenblatt fest. Ziehen Sie alle Felder der Datenherkunft aus der Feldliste in den Entwurf des Formulars (siehe Bild 17).

Entwurf des Formulars frmMaterialpreiseHoehenBreiten

Bild 17: Entwurf des Formulars frmMaterialpreiseHoehenBreiten

Wechseln Sie nun in die Datenblattansicht, verhält sich das Formular genauso wie die Abfrage. Sie zeigt für die Nachschlagefelder Hoehe und Breite die Werte an, die zu der MaterialID passen, die für den beim Öffnen oben angezeigten Datensatz ausgewählt ist.

Wir wollen nun, dass beim Wechseln des Wertes für das Feld MaterialID oder auch beim Wechseln des Datensatzes die zur jeweiligen MaterialID gehörenden Werte der beiden Tabellen tblHoehen und tblTiefen angezeigt werden.

Dazu müssen wir für zwei Ereignisprozeduren entsprechende Prozeduren hinterlegen. Das erste ist das Ereignis Beim Anzeigen des Formulars, die beim Wechseln des Datensatzes ausgelöst wird, das zweite das Ereignis Nach Aktualisierung des Nachschlagefeldes MaterialID. Dieses benennen wir zuvor in cboMaterialID um, genau wie wir die beiden Kombinationsfelder Hoehe und Breite in cboHoehe und cboBreite umbenennen. Beide resultierenden Ereignisprozeduren statten wir mit einem Aufruf der Prozedur NachschlagefelderAktualisieren aus:

Private Sub cboMaterialID_AfterUpdate()
     NachschlagefelderAktualisieren
End Sub
Private Sub Form_Current()
     NachschlagefelderAktualisieren
End Sub

Diese sieht schließlich wie folgt aus:

Private Sub NachschlagefelderAktualisieren()
     Dim strSQLHoehe As String

Dies war die Leseprobe dieses Artikels.
Melden Sie sich an, um auf den vollständigen Artikel zuzugreifen.

Bitte geben Sie die Zahlenfolge in das nachfolgende Textfeld ein

Die mit einem * markierten Felder sind Pflichtfelder.

Aktuell im Blog
Onlinebanking mit Access

Es ist geschafft: Endlich ist das Buch Onlinebanking mit Access fertiggeschrieben. Das war... [mehr]

Direktzugriff auf Tabellen und Felder

Die IntelliSense-Erweiterung für Tabellen und Felder hat mir soviel Spaß gemacht, dass ich gleich... [mehr]

IntelliSense für Tabellen und Felder

Wenn Sie mit dem VBA-Editor arbeiten und dort gelegentlich SQL-Anweisungen eingeben, müssen Sie... [mehr]

Download Access und SQL Server

Erfahren Sie, welche Schritte zum Download des aktuellen Stands des Buchs "Access und SQL Server"... [mehr]

Bilder in Access 2013

Wer die Bibliothek mdlOGL0710 von Sascha Trowitzsch oder ein ähnliches Modul aus meinen... [mehr]

Dynamische Ribbons

Immer wieder fragen Leser, wie man Ribbon-Elemente wie etwa Schaltflächen in Abhängigkeit... [mehr]

Die Blogmaschine

Einen kleinen Blog zusätzlich zum Shop zu betreiben ist eine tolle Sache. Hier lassen sich... [mehr]

Wegwerfadressen für die Newsletteranmeldung

Die Verwendung von Wegwerf-Adressen für die Nutzung aller möglichen Online-Dienste nimmt... [mehr]

Access und Facebook

Facebook und Access - das ist eine der wenigen Kombinationen, die ich noch nicht in die Mangel... [mehr]

Access und SQL Server - das Projekt

Mein neues Buch Access und SQL Server (gemeinsam mit Bernd Jungbluth) geht in die Endphase. Wer... [mehr]