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).
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.
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).
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.
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.
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.
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.
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.
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.
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).
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).
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.
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.
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.
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:
- 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.
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).
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.