Unterabfragen als Feldinhalt

Dass gespeicherte Abfragen selbst wieder als Pseudotabellen in andere Abfragen integriert und dort mit anderen Tabellen verknüpft werden können, dürfte Ihnen bekannt sein. Weniger verbreitet jedoch sind jene Abfragen, die ungespeichert als SQL-Ausdruck zur Berechnung von Feldinhalten und Bedingungen dienen. Diese mit den Schlagwörtern Unterabfrage oder Subselect bezeichneten Ausdrücke sind Gegenstand dieses Beitrags.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1604_Unterabfragen.accdb

Unterabfragen, Subselects

Eine Unterabfrage (engl. Subselect) ist nichts weiter, als eine vollständige in SQL geschriebene Auswahlabfrage, deren Ergebnis oder Ergebnisse zur Anzeige eines einzelnen Felds oder zur Bestimmung eines Auswahlkriteriums in einer anderen Abfrage dienen. Im Folgenden verwenden wir einige Beispiele, um verschiedene Möglichkeiten im Umgang mit diesen Unterabfragen zu demonstrieren.

Grundlage für die Beispieldatenbank ist das Datenmodell in Bild 1, welches Kunden und deren Bestellungen verwaltet. Neben den Kundendaten, bestehend aus der Tabelle tblKunden und deren Nachschlagetabellen tblAnreden, tblOrte und tblLaender, sind die Bestellungen in der Tabelle tblBestellungen festgehalten und zu jeder Bestellposition jeweils ein Datensatz in der Tabelle tblBestelldetails. Die Artikel jeder Bestellposition kommen schließlich aus der Nachschlagetabelle tblArtikel.

Bild 1: Beziehungen zwischen Kunden und deren Bestellungen im Datenmodell der Beispieldatenbank

Auswertungen über Subselects

Eine Anforderung an eine Auswertung könnte nun sein, die Anzahl von Bestellungen in einer übersicht darzustellen, die jeder Kunde getätigt hatte. Die dafür geeignete Abfrage wäre eine Gruppierungs– beziehungsweise Aggregatabfrage. Bild 2 zeigt, wie Sie diese gestalten könnten. Aus der Kundentabelle entnehmen wir lediglich die ID der Datensätze und den vollständigen Namen Kunde, welcher sich aus Nachname und Vorname zusammensetzt:

Bild 2: Die Abfrage gibt nur jene Kunden aus, die mindestens einmal bestellten

...Nachname & ', ' & Vorname AS Kunde

Um die Anzahl von Bestellungen zu jedem Kunden zu ermitteln, wird die Tabelle tblBestellungen mit der Kundentabelle 1:1 verknüpft. Die Berechnung der Anzahl erfordert als Aggregat, dass Sie aus der Abfrage eine Gruppierungsabfrage machen, indem Sie das Summensymbol ? im Entwurfs-Ribbon anklicken. Für die nun eingeblendete Zeile Funktion geben Sie bei ID und Kunde die Gruppierung an, und beim Feld Anzahl Bestellungen die ID von tblBestellungen, sowie die Funktion Anzahl. Die Bedingung, dass diese Anzahl größer, als 0, sein muss, ist eigentlich überflüssig, denn durch die 1:1-Verknüpfung werden ohnehin alle Datensätze ausgefiltert, die keinen Bezug zwischen den Tabellen besitzen.

Im Ergebnis haben Sie nun alle Kunden, die überhaupt etwas bestellten und die Gesamtzahl ihrer Bestellungen. Sie könnten noch nach diesem Feld absteigend sortieren, um die Top-Besteller zu ermitteln.

Diese Auswertung lässt sich aber auch anders realisieren. Statt die Tabelle tblBestellungen direkt in den Abfrageentwurf zu ziehen, wird sie in einen SELECT-Ausdruck als Unterabfrage integriert, der das Ausgabefeld Anzahl Bestellungen speist. Wie das aussieht, zeigt Bild 3. Hier wird bereits deutlich, dass solche Abfragen in der Entwurfsansicht etwas schwieriger zu handeln sind, da die SQL-Ausdrücke für die Unterabfragen relativ lang ausfallen und damit die Spaltenbreite dieser berechneten Felder ziemlich breit aufgezogen werden müsste. Entweder Sie verwenden dann die Zoom-Funktion für das Feld (rechter Mausklick), oder Sie schalten besser gleich in die SQL-Ansicht, die den Code von Listing 1 zutage fördert. Das Ergebnisfeld Anzahl Bestellungen ist hier mit n bezeichnet. Es errechnet sich über die Aggregatfunktion COUNT(*) auf die Datensätze von tblBestellungen. Da wir ja aber nicht für jeden Kunden die Gesamtzahl aller Bestellungen ausgeben möchten, sondern nur die Zahl seiner Bestellungen, braucht es noch eine zusätzliche WHERE-Bedingung. Pro Kundendatensatz müssen die Bestellungen gemäß seiner ID gefiltert werden.

Bild 3: Auch hier zeigen sich nur Kunden mit Bestellungen, jedoch mit Unterabfrage

Hier kommt nun ein Trick ins Spiel: Die Tabelle tblBestellungen wird in den Alias-Namen TMP umbenannt. Auf ihr Feld KundeID können Sie somit über TMP.KundeID Bezug nehmen. Ihr Wert muss mit der ID der Tabelle tblKunden übereinstimmen, damit die Filterung greift. Also setzen Sie diese Bedingung ein:

...WHERE TMP.KundeID = tblKunden.ID

Das Ergebnis stimmt mit dem der Abfrage aus Bild 2 überein.

