An imprint of Pearson Education

München • Boston • San Francisco • Harlow, EnglandDon Mills, Ontario • Sydney • Mexico CityMadrid • Amsterdam

André Minhorst

Das Access 2003Entwicklerbuch

3 Abfragen

Abfragen sind die Schnittstelle zwischen den in den Tabellen enthaltenen Daten undden für die Bearbeitung und Anzeige zuständigen Formularen und Berichten sowiefür die Weiterverarbeitung per VBA.

Genau wie Tabellen sollten die Benutzer einer professionellen Datenbank auch eineAbfrage nie zu Gesicht bekommen. Sie dient lediglich dazu, die Daten für die eigent-liche Bearbeitung aufzubereiten.

Dabei gibt es verschiedene Möglichkeiten:

�  Einschränken der Felder einer Tabelle: Mit einer Abfrage können Sie die Felder aus-wählen, deren Inhalte als Abfrageergebnis angezeigt werden sollen.

�  Einschränken der Daten einer Tabelle: Genau wie die Felder können Sie auch dieanzuzeigenden Datensätze einer Tabelle per Abfrage einschränken. Dazu verwen-den Sie ein geeignetes Kriterium.

�  Zusammenführen der Daten verschiedener Tabellen: Nach der Normalisierung lie-gen Daten in vielen Fällen in mehreren, miteinander verknüpften Tabellen vor. Ineiner Abfrage können Sie die Felder der verknüpften Tabellen wieder zusammen-führen und – in den meisten Fällen – wie eine einzige Tabelle verwenden.

�  Zusammenführen der Daten gleichartiger Tabellen: Auch wenn man gleichartigeDaten aus verschiedenen Tabellen benötigt – etwa die Namen der Mitarbeiter ausder Mitarbeitertabelle und diejenigen aus der Kunden-Tabelle –, hilft eine Abfrageweiter (siehe Abschnitt 3.3, »UNION-Abfragen«).

�  Spezielle Aufbereitung von Daten: Mit Hilfe von Kreuztabellenabfragen lassen sichDaten wie beispielsweise die Verkaufszahlen von Produkten in bestimmten Zeit-räumen in einer Art Matrix ausgeben.

�  Berechnungen auf Basis der Felder der zugrunde liegenden Tabellen ausführen:Berechnete Werte in Tabellenfeldern sind bekanntlich tabu, da diese zu redundan-ten Daten führen. Daher gehören Berechnungen in Abfragen.

128 3 Abfragen

Abfragen werden Sie überall antreffen: Als Datenherkunft von Formularen undBerichten, als Datensatzherkunft von Kombinations- und Listenfeldern, als Daten-quelle von Recordset-Objekten in VBA-Code und vielleicht auch als Bestandteil einerweiteren Abfrage.

Weil Abfragen so wichtig sind, stellt Access eine mächtige Entwurfsansicht dafürbereit, die nur wenig Wünsche offen lässt. So lassen sich dort keine UNION-Abfragenoder PassThrough-Abfragen eingeben; dafür ist aber eine zusätzliche SQL-Ansichtvorhanden, in die man nicht nur diese Abfragen eingeben, sondern mit der man auchden SQL-Text der anderen Abfragen ausgeben kann.

3.1 Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft

Formulare und Berichte beziehen ihre Daten aus der unter der Eigenschaft Datenher-kunft angegebenen Tabelle oder Abfrage, bei Kombinations- und Listenfeldern heißtdie entsprechende Eigenschaft Datensatzherkunft. Zum Füllen dieser beiden Eigen-schaften gibt es verschiedene Techniken, die nachfolgend erläutert werden.

Die einfachste ist das Setzen der entsprechenden Eigenschaft auf eine bestehendeTabelle oder Abfrage. Der Einsatz einer Tabelle ist dabei nur sinnvoll, wenn alle Felderund alle Datensätze der Tabelle benötigt werden.

Wenn nicht alle Felder der Tabelle Verwendung finden oder nicht alle Datensätze ange-zeigt werden sollen, verwenden Sie eine Abfrage. Das kommt auch der PerformanceIhrer Anwendung zu Gute.

Wie Sie vielleicht zu Beginn des Buches gelesen haben, setzt dies grundlegendeKenntnisse im Umgang mit Access voraus. Daher finden Sie im Folgenden auchkeine detaillierte Beschreibung für die Anwendung der Abfrage-Entwurfsansicht,sondern Informationen zu oft benötigten Vorgehensweisen im Zusammenhang mitAbfragen. Dabei findet gelegentlich ein Vorgriff auf die in Kapitel 8, »Access-SQL«,enthaltene umfassende Beschreibung der Abfragesprache SQL statt.

Die Beispiele zu diesem Kapitel finden Sie auf der Buch-CD in der DatenbankKap_03\Abfragen.mdb.

Die Herkunft der Daten heißt in Formularen und Berichten Datenherkunft und inSteuerelementen wie dem Kombinationsfeld oder Listenfeld Datensatzherkunft.Wenn es im Folgenden nicht explizit um die Datensatzherkunft solcher Steuerele-mente geht, wird verallgemeinernd der Begriff Datenherkunft verwendet.

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 129

Tabelle als Datenherkunft

Die einfachste Art der Datenherkunft ist eine Tabelle. In vielen Fällen haben Tabellenaber mehr Felder oder enthalten mehr Datensätze als tatsächlich angezeigt werden sol-len. Lookup-Tabellen, die nur aus einem Primärschlüsselfeld und einem weiteren Feldbestehen, können aber durchaus ohne Verwendung einer Abfrage eingesetzt werden –etwa als Datensatzherkunft von Kombinationsfeldern. Erst wenn die enthaltenenDaten auch noch sortiert werden sollen, ist eine Abfrage erforderlich.

Beispiel für diese und die folgenden Möglichkeiten zur Bestückung von Formularenund Steuerelementen mit Datenherkünften ist ein Formular, das die Abwesenheit vonMitarbeitern nach Jahren filtert (siehe Abbildung 3.1).

Ein gutes Beispiel für die Verwendung einer Tabelle als Datensatzherkunft ist dasKombinationsfeld cboJahr. Voraussetzung für den Einsatz einer Tabelle ist, dass alle Fel-der benötigt werden und dass die Daten in der richtigen Reihenfolge vorliegen. Das isthier der Fall: Das Feld JahrID dient als nicht sichtbares, gebundenes Feld und derInhalt des Feldes Jahr wird im Kombinationsfeld angezeigt (siehe Abbildung 3.2).

SQL-Ausdruck als Datenherkunft

Die zweite Möglichkeit sind reine SQL-Ausdrücke. Diese können statt des Namens derTabelle oder der Abfrage für die Datenherkunft- oder Datensatzherkunft-Eigenschaftangegeben werden. In vielen Fällen geht es einfach schneller, wenn man mal eben einekurze SQL-Anweisung für die entsprechende Eigenschaft einträgt, als wenn manzunächst eine Abfrage erstellt, diese speichert und dann die Abfrage als Wert derjeweiligen Eigenschaft einträgt.

Im Beispielformular ist das Kombinationsfeld cboMitarbeiter mit einem solchen SQL-Ausdruck ausgestattet. Dieser lautet folgendermaßen:

SELECT tblMitarbeiter.MitarbeiterID, [Nachname] & ", " & [Vorname] AS Mitarbeiter FROM tblMitarbeiter;

Abbildung 3.1: Beispiel für das Zuweisen der Datensatzherkunft

130 3 Abfragen

Dieser Ausdruck wurde über die Entwurfsansicht für Abfragen erstellt, aber nicht alsAbfrage gespeichert. In diesem Fall wird der reine SQL-Ausdruck in die EigenschaftDatensatzherkunft des Kombinationsfeldes eingetragen.

Gespeicherte Abfrage als Datenherkunft

Die einfachste, weil ohne VBA- und SQL-Kenntnisse zu bewältigende und daher auchfür Einsteiger geeignete Möglichkeit zur Erstellung einer Abfrage bietet die dafür vor-gesehene Entwurfsansicht für Abfragen.

Damit ist die Einschränkung der Datenherkunft sowohl bezüglich der Felder als auchder Datensätze möglich und auch die Verwendung von Parametern ist relativ einfach.

