Language: Deutsch English















Last Update: 2017 - 03 - 20





Darstellung von Daten einer 1:N-Beziehung mit einer Auswahlabfrage

von Philipp Stiefel, ursprünglich veröffentlicht 24. April 2016


Article header image, Kaffeefilter

Photo by Karl Fredrickson used here under CC0 licensing

Die häufigste Beziehung im Datenmodell einer relationalen Datenbank ist die 1-zu-N-Beziehung (oft auch 1:N geschrieben). Es gib einen Elterndatensatz (die 1-Seite) und keinen, einen oder mehrere Kind-Datensätze (die N-Seite) dazu. Beispiele dafür sind die Bestellungen eines Kunden, die Besuche eines Ortes/einer Lokalität oder die Wartung einer Maschine.

In diesem Artikel erkläre ich, wie man solche Daten in einer Microsoft Access Datenbank sinnvoll und möglichst übersichtlich und informativ mit einer Auswahlabfrage für den Benutzer aufbereiten kann. Die Anzeige für den Benutzer sollte am Ende natürlich ein einem Formular und nicht direkt in der Abfrage erfolgen.

Für die Eingabe solcher Daten eignet sich ein Hauptformular/Unterformular-Konstrukt, oder es gibt separate, alleinstehende Eingabeformulare für den Elterndatensatz und jeweils einen Kind-Datensatz. So oder so ist dies mit Access auch für Anfänger relativ leicht zu lösen.

Darstellung als Liste mit einer Auswahlabfrage

Etwas anders sieht das aus, wenn du primär die Elterndatensätze in einer Liste darstellen möchtest. Nur die Daten der Eltern-Seite darzustellen ist einfach, aber du möchtest vielleicht dem Benutzer weitere Informationen zu diesen Daten anzeigen, die sich aus den Kind-Datensätzen ergeben.

Wenn du einfach die Eltern- und die Kind-Tabelle in einer Abfrage über die jeweiligen Schlüsselfelder miteinander kombinierst, erhältst du so viele Datensätze, wie es Kinder gibt. Die Eltern-Daten werden dabei immer wiederholt. Dies mag bei einigen wenigen Datensätzen noch ein akzeptables Ergebnis sein, aber sobald dadurch mehr als 2-3 Wiederholungen pro Elterndatensatz entstehen, wird diese Darstellung völlig unübersichtlich und unbrauchbar.

Ergebnis einer Auswahlabfrage auf 1-zu-N-Daten

Ich habe als Beispiel Kunden und Bestellungen ausgewählt. Da es die Definition eines Kunden ist, dass er zumindest einmal etwas gekauft (bestellt) hat, gehe ich davon aus, dass es immer mindestens eine Bestellung gibt. In Fällen wo auch Elterndatensätze ohne ein Kind existieren können, sollte man einen LEFT JOIN anstelle des INNER JOIN in der Abfrage verwenden, um die Tabellen zu verknüpfen. Andernfalls werden die Elterndatensätze ohne Kind nicht angezeigt.

Mit aggregierten Daten der Kind-Datensätze abfragen

Die Anzahl der Bestellungen pro Kunde und der Gesamtwert aller Bestellungen sind wertvolle Informationen für einen Benutzer. Folglich würde es Sinn machen, diese Informationen ebenfalls in der Liste der Kunden anzuzeigen.

Eine Möglichkeit ist es, Aggregatfunktionen wie SUM (SUMME) oder COUNT (ANZAHL)zu verwenden, um die Daten aus der Kind-Tabelle zu aggregieren. Die SUM-Aggregatfunktion ermittelt die Gesamtsumme eines numerischen Felder über alle Ergebnisdatensätze eine Abfrage. Analog dazu ermittelt COUNT die Anzahl der Datensätze.

In der Regel will man nicht einfach die Gesamtsumme/Anzahl aller Datensätze, sondern in unserem Bespiel die Anzahl der Bestellungen pro Kunde und die Summe pro Kunde. Um das zu erreichen, muss man die Abfrage über die Spalten des Kunden gruppieren.

Die Abfrage kannst du mit dem Abfrageeditor von Access einfach erzeugen. Sie sieht dann etwa so aus:

Entwurfsansicht einer Auswahlabfrage mit Summe und Anzahl

Hier ist das SQL-Statement dazu. Ich habe es manuell für bessere Lesbarkeit überarbeitet und Aliase (k, b) für die Tabellen vergeben.

SELECT k.KundeId, k.Vorname, k.Nachname, Count(b.BestellungId) AS AnzahlBestellungen, Sum(b.Gesamtwert) AS SummeGesamtwert FROM tblKunde k INNER JOIN tblBestellung b ON k.KundeId = b.KundeId GROUP BY k.KundeId, k.Vorname, k.Nachname;

Und hier das Ergebnis der Abfrage

Ergebnis einer Auswahlabfrage mit Summe und Anzahl

Dies ist schon mal eine informativere und übersichtlichere Darstellung für die Benutzer Eurer Anwendung.