Man könnte meinen, dass die Performance dieser Abfrage darunter litte, dass für jeden Kundendatensatz die Unterabfrage zur Anzahlberechnung erneut ausgeführt werden muss. Tatsächlich unterscheidet sie sich nicht nennenswert von der gruppierten Abfrage oben, denn auch bei Aggregatabfragen muss intern für jeden Datensatz eine eigene neue Berechnung durchgeführt werden.

Nummerieren

Der prominenteste Vertreter von Unterabfragen ist die Lösung zum Durchnummerieren der ausgegebenen Datensätze. Sie möchten etwa alle Kunden der Datenbank ausgeben und die Datensätze fortlaufend nummerieren. Unter Excel wäre das ein leichtes Unterfangen. Sie fügten hier einfach ganz vorn eine zusätzliche Spalte ein, schrieben eine 1 und eine 2 in die beiden ersten Zellen hinein und zögen die Spalte nach Markierung dieser zwei Zellen über die ganze Kundentabelle auf.

Leider gestaltet sich das unter Access weitaus schwieriger. Eine Nummerierungsfunktion fehlt hier.

über eine gewiefte Unterabfrage lässt sich die Nummerierung dennoch erreichen. Voraussetzung allerdings ist, dass die Tabelle einen Primärschlüssel, wie die Autowert-ID von tblKunden, aufweist. Ohne diesen wäre die Sache nur über eine gar nicht so einfache VBA-Funktion zu lösen.

Das Feld Pos in Bild 4 stellt das Nummernfeld dar. Es berechnet sich über eine Unterabfrage, die in Listing 2 besser zu überblicken ist. Zunächst ermittelt COUNT die Anzahl der Datensätze der Tabelle tblKunden. Hätte sie 9.000 Datensätze, so gäbe Pos in jeder Zeile eben diesen Wert aus, was nicht wünschenswert ist. Eine zusätzliche Bedingung ändert aber die Situation.

Bild 4: Durchnummerieren der Kunden über Unterabfrage

SELECT 1+(SELECT COUNT(*) FROM tblKunden AS TMP WHERE TMP.ID
Listing 2: SQL-Code der Abfrage zum Durchnummerieren der Kunden
Auch hier wird die Tabelle intern über den Alias in TMP umbenannt. Anschließend filtert die WHERE-Bedingung diese TMP-Tabelle so, dass nur jene Datensätze berücksichtigt werden, deren ID (Autowert!) kleiner ist, als die aktuell im Datensatz stehende ID der  angezeigten tblKunden:
...WHERE TMP.ID < tblKunden.ID
Also filtert die Abfrage zu jedem Kundendatensatz die virtuelle Kundentabelle (TMP) so, dass ihre Datensatzzahl der entspricht, die bis zu dieser Position erreicht wurde. Diese Anzahl wird in Pos als Nummerierung zweckentfremdet.
Eine absteigende Nummerierung erreichen Sie, indem Sie statt des Kleiner-Zeichens das Größer-Zeichen  in die Bedingung einsetzen. Jede Tabelle mit einem Autowert lässt sich auf diese Weise durchnummerieren. Später zeigen wir noch ein Beispiel, wie sich die Artikel jeder Bestellung durchnummerieren lassen. Die Unterabfrage dazu fällt etwas umfangreicher aus, weil sie noch weitere Bedingungen enthalten muss.
Unterabfragen für Bedingungen
Eine Unterabfrage gibt einen oder mehrere Datensätze zurück. Soll ein Feldwert ermittelt werden, so darf die Zahl dieser Datensätze naturgemäß nicht größer sein, als 1. Schließlich kann eine Feldzelle ja nicht mehr anzeigen, als einen Wert. Die Aggregatfunktion COUNT etwa garantiert dies, ebenso, wie FIRST (Erster Wert) oder LAST (Letzter Wert) oder AVG (Mittelwert). Verwenden Sie hier Unterabfragen, die mehrere Datensätze zurückliefern, so wird Access bereits beim Abspeichern der Abfrage meckern und Sie darauf aufmerksam machen, dass eine solche Unterabfrage nur einen Wert zurückgeben darf.
Anders bei Unterabfragen, die für Bedingungen benutzt werden. Als Beispiel möchten wir alle Kunden ausgeben, die irgendwann Bestellungen tätigten. Das Kriterium dafür ist, dass ihre Kunden-ID in der Tabelle tblBestellungen irgendwo auftaucht. Die Abfrage in Bild 5 baut auf der früheren auf, in der die Gesamtzahl aller Bestellungen eines Kunden bestimmt wurde. Auch hier findet für diese Feldberechnung eine Unterabfrage Anwendung. Der Nachteil dieser Lösung besteht darin, dass alle Kunden ausgegeben werden, also auch jene, bei denen COUNT den Wert 0 zurückgibt. Natürlich könnte man als Kriterium für dieses Feld den Ausdruck >0 bestimmen. Die Abfrage müsste dann für jeden Kunden seine Bestellzahl ermitteln und aus dem Ergebnis all jene streichen, deren Wert 0 betrüge. Eine Vorfilterung macht diese Abfrage aber schneller.

Bild 5: Alle Kunden mit Bestellungen über IN-Statement auf tblBestellungen
Möchten Sie weiterlesen? Dann lösen Sie Ihr Ticket!

Hier geht es zur Bestellung des Jahresabonnements des Magazins Access [basics]:

Zur Bestellung ...

Danach greifen Sie sofort auf alle rund 400 Artikel unseres Angebots zu - auch auf diesen hier!

Oder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:


	
	


		
		

			

				Username or Email
				
			
			

				Passwort
				
			
						

				
				Angemeldet bleiben