Home > Artikel > Ausgabe 5/2012 > Gruppierungen in Abfragen

Gruppierungen in Abfragen

  PDF ansehen

  Download PDF und Beispieldatenbank

Abfragen lassen sich längst nicht nur dazu benutzen, die gewünschten Felder oder Datensätze einer oder mehrerer Tabellen darzustellen. Sie können Daten auch nach bestimmten Kriterien zu Gruppen zusammenfassen und für diese Gruppen verschiedene Informationen abfragen – beispielsweise die Summe bestimmter Zahlenwerte, den größten oder kleinsten Wert und vieles mehr. In diesem Artikel erfahren Sie alles über die Gruppierungsfunktionen in Abfragen.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1205_GruppierungenInAbfragen.mdb.

Abfragen gruppieren

Um eine Abfrage mit einer Gruppierung zu erstellen, legen Sie zunächst eine gewöhnliche Abfrage an. Fügen Sie die Tabellen hinzu, deren Daten Sie gruppiernen möchten und schließen Sie den Dialog Tabelle anzeigen. Nun können Sie die gewünschten Felder zum Entwurfsraster des Abfrageentwurfs hinzufügen.

Wie aber gruppieren Sie die Daten? Im aktuellen Zustand des Abfrageentwurfs gelingt dies gar nicht. Sie müssen dazu zunächst eine weitere Zeile im Entwurfsraster einblenden, was versionsübergreifend am einfachsten über das Kontextmenü des Entwurfsrasters gelingt. Dort finden Sie in älteren Access-Versionen den Eintrag Funktionen vor, in neuere Versionen heißt der Befehl Summen. In beiden Fällen wird dieser jedoch durch ein Summenzeichen gekennzeichnet (siehe Bild 1).

Einblenden der Zeile zum Festlegen der Gruppierungseigenschaften

Bild 1: Einblenden der Zeile zum Festlegen der Gruppierungseigenschaften

Gleich danach blendet Access eine neue Zeile im Entwurfsraster ein, mit dem sich die Einträge aus Bild 2 auswählen lassen.

Auswählen einer Gruppierungsfunktion

Bild 2: Auswählen einer Gruppierungsfunktion

Artikel pro Kategorie zählen

Im ersten Beispiel wollen wir herausfinden, wieviele Artikel jede Kategorie in der Tabelle tblArtikel stecken. Dazu fügen wir das Feld KategorieID zwei Mal zum Entwurfsraster der Abfrage hinzu. Warum gleich zwei Mal? Das erste Exemplare soll die Namen der Kategorien anzeigen, das zweite die Anzahl der Artikel je Kategorie.

Damit die erste Spalte den Namen der Kategorie ausgibt und die zweite die Anzahl der Artikel je Kategorie, wählen Sie für die erste Spalte in der Zeile Gruppierung den Eintrag Gruppierung und für die zweite den Eintrag Anzahl aus (siehe Bild 3).

Hinzufügen zweier Felder zum Ausgaben des Kategorienamens und der Anzahl der Artikel je Kategorie

Bild 3: Hinzufügen zweier Felder zum Ausgaben des Kategorienamens und der Anzahl der Artikel je Kategorie

Das Ergebnis erhalten Sie bereits, wenn Sie nun in die Datenblattansicht wechseln. Es sieht wie in Bild 4 aus – genau wie gewünscht! Hier gibt es eigentlich nur einen Verbesserungsvorschlag: Die Spaltenüberschrift AnzahlVonKategorieID haben wir erstens nirgends festgelegt und wirkt zweitens nicht besonders professionell. Access legt für Gruppierungsspalten, die mit einer Aggregatfunktion versehen wurden, automatisch eine aus der Aggregatfunktion und dem Feldnamen bestehende Spaltenüberschrift fest.

Ausgabe der Artikel je Kategorie

Bild 4: Ausgabe der Artikel je Kategorie

Dieses kleine Manko ist jedoch schnell behoben: Stellen Sie dem Feldnamen im Entwurfsraster einfach die gewünschte Spaltenüberschrift voran, schon sieht das Datenblatt perfekt aus (siehe Bild 5).

Finetuning der Spaltenüberschrift einer Aggregatspalte

Bild 5: Finetuning der Spaltenüberschrift einer Aggregatspalte

Gruppierung nach mehr als einem Feld