Daten des letzten Kind-Datensatzes anzeigen

Eine weitere wichtige Information für einen Kundenbetreuer in diesem Beispiel, wäre der Status der Bestellungen. Hier lässt sich aber nur schwer eine sinnvolle Information über alle Bestellungen mit einer Auswahlabfrage ermitteln. Die bedeutsamste Information dafür ist sicherlich der Status der letzten Bestellung.

Dein erster Gedanke dazu könnte sein, für diesen Zweck die LAST-Domänenaggregatfunktion (LetzterWert) zu verwenden. Leider sind die LAST- und FIRST-Funktionen (ErsterWert) für einen solchen Zweck eher ungeeignet. – Warum das so ist, erkläre ich bald in einem anderen Artikel.

Wesentlich bessere Ergebnisse erreicht man hier mit der MAX- bzw. MIN-Funktion, die den größten bzw. kleinsten Wert eines Feldes ermitteln. In unserem Beispiel ergibt sich aus dem größten Wert für Bestelldatum eindeutig das Datum der letzten Bestellung. – Dieses Datum allein ist schon hilfreich, aber wir wollen ja auch den Status der letzten Bestellung anzeigen und das wird leider etwas komplizierter.

Wir fangen erstmal mit dem Datum der letzten Bestellung pro Kunden an. Dafür erstelle ich eine weitere Abfrage.

Entwurf Auswahlabfrage mit MAX Ergebnis Auswahlabfrage mit MAX

Deren SQL-Text sieht so aus.

SELECT tblBestellung.KundeId, Max(tblBestellung.Bestelldatum) AS MaxBestelldatum FROM tblBestellung GROUP BY tblBestellung.KundeId;

Mit einer weiteren Abfrage, basierend auf den Ergebnissen dieser ersten Abfrage und der Tabelle tblBestellung, lässt sich jetzt der komplette Datensatz der letzten Bestellung ermitteln. Das klingt komplizierter als es ist. Hier ist die fertige Abfrage dafür.

SELECT b.* FROM tblBestellung b INNER JOIN qryKunde_MaxBestelldatum kmb ON b.Bestelldatum = kmb.MaxBestelldatum AND b.KundeId = kmb.KundeId;

Beachte, dass die Verknüpfung der Tabelle mit der Abfrage sowohl über die KundeId als auch über das (Max)Bestelldatum hergestellt wird. Die Abfrage qryKunde_MaxBestelldatum enthält dabei nur das jeweils letzte (größte) Bestelldatum.

Verknüpfung über mehrere Felder im Entwurf der Auswahlabfrage

Damit haben wir es fast geschafft. Wir integrieren jetzt nur diese Auswahlabfrage noch zusätzlich in unsere erste Abfrage mit der Anzahl der Bestellungen und deren Gesamtwert. Dabei verknüpfen wir die Abfrage über die KundeId.

Entwurf der Auswahlabfrage aggregierten Daten und letztem Kind-Datensatz
SELECT k.KundeId, k.Vorname, k.Nachname, Count(b.BestellungId) AS AnzahlBestellungen, Sum(b.Gesamtwert) AS SummeGesamtwert, klb.Bestelldatum AS LetztesBestelldatum, klb.Bestellstatus AS LetzterBestellstatus FROM (tblKunde k INNER JOIN tblBestellung b ON k.KundeId = b.KundeId) INNER JOIN qryKunde_LetzteBestellung klb ON k.KundeId = klb.KundeId GROUP BY k.KundeId, k.Vorname, k.Nachname, klb.Bestelldatum, klb.Bestellstatus;

Beachte, dass wir hier auch nach den Werten der Abfrage qryKunde_LetzteBestellung gruppieren müssen. In diesem Fall ist dies nur eine rein technische Notwendigkeit. Wir wissen zwar, dass es nur jeweils einen einzigen Datensatz pro Kunden in dieser Abfrage geben kann. Die Jet-/ACE-Datenbankengine, die die Abfrage ausführt, weiß das nicht, daher müssen wir dies als Gruppierung definieren.

Ebenfalls beachtenswert sind auch die Aliase LetztesBestelldatum und LetzterBestellstatus. Diese sind technisch nicht notwendig, aber sie machen die Aussage der Abfrage klarer und vermeiden Missverständnisse.

Mit dieser Abfrage haben wir jetzt unser Ziel erreicht, die Elterndatensätze zusammen mit relevanten Daten der Kind-Datensätze darzustellen.

Ergebnis Auswahlabfrage mit aggregierten Daten und letztem Kind-Datensatz

Lösungsweg als Video

Erstmals für einen Artikel auf codekabinett.com habe ich den Lösungsweg auch als Screen-Recording aufgenommen.

Wenn dir das Video gefällt und du in Zukunft mehr solche Videos zu Microsoft Access von mir sehen willst, dann like das Video auf YouTube oder teile es.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



© 1999 - 2016 by Philipp Stiefel