Die Datensatzherkunft des Kombinationsfeldes cboMitarbeiter lässt sich natürlich eben-so mit einer gespeicherten Abfrage wie mit einem SQL-Ausdruck füllen. Um den SQL-Ausdruck in eine gespeicherte Abfrage zu überführen, klicken Sie einfach auf dieSchaltfläche mit den drei Punkten (…) und speichern die nun in der Entwurfsansichtangezeigte Abfrage ab – beispielsweise unter dem Namen qryFrmAbwesenheitenCbo-Mitarbeiter. Auf diese Weise erkennen Sie später schnell, wofür Sie diese Abfrage be-nötigen.

Abbildung 3.2: Tabelle als Datensatzherkunft

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 131

Datenherkunft per VBA zuweisen

Die Eigenschaften Datenherkunft und Datensatzherkunft stehen auch unter VBA zur Ver-fügung – dort verwendet man die Eigenschaftsnamen RowSource und RecordSource.

Sie können diesen Eigenschaften von Formularen sowie Kombinations- und Listenfel-dern per VBA den Namen einer Tabelle oder Abfrage oder auch einen SQL-Ausdruckzuweisen. Was macht das für einen Sinn? Manchmal weiß man noch nicht genau, wiedie anzuzeigenden Daten gestaltet sind. Das ist meist bei Suchformularen der Fall: EinFormular bietet mehrere Such- und Sortierkriterien an, die der Benutzer mit dengewünschten Werten füllen kann.

Man könnte die Kriterien einfach in Form von Parametern an die der Suche zugrundeliegende Abfrage übergeben, aber je nach Komplexität und Anzahl der enthaltenen-zugrunde liegenden Tabellen gerät die Abfrage recht komplex. Eine Abfrage überdie drei per m:n-Beziehung verknüpften Tabellen tblBestellungen, tblBestelldetails undtblArtikel, deren Suchkriterien sich über die äußeren Tabellen erstrecken, benötigt auchalle Tabellen der Abfrage. Soll eine Suche allerdings alle Artikel liefern, deren Suchkri-terien sich lediglich auf die Tabellen tblBestelldetails und tblArtikel erstrecken, kann mandie Tabelle tblBestellungen und die notwendige Verknüpfung und damit Zeit und Res-sourcen sparen – vorausgesetzt, das Abfrageergebnis gibt keine darin enthaltenen Fel-der zurück.

Hier würde dann eine VBA-Routine zum Einsatz kommen, die einen SQL-Ausdruckmit den benötigten Tabellen zusammensetzt.

Parameter statt Zusammensetzen von SQL-Ausdrücken

Auch in anderen, einfacheren Fällen, in denen eine Abfrage lediglich aus einer einzi-gen Tabelle besteht und nur ein Parameter eingesetzt werden muss, verwenden vieleEntwickler VBA, um einen SQL-String zusammenzusetzen und diesen als Datenher-kunft zu verwenden. Das sieht dann beispielsweise so aus:

Verwenden Sie nun besser einen SQL-Ausdruck oder eine gespeicherte Abfrage alsDatenherkunft? Performancetechnisch betrachtet besteht kein großer Unterschied –in beiden Fällen wird die Abfrage beim ersten Aufruf kompiliert und damit opti-miert (siehe auch Kapitel 12, Abschnitt 12.2.1, »Abfragen und die Jet-Engine«). Bleibenzwei Gründe, die für das Speichern der Abfrage sprechen: Entweder benötigen Siedie Abfrage an mehreren Stellen oder Sie möchten die Abfrage testen beziehungs-weise optimieren, während das Formular in der Formularansicht angezeigt wird. Inallen anderen Fällen scheint die Verwendung eines SQL-Ausdrucks nahe liegender,zumal die Abfragen-Ansicht des Datenbankfensters sonst ziemlich schnell unüber-sichtlich werden dürfte.

132 3 Abfragen

Private Sub ListenfeldAktualisieren() Dim strSQL As String Dim strSQLSelect As String Dim strSQLWhere As String 'Basisabfrage (SELECT-Teil) strSQLSelect = "SELECT AbwesenheitID, StartDatum, EndDatum, " _ & " Abwesenheitsart FROM tblAbwesenheitsarten " _ & "INNER JOIN tblAbwesenheiten " _ & "ON tblAbwesenheitsarten.AbwesenheitsartID = " _ & "tblAbwesenheiten.Abwesenheitart " 'Erstes Kombinationsfeld auswerten If Not Nz(Me!cboMitarbeiter, 0) = 0 Then strSQLWhere = "MitarbeiterID = " & Me!cboMitarbeiter End If 'Zweites Kombinationsfeld auswerten If Not Nz(Me!cboJahr, 0) = 0 Then If Len(strSQLWhere) > 0 Then strSQLWhere = strSQLWhere & " AND " End If strSQLWhere = strSQLWhere & "Year(tblAbwesenheiten.StartDatum) = " _ & Me!cboJahr.Column(1) End If 'SELECT-Teil zum SQL-Ausdruck hinzufügen strSQL = strSelect 'Falls WHERE-Bedingung vorhanden, WHERE-Teil zum SQL-Ausdruck hinzufügen If Len(strSQLWhere) > 0 Then strSQL = strSQL & " WHERE " & strSQLWhere End If Debug.Print strSQL 'Neue Datensatzherkunft zuweisen und Listenfeld aktualisieren Me!lstAbwesenheiten.RowSource = strSQL Me!lstAbwesenheiten.RequeryEnd Sub

Listing 3.1: Datensatzherkunft für ein Listenfeld per zusammengesetztem SQL-Ausdruck ermitteln

Die Prozedur wird von den beiden Prozeduren aufgerufen, die durch die Ereignis-eigenschaft Nach Aktualisierung der beiden Kombinationsfelder cboMitarbeiter undcboJahr ausgelöst werden. Die hier ermittelte SQL-Anweisung ist bei keiner Ausfüh-rung kompiliert.

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 133

Eine Alternative ist die Verwendung einer gespeicherten Abfrage mit Parametern. DieParameter, die normalerweise die Anzeige eines Dialogs zum Eingeben des Parametershervorrufen, füllen Sie ebenfalls per VBA. Beim späteren Aufruf weisen Sie dem Listen-feld ein Recordset zu, das auf der kompilierten Abfrage inklusive Parametern basiert.

Die Abfrage sieht wie in Abbildung 3.3 aus. Die ersten vier Felder der Abfrage werdenangezeigt, die letzten beiden sind lediglich Kriterienfelder. Als Kriterien dienen die perVBA zu füllenden Parameter [cboMitarbeiter] und [cboJahr]. Ersterer wird direkt mitdem Inhalt des Feldes MitarbeiterID verglichen, Letzterer mit dem Ausdruck, derdurch die Anwendung der Jahr-Funktion auf dem Inhalt des Feldes StartDatumerzeugt wird. Dabei handelt es sich um die dem Datum entsprechende Jahreszahl.

Nun fehlt noch die Prozedur, mit der die Parameter per Code gefüllt werden und dasErgebnis der Abfrage dem Listenfeld zugewiesen wird. Diese Prozedur erstellt einQueryDef-Objekt auf Basis der Abfrage qryFrmAbwesenheitLstAbwesenheitParameter. Die-ses Objekt enthält eine Auflistung namens Parameters, mit der Sie die in der Abfragegespeicherten Parameter referenzieren und die gewünschten Werte zuweisen können.Der Parameter [cboMitarbeiter] soll mit dem gebundenen Feld der Datensatzherkunftdes Kombinationsfeldes cboMitarbeiter gefüllt werden, der Parameter [cboJahr] wird mitdem im Kombinationsfeld cboJahr angezeigten Wert bestückt. Beachten Sie, dass derangezeigte Wert nicht mit dem Wert des gebundenen Feldes übereinstimmt, sonderndas Jahr und nicht dessen ID enthält!

Dies funktioniert übrigens nur unter Access XP und Access 2003. Unter Access 2000und älteren Versionen von Access haben Kombinations- und Listenfelder nochkeine Recordset-Eigenschaft.

Abbildung 3.3: Abfrage mit Parametern

134 3 Abfragen

Nach dem Füllen der Parameter wird die Abfrage mit der OpenRecordset-Methode aus-geführt und das Ergebnis in ein Recordset-Objekt geschrieben, das schließlich der ent-sprechenden Eigenschaft des Listenfeldes zugewiesen wird.