Dies war eine der einfachsten Gruppierungen, die Sie mit einer Access-Abfrage realisieren können. Um die Funktion des für das erste Feld gewählten Eintrags Gruppierungen richtig zu verstehen, verwenden wir im zweiten Beispiel ein zusätzliches Feld als Gruppierung – nämlich LieferantID.

Dieses Feld fügen Sie zwischen den beiden bereits vorhandenen Feldern ein. In der Zeile Funktionen legt Access automatisch den Eintrag Gruppierung fest – was in diesem Fall ja auch erwünscht ist. Der Entwurf sieht nun wie in Bild 6 aus.

Entwurf einer Abfrage mit zwei Gruppierungskriterien

Bild 6: Entwurf einer Abfrage mit zwei Gruppierungskriterien

Ein Wechsel in die Datenblattansicht zeigt, dass die Abfrage nun nicht mehr nur nach den Kategorien, sondern nach jeder Kombination aus Kategorie und Lieferant gruppiert und für jede Gruppe die Anzahl der Artikel liefert (siehe Bild 7).

Anzahl der Artikel je Kategorie und Lieferant

Bild 7: Anzahl der Artikel je Kategorie und Lieferant

Eine gruppierte Abfrage kombiniert also alle möglichen Kombinationen der Felder, die in der Zeile Funktion den Wert Gruppierung enthalten, und liefert weitere Daten entsprechend der dafür vorgesehenen Aggregatfunktionen.

Sie müssen übrigens für jede Spalte der Abfrage eine Funktion auswählen.

Aggregatfunktionen in Gruppierungen

In Gruppierungen können Sie die folgenden Aggregatfunktionen einsetzen:

  • Summe: Ermittelt die Summe des angegebenen Feldes der in der Gruppierung enthaltenen Datensätze.
  • Mittelwert: Ermittelt den Mittelwert, also die Summe der Werte der Gruppierung für das angegebene Feld geteilt durch die Anzahl der Elemente. Null-Werte werden nicht berücksichtigt.
  • Min: Liefert den kleinsten Wert der Gruppierung.
  • Max: Liefert den größten Wert der Gruppierung.
  • Anzahl: Liefert die Anzahl der Datensätze je Gruppierung. Null-Werte werden hier nicht berücksichtigt.
  • StAbw: Ermittelt die Standardabweichung für die Werte einer Gruppierung.
  • ErsterWert: Liefert den ersten Wert der Gruppierung.
  • LetzterWert: Liefert den letzten Wert einer Gruppierung.
  • Varianz: Ermittelt die Varianz. Wenn die Berechnung auf Basis von weniger als zwei Datensätzen erfolgt, ist das Ergebnis ein Null-Wert.

Einfache Berechnungen ohne Gruppierung

Eigentlich benötigen Sie gar keine Gruppierung. Sie können die Funktion-Zeile auch einfach nutzen, um Berechnungen auf Basis des vorliegenden Datenbestands durchzuführen.

Wenn Sie beispielsweise die Anzahl aller Artikel ermitteln möchten, verwenden Sie eine einfache Abfrage, die lediglich das Feld Lagerbestand der Tabelle tblArtikel enthält und für diese die Funktion Summe ausführt (siehe Bild 8).

Anzahl der Artikel mit der Summenfunktion

Bild 8: Anzahl der Artikel mit der Summenfunktion

Oder vielleicht möchten Sie einmal die Artikelpreise analysieren und Mittelwert, Standardabweichung und Varianz ermitteln. Dann hilft Ihnen eine wie in Bild 9 aufgebaute Abfrage weiter. Dies ist übrigens ein gutes Beispiel dafür, dass Sie auch mehrere Funktionen gleichzeitig in einer Abfrage einsetzen können.

Statistische Daten zum Artikelpreis

Bild 9: Statistische Daten zum Artikelpreis

Bestellungen nach Monat und Jahr

Im folgenden Beispiel soll die Anzahl der Bestellungen nach Monat und Jahr ausgegeben werden. Dazu ziehen Sie das Feld Bestelldatum der Tabelle tblBestellungen zwei Mal in das Entwurfsraster der Abfrage und das Feld BestellungID einmal. Für das Bestelldatum legen Sie die folgenden beiden Ausdrücke fest:

Jahr: Jahr([Bestelldatum])

Monat: Monat([Bestelldatum])

Für das Feld BestellungID wählen Sie in der Zeile Funktion den Eintrag Anzahl aus. Den Entwurf und das Ergebnis finden Sie in Bild 10.

