{"id":55000884,"date":"2013-06-01T00:00:00","date_gmt":"2020-05-22T21:35:38","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=884"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"mnDaten_in_einer_Zeile_anzeigen","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/mnDaten_in_einer_Zeile_anzeigen\/","title":{"rendered":"m:n-Daten in einer Zeile anzeigen"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/b><\/p>\n

Verknüpfte Daten nebeneinander<\/b><\/p>\n

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<\/b>, Kategorie 2 <\/b>et cetera. Darunter erscheinen schließlich die verknüpften Daten.<\/p>\n

\"pic001.png\"<\/p>\n

Bild 1: Darstellung von per m:n-Beziehung verknüpften Daten in einer Zeile<\/span><\/b><\/p>\n

Beispiel Artikel und Kategorien<\/b><\/p>\n

Die beiden Tabellen tblArtikel <\/b>und tblKategorien <\/b>sind in der Südsturm-Beispieldatenbank eigentlich über das Fremdschlüsselfeld KategorieID<\/b> der Tabelle tblArtikel <\/b>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.<\/p>\n

Im Beziehungen-Fenster sieht dies wie in Bild 2 aus; dort können Sie auch erkennen, dass wir das Feld KategorieID <\/b>aus der Tabelle tblArtikel <\/b>entfernt haben.<\/p>\n

\"pic002.png\"<\/p>\n

Bild 2: m:n-Beziehung zwischen Artikeln und Kategorien<\/span><\/b><\/p>\n

Die Verknüpfungstabelle tblKategoriezuordnungen <\/b>enthält drei Felder – das Primärschlüsselfeld sowie zwei Fremdschlüsselfelder, welche beliebige Kombinationen aus den Datensätzen der Tabellen tblArtikel <\/b>und tblKategorien <\/b>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.<\/p>\n

Dazu legen Sie neben dem Primärindex einen weiteren Index an, der die betroffenen Felder enthält und dessen Eigenschaft Eindeutig <\/b>auf Ja<\/b> eingestellt wurde (s. Bild 3).<\/p>\n

\"pic003.png\"<\/p>\n

Bild 3: Jede Kombination aus Artikel und Kategorie ist nur einmal möglich.<\/span><\/b><\/p>\n

Sie können nun wie in Bild 4 einige Beispieldatensätze für die Zuordnung von Artikeln zu Kategorien hinzufügen.<\/p>\n

\"pic004.png\"<\/p>\n

Bild 4: Beispieldatensätze für die m:n-Beziehung<\/span><\/b><\/p>\n

Liste per VBA erzeugen<\/b><\/p>\n

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<\/b> in einem weiteren Feld namens Kategorien <\/b>gespeichert werden. Die VBA-Prozedur zum Füllen dieses Feldes sieht wie in Listing 1<\/a><\/span> aus.<\/p>\n

Listing 1: Zusammenstellen der Kategorienliste per VBA<\/p>\n

Public Sub KategorielisteSchreiben()\r\n    Dim db As DAO.Database\r\n    Dim rstArtikel As DAO.Recordset\r\n    Dim rstKategorien As DAO.Recordset\r\n    Dim strKategorien As String\r\n    Set db = CurrentDb\r\n    Set rstArtikel = db.OpenRecordset("SELECT * FROM tblArtikel", dbOpenDynaset)\r\n    Do While Not rstArtikel.EOF\r\n         strKategorien = ""\r\n        Set rstKategorien = db.OpenRecordset("SELECT t2.Kategoriename FROM" _\r\n            & "tblKategoriezuordnungen AS t1 INNER JOIN tblKategorien AS t2 ON t1.KategorieID = " _\r\n            & "t2.KategorieID WHERE t1.ArtikelID = " & rstArtikel!ArtikelID, dbOpenDynaset)\r\n        Do While Not rstKategorien.EOF\r\n            strKategorien = strKategorien & ";" & rstKategorien!Kategoriename\r\n            rstKategorien.MoveNext\r\n        Loop\r\n        If Len(strKategorien) > 0 Then\r\n            strKategorien = Mid(strKategorien, 2)\r\n        End If\r\n        With rstArtikel\r\n            .Edit\r\n            !Kategorien = strKategorien\r\n            .Update\r\n        End With\r\n        rstArtikel.MoveNext\r\n    Loop\r\n    Set db = Nothing\r\nEnd Sub<\/pre>\n
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<\/b>.<\/p>\n
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 <\/b>des Formulars ausgelöst wird.<\/p>\n
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 <\/b>nicht mehr.<\/p>\n
Die Prozedur erstellt zunächst ein Recordset auf Basis der Tabelle tblArtikel<\/b>. Per Do While <\/b>durchläuft die Prozedur alle Datensätze der Tabelle tblArtikel <\/b>in einer äußeren Schleife.<\/p>\n
Innerhalb dieser Schleife erzeugt die Prozedur eine weitere Datensatzgruppe, die diesmal alle Datensätze der verknüpften Tabellen tblKategoriezuordnungen <\/b>und tblKategorien <\/b>enthält, bei denen das Feld ArtikelID <\/b>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<\/b>-Schleife durchlaufen. Dabei stellt die Prozedur in der Stringvariablen strKategorien <\/b>eine per Semikola getrennte Liste der Kategorienamen zusammen.<\/p>\n
Dabei wird mit jedem Durchlauf ein Ausdruck wie ;<Kategoriename> <\/b>zur Zeichenkette in strKategorien <\/b>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<\/b>-Funktion erledigt.<\/p>\n
Schließlich trägt die Prozedur die Kategorienliste in das Feld Kategorien <\/b>des aktuellen Datensatzes des Recordsets rstArtikel <\/b>ein.<\/p>\n
Das Ergebnis können Sie in Bild 5 begutachten. Alle Datensätze der Tabelle tblArtikel <\/b>enthalten im Feld Kategorien <\/b>einen oder mehrere durch Semikola getrennte Kategorienamen.<\/p>\n
\"pic005.png\"<\/p>\n
Bild 5: Tabelle mit der Kategorienliste im Feld Kategorien<\/span><\/b><\/p>\n
<\/p>\n
Kategorieliste per Abfrage<\/b><\/p>\n
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 <\/b>direkt in das Feld Kategorien <\/b>der Tabelle tblArtikel <\/b>überträgt.<\/p>\n
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.<\/p>\n
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<\/b>, Kategorie 2 <\/b>und so weiter die dem Artikel zugewiesenen Kategorien anzeigen. Wenn ein Artikel keine zehn Kategorien aufweist, bleiben die hinteren Kategorie-Felder einfach leer.<\/p>\n
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!<\/p>\n
Die Lösung: Eine Kreuztabellenabfrage<\/b><\/p>\n
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<\/b>, Kategorie 2 <\/b>und so weiter zuzuweisen.<\/p>\n
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.<\/p>\n
Wir benötigen also eine Darstellung, deren erste beiden Felder etwa ArtikelID <\/b>und Artikelname <\/b>anzeigen. Die übrigen Spalten soll die Kreuztabelle mit den Überschriften Kategorie 1<\/b>, Kategorie 2 <\/b>und so weiter füllen.<\/p>\n
Erster Schritt: Basisabfrage erstellen<\/b><\/p>\n
Dummerweise enthält aber keine der Tabellen tblArtikel<\/b>, tblKategoriezuordnungen <\/b>oder tblKategorien <\/b>ein Feld namens Kategorie 1<\/b>. 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<\/b>, Kategorie 2 <\/b>et cetera füllt.<\/p>\n
Hier kommt eigentlich nur eine Abfrage auf Basis der Verknüpfungstabelle tblKategoriezuordnungen <\/b>infrage, die in der Datenblattansicht wie in Bild 6 aussieht. Wie aber erhalten wir ein Feld namens Kategorienummer<\/b>, dessen Werte genau für jede Kombination aus Artikel und Kategorie von 1 <\/b>bis n <\/b>durchnummeriert sind<\/p>\n
\"pic007.png\"<\/p>\n
Bild 6: Basisabfrage für die Kreuztabelle<\/span><\/b><\/p>\n
Das Rätsel lüftet ein Blick auf den Entwurf dieser Abfrage (s. Bild 7). Diese fügt dem Entwurfsraster neben den drei Feldern der Tabelle tblKategoriezuordnungen <\/b>noch ein weiteres Feld namens Kategorienummer <\/b>hinzu, dessen Ausdruck wie folgt aussieht:<\/p>\n
\"pic006.png\"<\/p>\n
Bild 7: Entwurfsansicht der Abfrage qryKategoriezuordnungenMitKategorienummer<\/span><\/b><\/p>\n
Kategorienummer: "Kategorie " & DomAnzahl("KategorieZuordnungID";"tblKategoriezuordnungen";"KategorieZuordnungID<=" \r\n& [KategorieZuordnungID] & " AND ArtikelID = " & [ArtikelID])<\/pre>\n
Was bewirkt dieser Ausdruck nun Er ermittelt die Anzahl aller Einträge der Tabelle tblKategoriezuordnungen<\/b>, deren Feld ArtikelID <\/b>den gleichen Wert enthält wie der aktuelle Datensatz und dessen Primärschlüsselfeld einen Wert enthält, der kleiner oder gleich dem Primärschlüsselfeld des aktuellen Datensatzes ist.<\/p>\n
Wenn der Artikel Chai <\/b>beispielsweise drei Kategorien zugeordnet ist, dann durchläuft Access beim Ermitteln des Abfrageergebnisses die drei Datensätze in der angegebene Sortierreihenfolge – hier nach dem Primärschlüsselwert. Der erste Wert im Feld KategoriezuordnungID <\/b>lautet 1<\/b>. Es gibt in der Tabelle tblKategoriezuordnungen <\/b>genau einen Wert, der kleiner oder gleich 1 <\/b>ist und die gleiche ArtikelID <\/b>aufweist – also ist die Anzahl 1<\/b>. Beim zweiten Datensatz mit dem Wert 2 <\/b>im Feld KategoriezuordnungID <\/b>findet die DomAnzahl<\/b>-Funktion genau zwei passende Datensätze – den mit dem Wert 1 <\/b>und den mit dem Wert 2 <\/b>im Primärschlüsselfeld. Dieser Nummerierung stellt die Abfrage noch den Ausdruck Kategorie<\/b> voran, sodass Texte wie Kategorie 1<\/b>, Kategorie 2 <\/b>und so weiter entstehen. Die Nummerierung beginnt dabei für jeden Artikel von vorn.<\/p>\n
Kreuztabelle erzeugen<\/b><\/p>\n
Nun liegt die gewünschte Datenherkunft vor. Nun wollen wir eine Kreuztabelle erstellen, deren Ergebnis beispielsweise wie in Bild 8 aussieht. Dort finden Sie jeweils die Werte der Felder ArtikelID <\/b>und Artikelname <\/b>plus die jeweils zugeordneten Kategorien.<\/p>\n
\"pic008.png\"<\/p>\n
Bild 8: Das gewünschte Aussehen der Kreuztabellenabfrage<\/span><\/b><\/p>\n
Welche Informationen benötigen wir außer den durch die Abfrage qryKategoriezuordnungenMitKategorienummer <\/b>gelieferten Daten<\/p>\n
Diese liefert beispielsweise nur die Fremdschlüsselfelder zu den Tabellen tblArtikel <\/b>und dem Feld Artikelname <\/b>sowie der Tabelle tblKategorien <\/b>und dem Feld Kategoriename<\/b>. Da aber sowohl der Artikelname als auch der Kategoriename in der Kreuztabelle angezeigt werden sollen, fügen wir noch die beiden Tabellen tblArtikel <\/b>und tblKategorien <\/b>zum Entwurf hinzu.<\/p>\n
Anschließend wandeln Sie die Abfrage mit dem Kontextmenü-Eintrag Abfragetyp|Kreuztabellenabfrage <\/b>in eine Kreuztabelle um.<\/p>\n
Die Kreuztabelle erwartet nun drei verschiedene Datenarten, die Sie durch Auswahl eines der Einträge in der Zeile Kreuztabelle <\/b>festlegen:<\/p>\n