Private Sub ListenfeldAktualisierenParameter() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset 'Database- und Querydef-Objekt festlegen Set db = CurrentDb Set qdf = db.QueryDefs("qryFrmAbwesenheitenLstAbwesenheitenParameter") 'Parameter [cboMitarbeiter] mit der im Kombinationsfeld 'cboMitarbeiter ausgewählten MitarbeiterID füllen qdf.Parameters("cboMitarbeiter").Value = Me!cboMitarbeiter 'Parameter [cboJahr] mit dem im Kombinationsfeld angezeigten Jahr füllen qdf.Parameters("cboJahr").Value = Me!cboJahr.Column(1) 'Abfrage ausführen und Ergebnis in Recordset-Objekt ablegen Set rst = qdf.OpenRecordset 'Recordset der gleichnamigen Eigenschaft des Listenfelds zuweisen Set Me!lstAbwesenheiten.Recordset = rst Set rst = Nothing Set qdf = Nothing Set db = NothingEnd Sub

Listing 3.2: Listenfeld mit Parameterabfrage füllen

Der erste Test mit dieser Routine läuft nur zufrieden stellend, wenn in beiden Kombi-nationsfeldern ein Wert ausgewählt ist. Eigentlich soll das Listenfeld beim Öffnen desFormulars alle Abwesenheiten anzeigen, beim Auswählen eines Mitarbeiters ohne Jahralle Abwesenheiten dieses Mitarbeiters für alle Jahre, um beim Auswählen lediglicheines Jahres alle Abwesenheiten dieses Jahres für alle Mitarbeiter zu berücksichtigen.

Das funktioniert deshalb nicht, weil die Abfrage beispielsweise bei fehlender Auswahldes Mitarbeiters den Wert Null als Parameter übergibt. Und da es keine Mitarbeiter mitder MitarbeiterID Null gibt, werden auch keine Abwesenheiten angezeigt. Das Gleichegilt für die Auswahl des Jahres.

Die Prozedur verwendet einige Objekte, Methoden und Eigenschaften der DAO-Bibliothek von Access. Detaillierte Informationen finden Sie in Kapitel 8, »DAO«.

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 135

Sie müssen also dafür sorgen, dass die Parameter im Falle einer fehlenden Auswahlneutralisiert werden. Folgender Trick hilft dabei weiter: Fügen Sie in die Kriterien-spalte der betroffenen Felder die folgenden abgewandelten Ausdrücke ein. Ein Daten-satz wird angezeigt, wenn das Kriterium wahr ist – und das ist entweder beipassendem Parameterwert oder bei der Übergabe des Wertes Null der Fall:

[cboMitarbeiter] Oder [cboMitarbeiter] Ist Null

[cboJahr] Oder [cboJahr] Ist Null

Interessant ist, was Access nach dem Schließen und erneutem Öffnen aus den Kriterienmacht. Abbildung 3.4 zeigt, wie Sie die Kriterien alternativ formulieren können.

Abfragen mit Parameter oder zusammengesetzte SQL-Ausdrücke?

Welche der beiden Varianten Sie verwenden, hängt von der Menge der Parameter ab.Je mehr Parameter vorkommen, desto langsamer wird die Abfrage und umso kompli-zierter wird der Abfrageentwurf. Wenn Sie sich den Abfrageentwurf aus Abbildung

Die Verwendung von Null als Standardwert bei fehlender Eingabe eines Parametersist übrigens nicht zwingend, sondern in diesem Fall durch die Verwendung desStandarddatentyps Variant bedingt. Sie können für einen Parameter in der Abfrage-definition durchaus andere Datentypen angeben; folglich werden dann auch dieentsprechenden Standardwerte bei fehlendem Wert verwendet (etwa 0 bei Zahlen-typen oder eine leere Zeichenkette bei String-Variablen).

Abbildung 3.4: Zwei harmlose Kriterienausdrücke nach der Überarbeitung durch Access

136 3 Abfragen

3.4 ansehen und sich vorstellen, wie eine Abfrage mit vier oder mehr Parametern aus-sehen wird, können Sie sich vermutlich ausmalen, wie viel Spaß eine nachträglicheÄnderung am Abfrageentwurf machen wird. Für Abfragen mit mehreren Parameternempfiehlt sich daher eher die Verwendung eines per VBA zusammengesetzten SQL-Ausdrucks.

3.1.1 Probleme mit Kriterienausdrücken bei SQL-Ausdrücken in VBA

Viele Fehler bei der Verwendung von SQL-Ausdrücken unter VBA passieren imZusammenhang mit den Kriterien. Mal meldet Access das Problem, dass zu wenigParameter übergeben wurden (siehe Abbildung 3.5), ein anderes Mal funktionieren dieVergleiche mit übergebenen Datumsangaben nicht.

Zeichenkette oder Zahlenwert?

Der Fehler aus Abbildung 3.5 resultiert fast immer aus dem Fehlen von Anführungs-zeichen im SQL-Ausdruck beim Verwenden von Zeichenketten als Kriterium. Das fol-gende Listing zeigt einen solchen Fehler:

Public Function MitarbeiterSuchen(strNachname As String) … Set rst = CurrentDB.OpenRecordset("SELECT MitarbeiterID, Vorname",� "Nachname FROM tblMitarbeiter WHERE Nachname = " & strNachname) …End Function

Listing 3.3: Falsche Verwendung einer Zeichenkette als Kriterium

Abbildung 3.5: Fehlermeldung beim Verwenden einer SQL-Anweisung per VBA

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 137

In dieses Listing wurden keine Anführungszeichen für die Zeichenkette integriert. DerSQL-Ausdruck sieht für den Aufruf MitarbeiterSuchen "Müller" wie folgt aus:

SELECT MitarbeiterID, Vorname, Nachname FROM tblMitarbeiter WHERE Nachname = Müller

»Müller« wird hierbei nicht als Zeichenkette, sondern als Parameter ausgelegt. Da für die-sen kein Wert vorliegt, erscheint obige Fehlermeldung. Die Lösung des Problems ist ein-fach: Fassen Sie den Parameter einfach in Anführungszeichen oder Hochkommata ein (ineiner Zeile):

Set rst = db.OpenRecordset("SELECT MitarbeiterID, Vorname, Nachname FROM tblMitarbeiter WHERE Nachname = '" & strNachname & "'")

oder

Set rst = db.OpenRecordset("SELECT MitarbeiterID, Vorname, Nachname FROM tblMitarbeiter WHERE Nachname = """ & strNachname & """")

Probleme mit Datumsangaben

Auch Datumsangaben führen immer wieder zu Problemen. Die folgende Routine sollbeispielsweise Informationen über Abwesenheiten ausgeben, deren Beginn in einembestimmten Zeitraum liegt, der durch die Parameter datStart und datEnde angegebenwerden kann.

Public Function AbwesenheitenZeitraum(datStart As Date, datEnde As Date) … Set rst = db.OpenRecordset("SELECT * FROM tblAbwesenheiten WHERE"� "Startdatum BETWEEN " & datStart & " AND " & datEnde, dbOpenDynaset) …End Function

Listing 3.4: Ermitteln von Abwesenheiten in einem bestimmten Zeitraum

Wenn Sie die Routine mit folgendem Aufruf starten, erscheint die Fehlermeldung ausAbbildung 3.6. Auf den ersten Blick scheinen hier die Anführungszeichen zu fehlen.

AbwesenheitenZeitraum "1.1.2004", "31.1.2004"

Ändern Sie den Aufruf der SQL-Anweisung wie folgt um, gibt es allerdings eineandere Fehlermeldung (siehe Abbildung 3.7):

Set rst = db.OpenRecordset("SELECT * FROM tblAbwesenheiten WHERE Startdatum BETWEEN '" & datStart & "' AND '" & datEnde & "'", dbOpenDynaset)

138 3 Abfragen

Diesmal hat Access Probleme mit dem Datentyp – ein String scheint das Bedürfnisnach einem Wert des Typs DATETIME nicht zu befriedigen.

Mit dem Wissen, dass Access Datumsangaben intern als Zahlenwerte behandelt,erscheint dies schnell logisch. Dabei gibt es zwei Möglichkeiten: Verwenden Sie denDatentyp Double, um Datumsangaben inklusive Uhrzeit zu verwalten, dann entsprichtdie Zahl vor dem Komma der Anzahl Tage seit dem 31.12.1899 und die Zahl nach demKomma der Anzahl Sekunden, die am angegebenen Tag verstrichen sind. Für Datums-angaben ohne Uhrzeit reicht dementsprechend der Datentyp Long aus.

Nun müssen Sie aber nicht alle Datumsangaben explizit in einen Zahlen-Datentypumwandeln. Es reicht, wenn Sie ein standardisiertes Format verwenden. Dieses hat dieForm yyyy-mm-dd. Zusätzlich fassen Sie diesen Ausdruck in der Abfrage in Raute-Zeichen (#) ein.

In obigem Code sieht das Ganze dann wie folgt aus (in einer Zeile):

Set rst = db.OpenRecordset("SELECT * FROM tblAbwesenheiten WHERE Startdatum BETWEEN #" & Format(strStart, "yyyy-mm-dd") & "# AND #" & Format(strEnde, "yyyy-mm-dd") & "#", dbOpenDynaset)

Das Formatieren des Datums und das Einfassen in Rauten lässt sich auch per Funktionerledigen:

Abbildung 3.6: Fehlermeldung bei der Verwendung von Datumsangaben in SQL-Ausdrücken

Abbildung 3.7: Auch die Zeichenkette taugt nicht als Datumskriterium.

Verwendung von Abfragen als Datenherkunft oder Datensatzherkunft 139

Public Function ISODatum(strDatum As String) As String ISODatum = Format(strDatum, "\#yyyy-mm-dd\#")End Function

Listing 3.5: Funktion zum Standardisieren von Datumsangaben

Die Zeile aus obiger Routine sähe dann so aus (in einer Zeile):

Set rst = db.OpenRecordset("SELECT * FROM tblAbwesenheiten WHERE Startdatum BETWEEN " & ISODatum(strStart) & " AND " & ISODatum(strEnde), dbOpenDynaset)

Verweis auf Steuerelemente

Access-SQL erlaubt direkte Verweise auf Formulare und Steuerelemente. Das ist hilf-reich, wenn Sie etwa den Feldinhalt eines Formulars als Abfragekriterium verwendenmöchten. Eine solche Abfrage sieht beispielsweise wie in Abbildung 3.8 aus.

Ein Blick in die entsprechende SQL-Anweisung zeigt, dass der Ausdruck tatsächlichim gleichen Format wie in VBA in die Abfrage integriert wurde:

SELECT MitarbeiterID, Nachname, Vorname, AbteilungIDFROM tblMitarbeiterWHERE AbteilungID=[Forms]![frmMitarbeiter]![cboAbteilungen];

Dies funktioniert sogar, wenn Sie mit anderen Backends wie etwa MySQL arbeiten.Beim Zusammensetzen von SQL-Abfragen via VBA bietet es sich allerdings an, dieentsprechenden Ausdrücke direkt auszulesen und als festen Parameterwert in dieAbfrage zu integrieren.

Abbildung 3.8: Abfrage mit einem Verweis auf ein Formularsteuerelement

140 3 Abfragen

3.2 Aktualisierbarkeit von Abfragen

Je nachdem, wie die in einer Abfrage enthaltenen Tabellen beschaffen sind und wie Siediese zusammensetzen, sind Abfragen nicht aktualisierbar, das heißt, dass Sie über dieDatenblattansicht und damit auch über die Anzeige in Formularen keine Datensätzebearbeiten oder hinzufügen können.

Es ist sicher jedem Access-Entwickler schon einmal passiert, dass er per VBA einenDatensatz einer Abfrage ändern oder hinzufügen wollte und eine entsprechende Feh-lermeldung erschien, für die es scheinbar keine Erklärung gab.

Wie erkennen Sie, ob das Abfrageergebnis aktualisierbar ist?

Wenn Sie eine Abfrage in der Datenblattansicht öffnen, erkennen Sie recht schnell, obdiese aktualisierbar ist – die Schaltfläche zum Springen auf einen neuen Datensatz istausgeblendet und unter dem letzten Datensatz befindet sich keine leere Zeile zumAnlegen eines neuen Datensatzes (siehe Abbildung 3.9).

Die Verwendung einer fest in eine Abfrage eingebauten Referenz bringt den Nach-teil mit sich, dass Sie diese Abfrage nicht einsetzen können, wenn Sie einen anderenals diesen Parameter verwenden möchten. Die Variante, eine Abfrage ohne Formu-larreferenzen zu erstellen und derartige Kriterien erst per VBA oder direkt in einerDatenherkunft- oder Datensatzherkunft-Eigenschaft zuzuweisen, ist flexibler.

Abbildung 3.9: Eine nicht aktualisierbare Abfrage erkennt man an der deaktivierten Schaltfläche »Neuer Daten-satz« und an der fehlenden Zeile mit einem leeren neuen Datensatz.

Aktualisierbarkeit von Abfragen 141

Nicht aktualisierbare Abfragen

Nachfolgend finden Sie Beispiele für Abfragen, die niemals aktualisierbar sind:

�  Abfragen mit zwei nicht verknüpften Tabellen. Beispiele sind alle Abfragen, die alleKombinationen aus den Datensätzen zweier Tabellen anzeigen sollen (siehe Abbil-dung 3.10).

�  Abfragen mit drei oder mehr Tabellen, deren innere Tabelle die 1-Seite für die bei-den äußeren Tabellen stellt. Beispiel: Sie möchten zu einem Mitarbeiter gleichzeitigdie Abwesenheiten und den Urlaubsanspruch ausgeben (siehe Abbildung 3.11).

�  Abfragen mit Gruppierungen und Aggregatfunktionen

�  Abfragen, bei denen die Eigenschaft Keine Duplikate auf Ja eingestellt ist

Sollte sich einmal eine Abfrage als nicht aktualisierbar erweisen, von der Sie es eigent-lich erwarten, prüfen Sie diese zunächst auf die oben genannten Eigenschaften.

Abbildung 3.10: Diese Abfrage liefert kein aktualisierbares Ergebnis.

Abbildung 3.11: Auch diese Abfrage ist nicht aktualisierbar.

142 3 Abfragen

3.3 UNION-Abfragen

UNION-Abfragen bieten die Möglichkeit, die Daten mehrerer gleichartig aufgebauterTabellen mit einer Abfrage zu vereinen. Dazu erstellen Sie zwei oder mehr gleichartigaufgebaute Abfragen und verketten diese mit dem UNION-Schlüsselwort. Entschei-dend ist, dass alle beteiligten Abfragen die gleiche Anzahl Felder haben und dass diejeweils an der gleichen Stelle befindlichen Felder den gleichen Datentyp besitzen.Haben die Daten verschiedene Datentypen, konvertiert Jet in den meisten Fällen beidein einen Variant-Wert und gibt anschließend einen String-Wert aus. Probleme gibt eshier, wenn GUIDs und andere Datentypen gemischt werden.

Weitere Grundlagen zu UNION-Abfragen finden Sie weiter hinten in Kapitel 7,Abschnitt 7.2.9, »Zusammenfassen von Abfrageergebnissen mit UNION«.

3.3.1 UNION-Abfragen zur Optimierung von Kombinationsfeldern

Sie können eine UNION-Abfrage beispielsweise dazu verwenden, Kombinationsfelderzu optimieren. Wenn Kombinationsfelder keinen Eintrag enthalten, zeigen diese einleeres Feld an. Praktischer und eine eindeutige Aufforderung an den Benutzer wäre es,wenn Kombinationsfelder ohne Wert etwa die Zeichenkette <Auswählen> anzeigenwürden (siehe Abbildung 3.12).

Als Datensatzherkunft des Kombinationsfeldes dient dabei die folgende SQL-Abfrage,die Sie direkt in die SQL-Ansicht der Abfrage eingeben müssen:

SELECT 0 AS AbteilungID, '<Auswählen>' AS Abteilung FROM tblAbteilungen UNION SELECT AbteilungID, Abteilung FROM tblAbteilungen;

Dies ist ein gutes Beispiel für die Zweckentfremdung einer UNION-Abfrage, denn deraus dem ersten Teil der Abfrage stammende Wert ist eigentlich gar nicht in der Tabellevorhanden. Deshalb gibt man dort nicht nur die Feldnamen, sondern die konkreten

Abbildung 3.12: Vorgefülltes Kombinationsfeld

UNION-Abfragen 143

Werte an. Der Übersicht halber versieht man die einzelnen Feldwerte noch mit demAS-Schlüsselwort und fügt den eigentlichen Feldnamen hinzu. Letzteres ist aber nichtunbedingt notwendig.

Wichtig ist bei diesem Beispiel, dass der im ersten Abfrageteil verwendete Wert für diegebundene Spalte der zukünftigen Datensatzherkunft kleiner ist als alle Werte, die ausder oder den anderen Tabellen noch hinzukommen. Anderenfalls lässt sich nur schwereine sinnvolle Sortierung festlegen – es sei denn, man fügt noch ein individuelles Sor-tierfeld hinzu.

Wenn Sie hingegen nach dem angezeigten Feld sortieren möchten, müssen Sie erstensdas Sortierkriterium an den letzten Teil der UNION-Abfrage anhängen und zweitensdafür sorgen, dass der ohne Auswahl angezeigte Datensatz der erste unter der angege-benen Sortierung ist:

SELECT 0 AS AbteilungID, '<Auswählen>' AS Abteilung FROM tblAbteilungenUNIONSELECT AbteilungID, Abteilung FROM tblAbteilungen ORDER BY Abteilung;

Da das Kleiner-Zeichen (<) im ASCII-Code vor den Buchstaben angeordnet ist, sindhier keine weiteren Maßnahmen erforderlich. Wollten Sie hingegen nur den EintragAuswählen ohne spitze Klammern verwenden, müssten Sie ein zusätzliches Sortierfeldanhängen, bei dem Sie für den ersten Teil der Abfrage einen Wert angeben, der aufjeden Fall vor allen anderen liegt. Außerdem legen Sie dieses Sortierfeld als ORDERBY-Kriterium fest:

SELECT 0 AS AbteilungID, 'Auswählen' AS Abteilung, 'AAAA' AS Sortierung FROM tblAbteilungenUNIONSELECT AbteilungID, Abteilung, Abteilung As Sortierung FROM tblAbteilungen ORDER BY Sortierung;

3.3.2 Eindeutige Schlüssel mit UNION-Abfragen

Einen gravierenden Nachteil haben UNION-Abfragen: Wenn die zumeist aus mehre-ren Tabellen zusammengeführten Daten eindeutig identifiziert werden sollen, umetwa einen ausgewählten Datensatz zu löschen, ist »Hängen im Schacht«. Das Problem

Wenn Sie einen Dummy-Datensatz wie im ersten Teil der obigen UNION-Abfragebenötigen, brauchen Sie in allein stehenden Tabellen nur den ersten Teil derAbfrage zu verwenden: SELECT 0 AS AbteilungID, '<Auswählen>' AS Abteilung. DieAngabe einer Ursprungstabelle mit FROM tblAbteilungen ist nur in Zusammenhangmit UNION-Abfragen erforderlich.

144 3 Abfragen

ist, dass auch die Primärschlüssel aus mehreren Tabellen zusammengeführt werdenund diese daher nicht zwangsläufig eindeutig sind, denn es können durchaus mehrereTabellen etwa den Schlüsselwert 1 besitzen.

Die einfachste Lösung ist die Verwendung von GUIDs als Primärschlüssel. DieseWerte sind nicht nur über mehrere Tabellen, sondern sogar weltweit eindeutig – sofernsie mit der entsprechenden Systemfunktion erzeugt wurden (weitere Informationenfinden Sie in Kapitel 2, Abschnitt 2.5, »Autowerte als Long oder GUID?«). Selbst dasLöschen eines Datensatzes, der aus einer UNION-Abfrage ausgewählt wurde, ist beiVorhandensein einer GUID einfach: Löschen Sie einfach aus allen beteiligten Tabellenden Datensatz mit der betroffenen GUID – irgendwo werden Sie schon den richtigentreffen und falsche Datensätze löschen Sie damit auch nicht.

Ohne GUID ist die eindeutige Identifikation von Daten aus UNION-Abfragen nochschwieriger. Als Beispiel dient die folgende Abfrage:

SELECT KundeID, Vorname, Nachname FROM tblKundenUNIONSELECT MitarbeiterID, Vorname, Nachname FROM tblMitarbeiter;

Abbildung 3.13 zeigt auf, was passieren kann: Der Wert 3 kommt in je einem Datensatzder beteiligten Tabellen vor.

Um dies zu verhindern, verwenden Sie einen kombinierten Wert aus KundeID bezie-hungsweise MitarbeiterID und dem jeweiligen Tabellennamen. Die UNION-Abfragesieht nun so aus:

SELECT 'Kunde' & KundeID AS PersonID, Vorname, Nachname FROM tblKundenUNIONSELECT 'Mitarbeiter' & MitarbeiterID AS PersonID, Vorname, Nachname FROM tblMitarbeiter;

Abbildung 3.13: UNION-Abfragen garantieren keinen eindeutigen Index.

Suchen in m:n-Beziehungen 145

Das Ergebnis überzeugt ebenfalls, die Datensätze verfügen nun über ein eindeutigesFeld (siehe Abbildung 3.14).

Gegenüber der Variante mit dem GUID-Wert als Primärschlüssel fällt diese Variantejedoch deutlich ab. Allein das Handling ist wesentlich aufwändiger: Wenn Sie etwaeinen mit dieser UNION-Abfrage ausgewählten Datensatz löschen möchten, müsstenSie zunächst über den zusammengesetzten Schlüssel ermitteln, aus welcher Tabelle derDatensatz stammt, und diesen dann dort löschen.

Daraus resultiert letzten Endes die Empfehlung, Daten, die gleich aufgebaut sind, auchin einer einzigen Tabelle zu speichern. Wenn die Daten gelegentlich unterschiedlicheEigenschaften aufweisen und daher unterschiedliche Felder benötigen, lässt sich diesüber 1:1-Beziehungen vermutlich leichter realisieren.

3.3.3 INSERT INTO mit UNION-Abfragen

Abfragen sind nun einmal nichts Beständiges und deshalb sind auch die mit einerUNION-Abfrage ermittelten Daten grundsätzlich erst einmal nicht zur Weitergabegeeignet. Jeglicher Versuch, die Daten per INSERT INTO-Abfrage einfach in eine tem-poräre Tabelle zu schreiben, schlägt fehl: SQL mag keine INSERT-Queries mit UNION-Abfragen als Datenherkunft (siehe Abbildung 3.15).

Die Lösung ist, wie in Abbildung 3.16 die UNION-Abfrage zu speichern und die An-fügeabfrage auf diese Abfrage zugreifen zu lassen.

3.4 Suchen in m:n-Beziehungen

Wenn Sie die Anleitungen zur Normalisierung in Kapitel 2 sorgfältig berücksichtigen,dann haben Sie beispielsweise eine Menge triviale Eigenschaften, zu denen jederzeitneue hinzukommen können, in eine m:n-Beziehung ausgelagert. Das dortige Beispielbezog sich auf Fahrzeuge und ihre Ausstattungsmerkmale.

Abbildung 3.14: Eine UNION-Abfrage mit eindeutigem Feld

146 3 Abfragen

Nach diesem Schritt haben Sie zwar ein sauberes Datenmodell, aber dummerweiselässt sich nicht mehr so einfach per Abfrage ermitteln, welche Autos beispielsweise miteiner Klimaanlage und Servolenkung ausgestattet sind, aber etwa kein Navigations-system besitzen. Hätte man in der Version mit allen Eigenschaften in einer einzigenTabelle nur die entsprechenden Felder mit Kriterien wie True oder False ausstattenmüssen, ist nun ein wenig mehr Aufwand notwendig.

Suchen von Rezepten mit bestimmten Zutaten

Als Beispiel dient nun aber eine neue Variante: Die Zuordnung von Zutaten zu einzel-nen Rezepten. Stellen Sie sich vor, was Sie alles anstellen könnten, wenn Sie nur nochin Kühl- und Vorratsschrank schauen, die dort vorhandenen Zutaten in ein Formulareingeben (ok, nicht alle, aber zumindest die, auf die Sie gerade Appetit haben) unddann umgehend alle Rezepte erhalten, die sich mit diesen Zutaten zaubern lassen.

Abbildung 3.15: UNION-Abfragen in INSERT INTO-Statements funktionieren nicht.

Abbildung 3.16: INSERT INTO mit UNION-Abfrage

Suchen in m:n-Beziehungen 147

Betrachtet man das Datenmodell aus Abbildung 3.17, fällt einem nur leider geradekein Rezept ein, auf dessen Basis man die passende Abfrage basteln könnte.

Mit einer Zutat jedenfalls funktioniert der Ansatz aus Abbildung 3.18. Das Ergebnisliefert alle Rezepte, welche die Zutat »Pfeffer« enthalten – das ist einfach.

Jetzt fügen Sie eine Zutat hinzu: Mal sehen, welche Rezepte übrig bleiben, wenn auchnoch Salz enthalten sein soll. Nur wohin mit dem neuen Kriterium? Wenn man es ganznaiv als »Oder«-Kriterium in die Spalte Zutat einfügt, enthält das Abfrageergebnismehr Datensätze als vorher – das war abzusehen und ist falsch. Schließlich sollte eineweitere Zutat die Ergebnismenge einschränken oder maximal gleich viele Ergebnissezurückliefern. Also verwenden Sie als Kriterium nun ="Pfeffer" Und "Salz". Das Ergeb-

Abbildung 3.17: Aufbau der Beziehung zwischen Rezepten und Zutaten

Abbildung 3.18: Abfrage zur Ermittlung aller Rezepte mit einer bestimmten Zutat

148 3 Abfragen

nis? Es enthält gar keine Datensätze mehr. Gibt es keine Rezepte, die Pfeffer und Salzenthalten? Doch, natürlich, aber es gibt bei keinem Rezept ein Zutatenfeld, das zweiZutaten gleichzeitig enthält.

Schauen Sie sich noch einmal genauer das Ergebnis der Variante mit »Oder« an (sieheAbbildung 3.19). Logischerweise sind dort alle Rezepte, die beide angegebenen Zuta-ten enthalten, zweimal aufgeführt; alle Rezepte, die nur eine der beiden Zutaten ent-halten, findet man nur einmal.

Damit lässt sich doch etwas anfangen. Gleiche Einträge in einer Abfrage lassen sichgruppieren und zu einer Gruppierung lässt sich auch die Anzahl der enthaltenenDatensätze ausgeben. Passen Sie also die Abfrage wie in Abbildung 3.20 an:

1. Blenden Sie mit dem Menüeintrag Ansicht/Funktionen bei aktivierter Entwurfsan-sicht die Funktionszeile ein.

2. Stellen Sie für das Feld RezeptID die Funktion Anzahl ein.

3. Das Feld Zutat dient nur als Bedingung und darf nicht angezeigt werden. Dazudeaktivieren Sie die Zeile Anzeigen und stellen als Funktion den Wert Bedingung ein.

Das Ergebnis aus Abbildung 3.21 überzeugt: Die Rezepte, die beide Zutaten enthalten,werden mit der Anzahl 2 ausgegeben. Damit erhalten Sie nicht nur die Rezepte mitallen gewünschten Zutaten, sondern auch andere – und zwar nach der Qualität desTreffers sortiert.

Abbildung 3.19: Ergebnis der »Oder«-Variante der Zutaten-Suche

Handhabung von 1:1-Beziehungen 149

3.5 Handhabung von 1:1-Beziehungen

In Kapitel 2, Abschnitt 2.4.7, »1:1-Beziehungen« haben Sie erfahren, wie Sie Daten vonTabellen in zwei oder mehr per 1:1-Beziehung verknüpfte Tabellen aufteilen. Offen istnoch, wie Sie mit solchen Tabellen arbeiten, wenn es um die Eingabe, das Bearbeitenund Löschen von Daten geht.

Mit einer geeigneten Abfrage lassen sich die Daten in per 1:1-Beziehung verknüpftenTabellen genau so bearbeiten wie die Daten einer einzelnen Tabelle.

Abbildung 3.20: Diese Abfrage sortiert die Rezepte nach der Anzahl der enthaltenen Wunschzutaten.

Abbildung 3.21: Rezepte und die Anzahl der enthaltenen Wunschzutaten

150 3 Abfragen

Als Beispiel dient die in Kapitel 2 vorgestellte Beziehung, bei der die erste Tabelle der1:1-Beziehung Personendaten und die zweite Tabelle Erweiterungsdaten zu den in derersten Tabelle enthaltenen Daten enthält (siehe Abbildung 3.22).

Für die Bearbeitung ist lediglich interessant, ob Sie Personen mit der Ausprägung»Angestellter« oder mit der Ausprägung »freier Mitarbeiter« behandeln möchten.Wenn Sie die Daten der Angestellten bearbeiten wollen, verwenden Sie die in Abbil-dung 3.23 abgebildete Abfrage.

Die Abfrage enthält alle Felder der beiden Tabellen mit Ausnahme des Primärschlüs-selfeldes der Tabelle tblAngestellte. Auch das Fremdschlüsselfeld dieser Tabelle mussnicht angezeigt werden, aber zu Beispielzwecken sollten Sie es einbauen. In der Abbil-dung enthält die Abfrage aus Platzgründen nur die wichtigsten Felder der zugrundeliegenden Tabellen.

Wenn Sie die Abfrage in der Datenblattansicht anzeigen, können Sie dort Daten wie ineiner ganz normalen Tabelle einfügen. Dabei kann allerdings folgendes Problem ent-stehen: Geben Sie einmal nur Daten in Felder der Tabelle tblPersonen ein (also etwa Vor-name und Nachname), schließen Sie die Abfrage und öffnen Sie diese erneut. DerDatensatz scheint verschwunden, zumindest ist er im Sinne der 1:1-Beziehung nichtvorhanden. Der Grund ist ganz einfach: Mit der getätigten Eingabe wird in der TabelletblAngestellte kein Datensatz angelegt und die Abfrage zeigt nur jene Kombinationenvon Datensätzen der Tabellen tblPersonen und tblAngestellte an, bei denen der Inhaltdes Feldes PersonID in beiden Tabellen gleich ist. Den Beweis liefert ein kurzer Blick indie Tabelle tblPersonen: Dort findet sich nämlich der frisch angelegte Datensatz, alleindas Pendant in der Tabelle tblAngestellte fehlt.

Abbildung 3.22: 1:1-Beziehung zwischen Personen auf der einen und Angestellten und freien Mitarbeitern auf der anderen Seite

Handhabung von 1:1-Beziehungen 151

Geben Sie nun einen kompletten Datensatz ein und füllen Sie die Felder von links nachrechts, werden Sie feststellen, dass das Feld PersonID der Tabelle tblAngestellte automa-tisch mit dem gleichen Wert wie in der Tabelle tblPersonen gefüllt wird, sobald Sie dieEingabe in eines der Felder der Tabelle tblAngestellte abgeschlossen haben (siehe Abbil-dung 3.24).

Wenn Sie zuerst ein Feld der Tabelle tblAngestellte füllen, erhält das Feld PersonID die-ser Tabelle zunächst den Wert 0. Erst wenn Sie mindestens ein Feld der Tabelle tblPerso-nen gefüllt haben, erhalten beide PersonID-Felder den über die Tabelle tblPersonengenerierten Autowert.

Bleibt die letzte Möglichkeit: Sie füllen lediglich die Felder der Tabelle tblAngestellteund versuchen, den Datensatz zu speichern. Dies lässt Access nicht zu: Es weist miteiner entsprechenden Meldung darauf hin, dass zunächst ein passender Datensatz inder Tabelle tblPersonen angelegt werden muss (siehe Abbildung 3.25).

Abbildung 3.23: Zusammenführen der Tabellen einer 1:1-Beziehung per Abfrage

Abbildung 3.24: Das Eingeben von Daten

152 3 Abfragen

Wie sorgen Sie nun dafür, dass vor dem Speichern alle benötigten Daten eingegebenwerden, ohne dass Access seine eigenen Meldungen anzeigt? In der Abfrage funktio-niert dies gar nicht: Es besteht keine Möglichkeit, die Meldung aus Abbildung 3.25abzufangen – weder durch Setzen der Eingabe erforderlich-Eigenschaft noch durch dieVerwendung der Gültigkeitsregel. Das ist aber auch nicht schlimm, denn wie bereitserwähnt, sollen Tabellen und Abfragen ohnehin nicht zur direkten Dateneingabe ver-wendet werden. Und in den dafür vorgesehenen Formularen gibt es sowieso ganzandere Mittel. Mehr dazu erfahren Sie in Kapitel 4, Abschnitt 4.4.5, »1:1-Beziehungen«.

Behandlung von 1:1-Beziehungen mit ergänzenden Feldern

Die oben vorgestellte 1:1-Beziehung dient dem »Vererben« der in der Tabelle tblPerso-nen gespeicherten Eigenschaften an speziellere Personentypen wie Angestellte oderfreie Mitarbeiter.

Es gibt auch 1:1-Beziehungen mit wesentlich weniger anspruchsvollem Hintergrund.Die 1:1-Beziehung aus Abbildung 3.26 dient beispielsweise nur dazu, einer Tabelle einDrucken-Feld hinzuzufügen, ohne dass die Tabelle tatsächlich erweitert wird.

Die passende Verknüpfung sieht wie in Abbildung 3.26 aus. Wenn Sie die beidenTabellen so wie in Abbildung 3.27 in den Abfrageentwurf übernehmen, werden Sienach dem Wechsel in die Datenblattansicht ein leeres Abfrageergebnis vorfinden(zumindest, wenn Sie noch keine Datensätze in der Tabelle tblMitarbeiterDrucken ange-legt haben). Der Grund ist einfach: Die Abfrage zeigt nur Daten, für die in beidenTabellen ein Datensatz vorliegt. Da die Tabelle tblMitarbeiterDrucken im Urzustandzunächst keine Daten enthält, ist das Abfrageergebnis noch leer. Leider lässt sich überdiese Abfrage auch kein Datensatz zur Tabelle tblMitarbeiterDrucken hinzufügen.

Um dies zu ermöglichen, ändern Sie in der Abfrage den Beziehungstyp wie in Abbil-dung 3.28 und erzeugen somit einen LEFT JOIN. Damit werden nun definitiv alleDatensätze der Tabelle tblMitarbeiter angezeigt, auch wenn es nicht für alle einen ver-knüpften Datensatz in der Tabelle tblMitarbeiterDrucken gibt.

Abbildung 3.25: Diese Meldung erscheint, wenn Sie unvollständige Daten in eine Abfrage mit zwei per 1:1-Bezie-hung verknüpften Daten eingeben.

Handhabung von 1:1-Beziehungen 153

Nun sieht die Datenblattansicht der Abfrage schon wesentlich erfreulicher aus: Zujedem Mitarbeiter wird das Drucken-Feld angezeigt, obwohl es eigentlich gar keineverknüpften Datensätze gibt. In der Tabelle tblMitarbeiterDrucken wird dann auch tat-sächlich erst ein Datensatz angelegt, wenn Sie in der Abfrage einen Haken in das ent-sprechende Feld setzen (siehe Abbildung 3.29).

Abbildung 3.26: 1:1-Beziehung zum Anfügen eines einzelnen Feldes

Abbildung 3.27: Abfrage mit Drucken-Feld in der Entwurfsansicht …

154 3 Abfragen

Wenn Sie das Feld in der Abfrage wieder deaktivieren, bleibt der Datensatz in derTabelle tblMitarbeiterDrucken allerdings vorhanden. Um Platz in der Datenbank zuschaffen, empfiehlt es sich daher, regelmäßig alle Datensätze aus der Tabelle tblMitar-beiterDrucken zu entfernen, deren Feld Drucken den Wert False enthält.

3.6 Extremwerte per Abfrage ermitteln

Unterabfragen sind oft ein bewährtes Mittel, um Kriterien für Abfragen zu ermitteln –sei es zur Ermittlung nur eines oder auch mehrerer Kriterien (IN-Operator). Besondersinteressant ist die Möglichkeit, in einer Unterabfrage auf den Inhalt des in derHauptabfrage enthaltenen Datensatzes zuzugreifen. So lassen sich beispielsweiseInformationen ermitteln, die auf eine Gruppe von Datensätzen bezogen sind.

Abbildung 3.28: Ändern der Verknüpfungseigenschaften

Abbildung 3.29: Das Drucken-Feld ist für alle Mitarbeiter verfügbar.

Extremwerte per Abfrage ermitteln 155

Extremwert einer Gruppierung ermitteln

Ein Beispiel sind Extremwerte von Gruppierungen. Wenn Sie in den Tabellen Artikelund Kategorien der Nordwind-Datenbank den teuersten Artikel einer Kategorie ermit-teln möchten, ist das kein Problem. Die Abfrage aus Abbildung 3.30 hilft dann weiter –hier wird beispielsweise der höchste Preis eines Artikels der Kategorie »Getränke«ermittelt.

Leider gibt die Abfrage nur den Preis, aber keine weiteren Informationen zum Namendes Artikels aus, da das Ergebnis über eine Gruppierung herbeigeführt wurde.

Sie können noch nicht einmal das Abfrageergebnis als Unterabfrage einer Abfrage ver-wenden, die dann alle gewünschten Daten enthält, da diese Unterabfrage kein eindeu-tiges Feld enthielte.

Extremwert per TOP und ORDER BY

Also tricksen Sie ein wenig: Verwenden Sie statt der Gruppierung eine nach dem Preissortierte Abfrage und die TOP-Option der SELECT-Klausel, um nur den ersten unddamit teuersten Datensatz zurückzugeben (siehe Abbildung 3.31).

Abbildung 3.30: Ermitteln des maximalen Preises von Artikeln der Kategorie Getränke

156 3 Abfragen

Extremwerte per Unterabfrage

Diese Abfrage liefert das richtige Ergebnis einschließlich Artikelinformationen zurück.Im nächsten Schritt sollen nun in einer Abfrage alle teuersten Artikel ihrer Kategorieausgegeben werden – und hier kommen die korrelierten Haupt- und Unterabfragenzum Zuge. Zum besseren Verständnis findet noch ein Zwischenschritt statt: DieAbfrage aus Abbildung 3.32 enthält eine Unterabfrage zur Ermittlung des teuerstenArtikels, dessen Detaildaten in der Hauptabfrage angezeigt werden.

Abbildung 3.31: Variante 2 zur Ermittlung des teuersten Artikels der Kategorie »Getränke«

Abbildung 3.32: Variante zum Ermitteln des teuersten Artikels über eine Unterabfrage

Datensätze mehrfach anzeigen 157

Extremwerte von Gruppierungen

Von dieser Abfrage aus gelangen Sie mit wenigen Änderungen zu einer Variante, diedie teuersten Artikel einer jeden Kategorie anzeigt. Dazu müssen Sie eine zusätzlicheBeziehung zwischen der Haupt- und der Unterabfrage einbauen, die dazu führt, dassdie Unterabfrage jeweils den teuersten Artikel der Kategorie des aktuellen Datensatzesdes Hauptformulars zurückgibt. Hört sich kompliziert an, ist es aber nicht: Dazu müs-sen Sie lediglich die Tabellen in Haupt- und Unterabfrage mit einem Alias-Namen ver-sehen und die Inhalte des Feldes Kategorie-Nr der beiden Abfragen gleichsetzen.

Der SQL-Ausdruck dieser Abfrage sieht wie folgt aus:

SELECT t1.[Artikel-Nr], t1.Artikelname, t1.EinzelpreisFROM Artikel AS t1WHERE t1.[Artikel-Nr]=( SELECT TOP 1 t2.[Artikel-Nr] FROM Artikel AS t2 WHERE t1.[Kategorie-Nr] = t2.[Kategorie-Nr] ORDER BY t2.Einzelpreis DESC);

In der Entwurfsansicht sieht die Abfrage wie in Abbildung 3.33 aus. Das Ergebnis ist inAbbildung 3.34 abgebildet.

3.7 Datensätze mehrfach anzeigen

Für manche Aufgaben kann es erforderlich sein, Datensätze mehr als einmal auszuge-ben – das Paradebeispiel ist wohl die Ausgabe eines Berichtes mit Adressetiketten. Esgibt einige Lösungen, die dieses Problem per VBA-Code angehen. Es funktioniert aberauch mit einer ausgefeilten Abfrage. Voraussetzung ist, dass sich ein Feld mit der aus-zugebenden Anzahl in der Tabelle befindet.

Abbildung 3.33: Korrelierte Haupt- und Unterabfrage zur Ermittlung des teuersten Artikels je Kategorie

158 3 Abfragen

Außerdem benötigen Sie eine Tabelle, die lediglich ein einziges Feld namens Anzahlenthält, das mit den Zahlen von 0 bis 10 (oder der maximalen Anzahl vorgesehenerExemplare) gefüllt ist (siehe Abbildung 3.35).

Abbildung 3.36 zeigt, wie Sie die Tabellen zusammenführen müssen. Beim Hinzufü-gen der beiden Tabellen fügt Access gegebenenfalls eine Verknüpfung zwischen dengleichnamigen Feldern (Anzahl) hinzu, diese entfernen Sie umgehend wieder. Dadurchzeigt Access alle Kombinationen der Datensätze aus der ersten und der Datensätze ausder zweiten Tabelle an. Das sind natürlich ein paar zu viel: Daher schränken Sie dieErgebnismenge noch ein wenig ein. Ziehen Sie neben den auszugebenden Daten dasFeld Anzahl der Tabelle tblAnzahl in das Entwurfsraster – nicht jedoch das der Mitarbei-tertabelle (!) – und fügen Sie diesem Feld das Kriterium aus der Abbildung hinzu.

Abbildung 3.34: Ergebnis der korrelierten Unterabfrage

Abbildung 3.35: Die Kombination dieser beiden Tabellen liefert die im Feld Anzahl der Mitarbeitertabelle angege-benen Exemplare eines jeden Datensatzes.

Datensätze mehrfach anzeigen 159

Somit enthält das Abfrageergebnis alle Kombinationen der Datensätze, für die dasFeld Anzahl der Tabelle tblAnzahl kleiner als die in der Mitarbeitertabelle angegebeneAnzahl ist.

Abbildung 3.37 zeigt das nach dem Feld MitarbeiterID sortierte Abfrageergebnis.

Abbildung 3.36: Abfrage zur Anzeige einer bestimmten Anzahl je Datensatz

Abbildung 3.37: Das Abfrageergebnis enthält jeden Datensatz in der gewünschten Anzahl. Die letzte Spalte zeigt den jeweiligen Wert des Feldes Anzahl der Tabelle tblAnzahl.

160 3 Abfragen

3.8 Nummerierung von Datensätzen

Felder wie der Primärindex taugen in den seltensten Fällen zum Bereitstellen einerlückenlosen Nummerierung von Datensätzen. Erstens sorgt jeder gelöschte Datensatzfür eine Lücke (sofern es sich nicht um den letzten Datensatz handelt) und zweitenssollte eine Nummerierung auch mal eine Umsortierung oder das Setzen eines Kriteri-ums mitmachen.

Da hierzu eine gewisse Dynamik erforderlich ist, funktioniert dies nur in Verbindungmit einer Abfrage auf Basis der zu nummerierenden Tabelle. Die Abfrage verwendeteinen Ausdruck, der die Anzahl der vor dem aktuellen Datensatz liegenden Daten-sätze berechnet (siehe Abbildung 3.38). Das Ergebnis der Abfrage finden Sie in Abbil-dung 3.39.

Abbildung 3.38: Nummerieren von Daten ohne Sortierung und Gruppierung

Abbildung 3.39: Nummerierte Datensätze per Abfrage

Nummerierung von Datensätzen 161

Alternative: Nummerieren per Unterabfrage

Die gleiche Abfrage können Sie mit einem anderen Nummerierungsfeld ausstatten.Diesmal verwenden Sie eine Unterabfrage:

Nummer: (SELECT Count(*) FROM Artikel As Temp WHERE Temp.[Artikel-Nr] < Artikel.[Artikel-Nr])+1

Nummerierung von Abfrageergebnissen mit alternativen Sortierungen

Wenn die Daten nach einem anderen Feld als dem Primärschlüsselfeld der Tabelle sor-tiert werden sollen, geben Sie im WHERE-Teil der Unterabfrage einfach auf beiden Sei-ten der Bedingung das gewünschte Feld an und legen die Sortierung auch in derHauptabfrage fest.

Nummerierung von Abfrageergebnissen mit eingeschränkten Ergebnismengen

Wenn das zu nummerierende Abfrageergebnis Kriterien enthält, fügen Sie diese auchder Unterabfrage hinzu. Um alle Artikel, deren Artikelname mit »C« beginnt, zu num-merieren, verwenden Sie die Abfrage aus Abbildung 3.40. Der dort aus Platzgründennicht vollständig abgebildete Nummer-Ausdruck lautet folgendermaßen:

Nummer: (SELECT Count(*) FROM Artikel As Temp WHERE (Temp.Artikelname LIKE "C*") AND (Temp.[Artikelname] < Artikel.[Artikelname]))+1

Ausnahmsweise ist die Alternative mit der Domänen-Funktion mal nicht die langsa-mere. Die Variante mit DCount ist je nach Datensatzmenge bis zu ca. 10% schnellerals die Version mit der Unterabfrage.

Abbildung 3.40: Nummerierung einer sortierten und eingeschränkten Abfrage

162 3 Abfragen

3.9 Reflexive 1:n-Beziehungen

In Kapitel 2, Abschnitt 2.4.8, »Reflexive Beziehungen« haben Sie die Grundlagen zu refle-xiven Abfragen erhalten. Die Anzeige und Behandlung der Daten reflexiver Beziehun-gen mit Abfragen ist relativ eingeschränkt, da es sich bei Access-SQL nicht um eineprozedurale Sprache handelt.

Die meisten Aufgaben in Zusammenhang mit reflexiven Beziehungen lassen sich nurmit VBA und passenden rekursiven Funktionen erledigen.

Für einige Aufgaben ist jedoch auch eine Abfrage geeignet. Wenn Sie etwa ermittelnmöchten, in welcher Ebene der Hierarchie sich ein Mitarbeiter befindet, können Sie dieAbfrage aus Abbildung 3.41 verwenden. Wichtig ist dabei, dass Sie jeweils einen LEFTJOIN zwischen den Tabellen einrichten.

Das Feld Ebene enthält den folgenden Ausdruck:

Ebene: Anzahl([tblMitarbeiterVorgesetzter].[VorgesetzterID])+Anzahl([tblMitarbeiterVorgesetzter_1].[VorgesetzterID])+Anzahl([tblMitarbeiterVorgesetzter_2].[VorgesetzterID])+1

Die Abfrage geht von der Tabelle tblMitarbeiterVorgesetzter aus. Die weiteren Tabellensind Kopien der ersten Tabelle, die Sie durch wiederholtes Ziehen der Tabelle tblMitar-beiterVorgesetzter zum Abfrageentwurf hinzufügen. Jeder Anzahl(…)-Ausdruck ermit-telt, ob das Feld VorgesetzterID in den verknüpften Tabellen einen Wert enthält. Ist dasder Fall, liefert die Anzahl-Funktion den Wert 1.

Abbildung 3.41: Ermittlung der Anzahl der Hierarchieebenen eines Mitarbeiters

Reflexive m:n-Beziehungen 163

Abbildung 3.42 hilft beim Verständnis dieser Abfrage. Die letzten drei Spalten sind imAbfrageentwurf nicht zu sehen. Sie enthalten den direkten Vorgesetzten, den Vor-gesetzten dieses Vorgesetzten und dessen Vorgesetzten.

Der Nachteil solcher Abfragen ist, dass Sie diese in der Regel schlecht statisch anlegenkönnen, da Sie nie wissen, ob nicht einmal eine Ebene hinzukommt oder wegfällt.Daher sollten Sie Vorgänge im Zusammenhang mit reflexiven Beziehungen in derRegel mit VBA und rekursiven Funktionen durchführen.

3.10 Reflexive m:n-Beziehungen

Mit reflexiven m:n-Beziehungen verhält es sich genauso wie mit 1:n-Beziehungen. Amflexibelsten lassen sich die enthaltenen Daten auswerten, wenn Sie dies mit rekursivenFunktionen unter VBA erledigen.

Abbildung 3.42: Hierarchieebene der Mitarbeiter und Anzeige der jeweiligen Vorgesetzten