Anzahl der Bestellungen nach Jahr und Monat

Bild 10: Anzahl der Bestellungen nach Jahr und Monat

Nun nehmen wir an, Sie möchten die Spalte mit dem Monat vorn und die Spalte mit dem Jahr erst als zweite Spalte anzeigen. Dann reicht es nicht aus, die beiden zu vertauschen, denn dann würde die Abfrage zunächst nach den Monaten und erst dann nach den Jahren sortieren (1/2010, 1/2011, 2/2010 ...). Um die Reihenfolge für dieses Beispiel richtig einzustellen, müssen Sie die Spalte für das Jahr kopieren und einfügen, dann an vor die erste Spalte verschieben und diese schließlich ausblenden. Erst dann zeigt Access die benötigten Ansicht an (siehe Bild 11).

Anzahl der Bestellungen nach Monat und Jahr

Bild 11: Anzahl der Bestellungen nach Monat und Jahr

Kriterien in Gruppierungen

Der Umgang mit Kriterien in Gruppierungen ist nicht ganz trivial. Angenommen, Sie möchten den Durchschnittswert aller Artikel mit einem Preis unter 10 Euro ermitteln.

Dann würden Sie vermutlich das Feld Einzelpreis in das Entwurfsraster ziehen, die Spalte Funktion aktivieren und dort den Eintrag Mittelwert auswählen. Außerdem legen Sie für dieses Feld noch das Kriterium <10 fest. Das Ergebnis aus Bild 12 überrascht jedoch ein wenig: Es ist schlicht leer.

Durchschnitt aller Artikel, die weniger als zehn Euro kosten?

Bild 12: Durchschnitt aller Artikel, die weniger als zehn Euro kosten?

Die Lösung ist eigentlich naheliegend: Das Kriterium wirkt sich nicht auf die Datensätze aus, die zur Ermittlung des Mittelwerts herangezogen werden, sondern auf den ermittelten Wert. Der liegt über zehn Euro, also gibt die Abfrage keinen Datensatz zurück.

Also fügen Sie das Feld Einzelpreis ein zweites Mal zum Entwurfsraster hinzu, wählen den Eintrag Bedingung aus der Zeile Funktion aus und tragen den Ausdruck <10 als Kriterium ein.

Dies liefert schließlich das korrekte Ergebnis (siehe Bild 13). Außerdem hat Access automatisch das Kontrollkästchen in der Spalte Anzeigen geleert. Bedingungsfelder werden im Ergebnis von Gruppierungsabfragen nicht eingeblendet.

Korrekte Ermittlung des Durchschnitts

Bild 13: Korrekte Ermittlung des Durchschnitts

Der Unterschied zwischen den beiden Varianten ist, dass im ersten Fall erst die Aggregatfunktion ausgeführt wird und dann erst das Kriterium berücksichtigt wird – im zweiten Fall läuft dies genau umgekehrt.

Aggregate auf Gruppierungen anwenden

Das nächste Beispiel zeigt, wie Sie den Durchschnittspreis der Artikel für alle Kategorien ausgeben.

Hier übernehmen Sie auch wieder das Feld KategorieID aus der Tabelle tblArtikel in die Abfrage und fügen das Feld Einzelpreis hinzu. Dieses statten Sie mit der Funktion Mittelwert aus und erhalten das Ergebnis aus Bild 14.

Mittelwerte der Einzelpreise für mehrere Kategorien

Bild 14: Mittelwerte der Einzelpreise für mehrere Kategorien

Vorsicht Null-Werte!

Beim Ermitteln des Ergebnisses von Aggregatfunktionen werden Null-Werte im untersuchten Feld nicht berücksichtigt. Das heißt beispielsweise, dass der Mittelwert nur auf Basis der Werte ermittelt wird, die nicht Null sind.

Das ist bei Mittelwerten, Summen et cetera auch in Ordnung. Aufpassen müssen Sie jedoch, wenn Sie die Anzahl von Datensätzen ermitteln. Sie dürfen dann nicht einfach irgendein Feld mit der Funktion Anzahl versehen, sondern sollten eines wählen, das für jeden Datensatz einen Wert enthält. Dies ist etwa bei Primärschlüsselfeldern mit Autowert immer der Fall.

Wenn Sie natürlich nur die Anzahl der Datensätze ermitteln möchten, die in einem bestimmten Feld einen Wert enthalten, müssen Sie dieses mit der Funktion Anzahl ausstatten.