<\/p>\n
Unterabfragen Was soll das nun wieder sein Ich kenne Unterformulare, aber Unterabfragen Nun: Unterabfragen sind ganz einfach Abfragen, deren Ergebnis als Kriterium in einer übergeordneten Abfrage verwendet wird oder deren Ergebnis als Teil des Ergebnisses der Hauptabfrage ausgegeben werden soll. Dabei gibt es für Unterabfragen spezielle Vorgaben, zum Beispiel dass diese nur ein einziges Feld zurückliefern dürfen (wobei das Feld auch eine Funktion eines Feldes sein kann wie eine Summe oder Anzahl). Wie genau man Unterabfragen definiert und wozu Du diese einsetzen kannst, zeige ich Dir in diesem Artikel.<\/p>\n
Die Beispiele dieses Artikels findest Du in der Datenbank 2301_AbfragenBasics_Unterabfragen.accdb<\/b>.<\/p>\nEs geht nicht ohne SQL-Ausdrücke<\/h2>\n
Die schlechte Nachricht vorneweg: Im Gegensatz zu den übrigen Abfragen, die wir in der Abfragen [basics]<\/b>-Beitragsreihe vorgestellt haben, kommen wir bei Unterabfragen nicht komplett mit dem Zusammenklicken von Abfragen über die Entwurfsansicht aus. Unterabfragen müssen nämlich als SQL-Ausdruck angegeben werden, also zum Beispiel wie folgt:<\/p>\n Die gute Nachricht lautet jedoch: In den meisten Fällen können wir diese SELECT-Abfragen zuvor wiederum mit dem Abfrageeditor zusammenstellen und holen uns den benötigten SQL-Ausdruck dann aus der SQL-Ansicht der Abfrage.<\/p>\n Unterabfragen sind immer Auswahlabfragen, die allerdings eine entscheidende Einschränkung aufweisen: Sie enthalten nur ein Ergebnisfeld. Wir können allerdings auf mehrere Tabellen, Kriterien, Gruppierungen und weitere Techniken zurückgreifen.<\/p>\n Unterabfragen kann man vor allem an zwei Stellen nutzen:<\/p>\n In einem berechneten Feld kann man das Ergebnis einer beliebigen Unterabfrage entweder als alleinigen Wert oder als Teil eines Ausdrucks mit anderen Informationen ausgeben. Wir könnten beispielsweise die Anzahl der Artikel der Tabelle in der gleichen Zeile mit den Daten des Artikels ausgeben. Das sieht zum Beispiel wie im Screenshot aus Bild 1 aus. Hier fügen wir also die folgende Abfrage als Unterabfrage hinzu:<\/p>\n Bild 1: Beispiel einer Unterabfrage zur Ermittlung der Artikelanzahl<\/p>\n Damit diese ihre Werte auch in dem von uns hinzugefügten Feld anzeigt, müssen wir einen Feldnamen für dieses berechnete Feld hinterlegen, in diesem Fall AnzahlArtikel<\/b>. Außerdem ist es zwingend erforderlich, dass wir die Unterabfrage in Klammern einfassen, sodass dieser Ausdruck zum Einsatz kommt:<\/p>\n Wechseln wir in die Datenblattansicht, erhalten wir das Ergebnis aus Bild 2. Auf die gleiche Weise können wir auch Daten aus anderen Tabellen anzeigen.<\/p>\n Bild 2: Die Anzahl der Artikel erscheint in jeder Zeile.<\/p>\n Das war ein sehr einfaches Beispiel, das kaum praktischen Nutzen hat. Was eher vorkommen könnte, wäre der Wunsch nach der Anzeige der Anzahl der Artikel der Kategorie des aktuellen Artikels. Sprich: Dies Hauptabfrage zeigt weiterhin alle Artikel der Tabelle tblArtikel <\/b>an. In der Unterabfrage wollen wir die Anzahl der Artikel ermitteln, die der Kategorie angehören, zu welcher der aktuelle Artikel der Hauptabfrage gehört. Dazu müssen wir nun in der Unterabfrage ein Kriterium hinzufügen, dass sich auf die Kategorie-ID des Artikels aus der Hauptabfrage bezieht.<\/p>\n Das ist nicht ganz so einfach, denn wie sollen wir bei der Formulierung des Kriteriums in der Unterabfrage das Feld KategorieID <\/b>der Hauptabfrage mit dem gleichnamigen Feld der Unterabfrage vergleichen – wenn beide sich auf die gleiche Tabelle beziehen Auch zu diesem Zweck gibt es in relationalen Datenbanken die Möglichkeit, einer Tabelle einen Aliasnamen zu geben. Wir legen also entweder in der Haupt- oder in der Unterabfrage fest, dass die Tabelle tblArtikel <\/b>unter einem anderen Namen angesprochen werden soll, beispielsweise t1<\/b>. Wir könnten auch für beide Instanzen der Tabelle tblArtikel <\/b>in der Datenbank einen Aliasnamen vergeben, beispielsweise t1<\/b> für die Tabelle in der Hauptabfrage und t2<\/b>, damit es richtig klar wird.<\/p>\n Um für die Tabelle tblArtikel <\/b>in der Hauptabfrage einen Aliasnamen festzulegen, aktivieren wir das Eigenschaftenblatt für diese Tabelle und legen den Aliasnamen für die Eigenschaft Alias <\/b>fest (siehe Bild 3).<\/p>\n Bild 3: Festlegen eines Aliasnamens für eine Tabelle einer Abfrage<\/p>\n Allerdings erscheint nun überall in der Abfrage t1 <\/b>statt tblArtikel<\/b>, was bei Verwendung mehrerer Tabellen in der Abfrage nicht zur Übersicht beiträgt (siehe Bild 4).<\/p>\n Bild 4: Tabelle mit Aliasname in einer Abfrage<\/p>\n Daher würden wir in diesem Fall eher der Tabelle in der Unterabfrage den Aliasnamen t1 <\/b>zuweisen (zuvor ersetzen wir den Aliasnamen der Tabelle in der Hauptabfrage allerdings wieder durch den eigentlichen Namen der Tabelle, also tblArtikel<\/b>). Um einer Tabelle im SQL-Code, den wir für Unterabfragen angeben müssen, mit einem Aliasnamen zu versehen, geben wir hinter dem Tabellennamen im FROM<\/b>-Bereich das AS<\/b>-Schlüsselwort gefolgt vom Aliasnamen an:<\/p>\n Dieser brauchen wir nun nur noch das Kriterium zuzuweisen, in dem wir die KategorieID<\/b> der Tabelle der Unterabfrage mit der der Tabelle in der Hauptabfrage abgleichen:<\/p>\n Diese Unterabfrage soll also die Anzahl der Datensätze aus der Tabelle tblArtikel <\/b>holen, deren KategorieID <\/b>mit der KategorieID<\/b> des aktuellen Datensatzes in der Abfrage entspricht. Dazu fassen wir die Unterabfrage noch in Klammern ein und stellen dieser den Namen des berechneten Feldes voran. Der Abfrageentwurf sieht anschließend wie in Bild 5 aus.<\/p>\n Bild 5: Ermitteln der Anzahl der Artikel der Kategorie des aktuellen Artikels<\/p>\n Wechseln wir jetzt in die Datenblattansicht, erscheint zu jedem Artikel die Anzahl der Artikel der gleichen Kategorie (siehe Bild 6).<\/p>\n Bild 6: Anzahl der Artikel der Kategorie des aktuellen Artikels<\/p>\n Im nächsten Beispiel wollen wir herausfinden, wie viele verknüpfte Datensätze es für die Datensätze der zu untersuchenden Tabelle gibt. Im konkreten Fall interessiert uns die Anzahl der Bestellungen eines jeden Kunden, die wir als zusätzliches Feld des Abfrageergebnisses ausgeben wollen. Dazu nutzen wir eine Abfrage, die in der Hauptabfrage Daten der Tabelle tblKunden <\/b>anzeigt und in einem berechneten Feld namens AnzahlBestellungen <\/b>die Anzahl der Datensätze der Tabelle tblBestellungen<\/b>, die mit dem aktuellen Kundendatensatz verknüpft sind. Die dazu benötigte Unterabfrage enthält wieder eine Bedingung, welche ein Feld der Tabelle der Unterabfrage mit einem Feld der Tabelle der Hauptabfrage abgleicht. Diesmal handelt es sich allerdings um unterschiedliche Tabellen, daher brauchen wir keinen Aliasnamen zu definieren. Der Entwurf der Unterabfrage ist in zu Bild 7 finden.<\/p>\n Bild 7: Entwurf der Abfrage zur Ermittlung der Anzahl der Bestellungen eines Kunden<\/p>\n Wechseln wir in die Datenblattansicht, liefert die Abfrage alle Kunden mit der Anzahl ihrer Bestellungen (siehe Bild 8).<\/p>\n Bild 8: Ausgabe der Anzahl der Bestellungen je Kunde<\/p>\n Mit dem Exists<\/b>-Schlüsselwort als Operator vor einer Unterabfrage können wir prüfen, ob diese Abfrage überhaupt Datensätze enthält. Exists <\/b>liefert dazu den Wert True <\/b>oder False <\/b>zurück. Diese Technik können wir beispielsweise nutzen, um Kunden auszugeben, die bisher noch keine Bestellung gemacht haben.<\/p>\n Das Ergebnis des berechneten Ausdrucks mit dem Exists<\/b>-Schlüsselwort und der Unterabfrage versehen wir noch mit einem Kriterium, beispielsweise Falsch<\/b>, um nur die Datensätze der Tabelle tblKunden <\/b>anzuzeigen, für die es noch keinen verknüpften Datensatz in der Tabelle tblBestellungen <\/b>gibt.<\/p>\n Das berechnete Feld mit der Unterabfrage erhält folgenden Ausdruck:<\/p>\n Wir betten diese wie in Bild 9 in die Abfrage ein und fügen noch den Wert Falsch<\/b> als Kriterium hinzu.<\/p>\n Bild 9: Abfrage zur Ermittlung aller Kunden, für die noch keine Abfrage existiert<\/p>\n Das Ergebnis sehen wir in Bild 10. Die Kunden bestellen also recht zuverlässig. Allerdings können diese Bestellungen auch bereits sehr lange zurückliegen. Mit einer kleinen Anpassung lässt sich allerdings schnell ermitteln, welche Kunden in letzter Zeit nicht mehr bestellt haben.<\/p>\n Bild 10: Übersicht der Kunden ohne Bestellung<\/p>\n Um also herauszufinden, welche Kunden beispielsweise noch keine Bestellung im Jahr 2023 getätigt haben, fügen wir der Unterabfrage einfach ein zusätzliches Kriterium hinzu – eines das die Bestelldatensätze auf solche mit einem Datum in 2023 einschränkt. Mehr zu Datumskriterien erfährst Du im Artikel Abfragen [basics]: Nach Datumsfeldern filtern <\/b>(www.access-basics.de\/592<\/b>). Die neue Unterabfrage lautet:<\/p>\n Damit liefert die Abfrage schon einige weitere Datensätze – und einen Ansatz, welchen Kunden man mal wieder ein Angebot machen könnte.<\/p>\n Vielleicht möchtest Du mal die drei teuersten Artikel jeder Kategorie ermitteln. Wenn wir unabhängig von der Kategorie die drei teuersten Artikel ermitteln wollen, nutzen wir eine absteigende Sortierung nach dem Feld Einzelpreis <\/b>und stellen die Eigenschaft Spitzenwerte <\/b>der Abfrage auf 3 <\/b>ein (siehe Bild 11).<\/p>\n Bild 11: Ermitteln der teuersten drei Artikel<\/p>\n Die drei teuersten Artikel einer bestimmten Kategorie können wir nun leicht ermitteln, indem wir in dieser Abfrage einfach das Feld Kategorie-ID <\/b>plus ein entsprechendes Kriterium hinzufügen (siehe Bild 12).<\/p>\n Bild 12: Die teuersten drei Artikel<\/p>\n Wenn wir aber in einem Abfrageergebnis wie Bild 13 in die drei teuersten Artikel einer jeden Kategorie ausgeben wollen, kommen wir so nicht weiter.<\/p>\n Bild 13: Die teuersten drei Artikel je Kategorie<\/p>\n Die zum Ziel führende Vorgehensweise sieht dann wie folgt aus: Wir erstellen eine Unterabfrage, die jeweils die TOP 3 <\/b>Artikel absteigend sortiert nach dem Einzelpreis liefert und die ein Kriterium aufweist, nach dem die Kategorie der Kategorie des Datensatzes in der Hauptabfrage entspricht. Diese Unterabfrage liefert je nach Kategorie immer drei Datensätze der Artikel-Tabelle beziehungsweise die ArtikelID <\/b>der jeweiligen Datensätze.<\/p>\n Für die Kategorie Getränke <\/b>liefert die Unterabfrage also beispielsweise so etwas: 38, 43, 2<\/b>. Da die Hauptabfrage das Feld ArtikelID <\/b>nach diesen Werten filtert, erhalten wir das gewünschte Ergebnis. Zusätzlich sortieren wir die Datensätze in der Hauptabfrage zuerst aufsteigend nach der Kategorie, damit wir die Datensätze der Kategorien zusammenhängend sehen, und absteigend nach dem Einzelpreis.<\/p>\n Der Clou ist, dass wir die Unterabfrage nicht einfach in Klammern als Kriterium angeben, sondern dass wir dieser das In<\/b>-Schlüsselwort voranstellen. Das bewirkt, dass wir alle Datensätze erhalten, die von der Unterabfrage zurückgeliefert werden. Der Entwurf ist in Bild 14 dargestellt.<\/p>\n Bild 14: Entwurf der Abfrage zum Ermitteln der teuersten drei Artikel je Kategorie<\/p>\n Ein weiteres Beispiel, das wir uns anschauen wollen, haben wir in einem anderem Artikel namens Abfragen [basics]: Duplikate per Assistent suchen<\/b> (www.access-basics.de\/598<\/b>) mit dem Assistent für die Duplikatsuche erzeugt. Hier haben wir für eine Tabelle namens tblAdressen <\/b>alle Datensätze ermittelt, deren E-Mail-Adresse in mehr als einem Datensatz vorkommt. Der Entwurf dieser Abfrage sieht wie in Bild 16 aus.<\/p>\n Bild 15: Abfrage zum Finden von Duplikaten<\/p>\n Die Unterabfrage nutzt diesmal eine Gruppierung nach dem Feld EMail <\/b>und zählt deren Anzahl. Mehr zum Thema Gruppierungen erfährst Du im Artikel Abfragen [basics]: Gruppierungen nutzen <\/b>(www.access-basics.de\/593<\/b>).<\/p>\n Diese Unterabfrage liefert eine Liste von E-Mails zurück, deren Anzahl in der Unterabfrage größer als 1 <\/b>ist, die also mindestens doppelt vorkommen.<\/p>\n Diese Liste wird wieder mit dem Schlüsselwort In <\/b>als Kriterium des Feldes EMail <\/b>der Hauptabfrage verwendet. Somit liefert die Abfrage als Ergebnis jeden einzelnen Datensatz, der eine der doppelten E-Mail-Adressen aus der Unterabfrage enthält – was wiederum wie in Bild 15 aussieht.<\/p>\nSELECT KundeID FROM tblKunden<\/pre>\n
SELECT ist Trumpf<\/h2>\n
Wo kann man Unterabfragen einsetzen<\/h2>\n
\n
Unterabfrage als Ausdruck<\/h2>\n
![\"Beispiel](\"..\/tl_files\/images\/pic_616_002.png\")
<\/a><\/p>\nSELECT Count(*) FROM tblArtikel<\/pre>\n
AnzahlArtikel: (SELECT Count(*) FROM tblArtikel)<\/pre>\n
<\/a><\/p>\nUnterabfrage als Ausdruck mit Bezug zur Hauptabfrage<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\nSELECT Count(*) FROM tblArtikel AS t1 <\/pre>\n
SELECT Count(*) FROM tblArtikel AS t1 WHERE t1.KategorieID = tblArtikel.KategorieID<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\nAnzahl verknüpfter Datensätze<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\nVerknüpfte Daten mit Exists suchen<\/h2>\n
BestellungVorhanden: Exists (SELECT BestellungID FROM tblBestellungen WHERE tblBestellungen.KundeID = tblKunden.KundeID)<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\nExists mit zusätzlichem Kriterium<\/h2>\n
Bestellung: Exists (\r\n SELECT BestellungID FROM tblBestellungen WHERE tblBestellungen.KundeID = tblKunden.KundeID AND Year(Bestelldatum) = 2023)<\/pre>\n
Bestimmte Menge Datensätze einer Gruppe <\/h2>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nBeispiel Duplikatsuche<\/h2>\n
<\/a><\/p>\n