Für Daten mit einer m:n-Beziehung gibt es einige Beispiele: Fahrzeuge und ihre Ausstattungsmerkmale, Artikel und Kategorien, Mitarbeiter und Positionen et cetera. Üblicherweise stellt man diese mit mehreren Steuerelementen dar – etwa die Fahrzeuge im Hauptformular und die Ausstattung im Unterformular. Oder die Mitarbeiter landen im Hauptformular und zwei Listenfelder zeigen die zugeordneten und die nicht zugeordneten Abteilungen an. Was aber, wenn die Daten gar nicht bearbeitet, sondern nur angezeigt werden sollen – und dies möglichst noch in einer einzigen Zeile Dann hilft die Lösung aus diesem Beitrag weiter.
Verknüpfte Daten nebeneinander
Ziel des Beitrags ist es, eine Darstellung wie in Bild 1 zu erhalten. Die ersten paar Spalten sollen die herkömmlichen Felder der m-Tabelle der Verknüpfung liefern. Die folgenden Spalten sollen im Spaltenkopf den Inhalt und die Nummer anzeigen, also etwa Kategorie 1, Kategorie 2 et cetera. Darunter erscheinen schließlich die verknüpften Daten.
Bild 1: Darstellung von per m:n-Beziehung verknüpften Daten in einer Zeile
Beispiel Artikel und Kategorien
Die beiden Tabellen tblArtikel und tblKategorien sind in der Südsturm-Beispieldatenbank eigentlich über das Fremdschlüsselfeld KategorieID der Tabelle tblArtikel verknüpft. Auf diese Weise lässt sich aber nur je eine Kategorie je Artikel festlegen. Was aber, wenn Sie die Kategorien etwas verfeinern möchten und ein Artikel mehreren Kategorien zugeteilt werden soll In diesem Fall benötigen wir eine m:n-Verknüpfungstabelle, die alle denkbaren Kombinationen aus Artikeln und Kategorien speichern kann.
Im Beziehungen-Fenster sieht dies wie in Bild 2 aus; dort können Sie auch erkennen, dass wir das Feld KategorieID aus der Tabelle tblArtikel entfernt haben.
Bild 2: m:n-Beziehung zwischen Artikeln und Kategorien
Die Verknüpfungstabelle tblKategoriezuordnungen enthält drei Felder – das Primärschlüsselfeld sowie zwei Fremdschlüsselfelder, welche beliebige Kombinationen aus den Datensätzen der Tabellen tblArtikel und tblKategorien erlauben. Allerdings schränkt ein zusammengesetzter Index über diese beiden Felder die Werte dahingehend ein, dass jede Kombination aus Artikel und Kategorie nur einmal vorkommen darf.
Dazu legen Sie neben dem Primärindex einen weiteren Index an, der die betroffenen Felder enthält und dessen Eigenschaft Eindeutig auf Ja eingestellt wurde (s. Bild 3).
Bild 3: Jede Kombination aus Artikel und Kategorie ist nur einmal möglich.
Sie können nun wie in Bild 4 einige Beispieldatensätze für die Zuordnung von Artikeln zu Kategorien hinzufügen.
Bild 4: Beispieldatensätze für die m:n-Beziehung
Liste per VBA erzeugen
Nun könnten Sie jedem Artikel per VBA eine entsprechende Liste der Kategorien hinzufügen – diese müsste jedoch regelmäßig aktualisiert werden. Die Liste soll in der Tabelle tblArtikel in einem weiteren Feld namens Kategorien gespeichert werden. Die VBA-Prozedur zum Füllen dieses Feldes sieht wie in Listing 1 aus.
Listing 1: Zusammenstellen der Kategorienliste per VBA
Public Sub KategorielisteSchreiben()
Dim db As DAO.Database
Dim rstArtikel As DAO.Recordset
Dim rstKategorien As DAO.Recordset
Dim strKategorien As String
Set db = CurrentDb
Set rstArtikel = db.OpenRecordset("SELECT * FROM tblArtikel", dbOpenDynaset)
Do While Not rstArtikel.EOF
strKategorien = ""
Set rstKategorien = db.OpenRecordset("SELECT t2.Kategoriename FROM" _
& "tblKategoriezuordnungen AS t1 INNER JOIN tblKategorien AS t2 ON t1.KategorieID = " _
& "t2.KategorieID WHERE t1.ArtikelID = " & rstArtikel!ArtikelID, dbOpenDynaset)
Do While Not rstKategorien.EOF
strKategorien = strKategorien & ";" & rstKategorien!Kategoriename
rstKategorien.MoveNext
Loop
If Len(strKategorien) > 0 Then
strKategorien = Mid(strKategorien, 2)
End If
With rstArtikel
.Edit
!Kategorien = strKategorien
.Update
End With
rstArtikel.MoveNext
Loop
Set db = Nothing
End Sub
Die Prozedur legen Sie in einem beliebigen Standardmodul an und starten diese nach dem Platzieren der Einfügemarke innerhalb der Prozedur mit der Taste F5.
Wenn Sie die Prozedur beispielsweise immer dann verwenden wollen, bevor die Artikeldaten in einem Formular angezeigt werden, rufen Sie es in der Ereignisprozedur auf, die durch das Ereignis Beim Laden des Formulars ausgelöst wird.
Die Prozedur selbst ist die einfachste Variante, um zum gewünschten Ergebnis zu kommen. Allerdings birgt das Ergebnis auch den Nachteil redundanter Daten in sich. Sobald einer der Artikel einer weiteren Kategorie zugeordnet wird oder eine Zuordnung entfällt, stimmen die Daten im Feld Kategorien nicht mehr.
Die Prozedur erstellt zunächst ein Recordset auf Basis der Tabelle tblArtikel. Per Do While durchläuft die Prozedur alle Datensätze der Tabelle tblArtikel in einer äußeren Schleife.
Innerhalb dieser Schleife erzeugt die Prozedur eine weitere Datensatzgruppe, die diesmal alle Datensätze der verknüpften Tabellen tblKategoriezuordnungen und tblKategorien enthält, bei denen das Feld ArtikelID mit der ID des aktuell in der äußeren Schleife bearbeiteten Artikel-Datensatzes übereinstimmt. Auch die Datensätze dieses Recordsets werden innerhalb einer Do While-Schleife durchlaufen. Dabei stellt die Prozedur in der Stringvariablen strKategorien eine per Semikola getrennte Liste der Kategorienamen zusammen.
Dabei wird mit jedem Durchlauf ein Ausdruck wie ;<Kategoriename> zur Zeichenkette in strKategorien hinzugefügt. Sollte diese zumindest eine Kategorie enthalten, muss natürlich noch das führende Semikolon entfernt werden, was eine entsprechende Anweisung mit der Mid-Funktion erledigt.
Schließlich trägt die Prozedur die Kategorienliste in das Feld Kategorien des aktuellen Datensatzes des Recordsets rstArtikel ein.
Das Ergebnis können Sie in Bild 5 begutachten. Alle Datensätze der Tabelle tblArtikel enthalten im Feld Kategorien einen oder mehrere durch Semikola getrennte Kategorienamen.
Bild 5: Tabelle mit der Kategorienliste im Feld Kategorien
Kategorieliste per Abfrage
Die fehlende Dynamik der Zusammenstellung der Kategorieliste per VBA stellt uns noch nicht ganz zufrieden. Gibt es denn keine Möglichkeit, eine solche Liste immer aktuell zu halten, ohne jedes Mal eine Prozedur aufrufen zu müssen Klar: Wenn Sie etwa mit dem SQL Server arbeiten, können Sie einen Trigger einsetzen, der änderungen in der Tabelle tblKategoriezuordnungen direkt in das Feld Kategorien der Tabelle tblArtikel überträgt.
Allerdings verwendet ja längst nicht jede Access-Datenbank ein SQL Server-Backend. Also suchen wir nach einer alternativen Lösung, zum Beispiel einer Abfrage.
Im ersten Schritt wollen wir eine Abfrage erhalten, die für eine bestimmte Anzahl von Kategorien je Artikel vorbereitet ist – sagen wir für zehn Stück. Die Abfrage sollte also in zehn Feldern mit den Namen Kategorie 1, Kategorie 2 und so weiter die dem Artikel zugewiesenen Kategorien anzeigen. Wenn ein Artikel keine zehn Kategorien aufweist, bleiben die hinteren Kategorie-Felder einfach leer.
Im zweiten Schritt wandeln wir die resultierende Abfrage so um, dass Sie genau die gleiche Darstellung wie in der obigen Lösung mit der per VBA zusammengestellten Kategorienliste erhalten!
Die Lösung: Eine Kreuztabellenabfrage
Der Weg zur Lösung dieser Aufgabe bestand größtenteils darin, eine Möglichkeit zu finden, um die zugewiesenen Kategorien den entsprechenden Feldnamen, also Kategorie 1, Kategorie 2 und so weiter zuzuweisen.
Wir brauchen also eine Abfrage, die je nach Anforderung beliebig viele Kategoriefelder zum Abfrageentwurf hinzufügt. Die Lösung fiel leicht, da es überhaupt nur eine solche Möglichkeit gibt – nämlich die Kreuztabellenabfrage. Sie erlaubt es, die Werte eines Feldes der Datenherkunft als Spaltenüberschriften zu verwenden – neben einer oder mehreren echten Spaltenüberschriften.
Wir benötigen also eine Darstellung, deren erste beiden Felder etwa ArtikelID und Artikelname anzeigen. Die übrigen Spalten soll die Kreuztabelle mit den Überschriften Kategorie 1, Kategorie 2 und so weiter füllen.
Erster Schritt: Basisabfrage erstellen
Dummerweise enthält aber keine der Tabellen tblArtikel, tblKategoriezuordnungen oder tblKategorien ein Feld namens Kategorie 1. Wir benötigen aber eine Datenherkunft, welche sowohl einen Verweis auf den Artikeldatensatz und auf die jeweilige Kategorie liefert und gleichzeitig noch ein Feld mit den Werten Kategorie 1, Kategorie 2 et cetera füllt.
Hier kommt eigentlich nur eine Abfrage auf Basis der Verknüpfungstabelle tblKategoriezuordnungen infrage, die in der Datenblattansicht wie in Bild 6 aussieht. Wie aber erhalten wir ein Feld namens Kategorienummer, dessen Werte genau für jede Kombination aus Artikel und Kategorie von 1 bis n durchnummeriert sind
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo