CLS: Tipps & Tricks: Access-Abfragen



Tipps & Tricks: Access-Abfragen

Auf dieser Seite finden Sie 7 Tipps & Tricks für Access-Abfragen. Soweit keine Einschränkungen dazu genannt werden, gelten diese Tipps & Tricks für alle Versionen von Access. Um SQL-Befehle einzugeben, erstellen Sie bitte eine neue Abfrage und wechseln von der "normalen" Entwurfsansicht in die SQL-Entwurfsansicht. Das dort typischerweise noch vorhandene SELECT;-Statement können Sie löschen und durch den jeweils genannten Beispielcode ersetzen.

Wenn in den folgenden Erläuterungen Texte wie MsgBox formatiert sind, handelt es sich um konkret einzugebende Inhalte wie VBA-Code oder die Eingabe von Werten. Menüs wie Datei Speichern sind wie hier zu sehen formatiert. Schaltflächen oder Registerkarten auf Dialogen werden wie Menüs behandelt.

Alle Tipps sind nach bestem Wissen geprüft, aber selbstverständlich ohne Gewähr. Sollten Sie doch einen Fehler darin entdecken, würden wir uns freuen, wenn Sie uns per E-Mail Bescheid sagen.

Entsprechend dem Tipp "Alle Tabellen der Datenbank ermitteln mit SQL" ändert sich hier der Typ des Objekts. Daher heißt der SQL-Befehl:

SELECT Name
FROM MSysObjects
WHERE ([Type] = -32768);

(Siehe dazu auch den Tipp "Alle Formulare ermitteln (2): wirklich alle")

Da Access-Datenbanken alle ihre Objekte in der (meistens nur ausgeblendeten) Tabelle 'MSysObjects' verwalten, können Sie diese auch per SQL-Befehl auslesen. Geben Sie dazu den folgenden SQL-Befehl in das SQL-Schema eines Abfrageentwurfs ein:

SELECT Name
FROM MSysObjects
WHERE (([Name] NOT LIKE "?Sys*") AND ([Type] = 1));

Um alle Tabellen einschließlich der MSys...-Tabellen im Ergebnis zu sehen, lassen Sie die dritte Zeile weg und setzen das Semikolon hinter die zweite Zeile. (Siehe dazu auch den Tipp Alle Tabellen einer Datenbank ermitteln mit VBA)

Obwohl durchaus häufig benötigt, scheinen Berechnungen in Abfragen eher weniger bekannt zu sein. Daher sollen hier ein paar Beispiele die Möglichkeiten aufzeigen. Die allgemeine Syntax lautet:

neuerFeldname: Formel

wobei sich neuerFeldname an die Regeln für Feldnamen halten muss. Außerdem muss er insofern neu sein, als dass er weder in den Spalten daneben noch in der/den Datenquelle/n der Abfrage vorkommen darf. Die Berechnung wird in der nächsten freien Spalte des Abfrage-Entwurfs eingetragen, wo normalerweise die anzuzeigenden Feldnamen stehen. Dabei kann die Berechnung sowohl eine einfache Formel wie die Ermittlung des Bruttopreises

Brutto: [vrkPreisNetto] * (1 + [artMWSt])

sein als auch integrierte Funktionen enthalten:

PLZ_Bereich: Links([kndPLZ]; 2)

Auch wenn dafür alle VBA-Funktionen genutzt werden können, steht hier immer deren deutscher Name und ein Semikolon als Trennzeichen. Wenn Sie deren englische Bezeichnung schreiben, übersetzt Access diese anschließend ins Deutsche. Sollten die vorhandenen VBA-Funktionen nicht ausreichen, erstellen Sie sich in einem beliebigen Modul eine eigene Funktion:

Function ErstesWort(varText As Variant) As Variant
Dim intPos As Integer
 
If intPos > 0 Then
ErstesWort = Left(varText, intPos - 1)
Else
ErstesWort = varText
End If

In einer Abfrage der gleichen Datenbank können Sie diese eigene Funktion dann wie gewohnt aufrufen:

Vorname: ErstesWort([kndName])

Die korrekte Deklaration der Datentypen (im obigen Beispiel also beide Male eigentlich As String) führt allerdings immer wieder zu Laufzeitfehlern, daher sollten diese zur Nutzung in Abfragen einfach als As Variant deklariert werden.

In Feldern, die rechts von diesem berechneten Feld stehen, können Sie bereits auf deren Ergebnisse zurückgreifen. Wenn als wie im ersten Beispiel dieses Tipps ein Brutto-Preis berechnet wurde, kann anschließend so der Kunden-Rabatt berücksichtigt werden:

BruttoInklRabatt: [Brutto] * (1 - [rcgRabatt])

Diese Folgeberechnung funktioniert nicht innerhalb von Gruppierungsabfragen, dort müssen Sie eine darauf basierende Abfrage zum Gruppieren benutzen.

Je mehr Informationen eine Datenbank enthält, desto wichtiger ist es, diese zu komprimieren. Gruppierungsabfragen sind dazu ideal. Eine typische Gruppierung von Verkaufsdaten sieht so aus:

SELECT kndName, Sum(Preis) As SummeVonPreis
FROM qryKundenArtikelVerkaeufe
GROUP BY kndName;

Dabei hat die Abfrage qryKundenArtikelVerkaeufe die drei einzelnen Tabellen bereits zusammengeführt und aus Einzelpreis und Menge den Preis in einem Feld berechnet. Sobald Sie diese Beispiel um den Artikelnamen erweitern, müssen mehr Zeilen angezeigt werden (falls nicht jeder Kunde exakt nur einen Artikel gekauft hätte):

SELECT kndName, artName, Sum(Preis) As SummeVonPreis
FROM qryKundenArtikelVerkaeufe
GROUP BY kndName, artName;

Das Hinzufügen weiterer Aggregat-Funktionen wie Summe, Mittelwert, Anzahl o.ä. verändert die Anzahl der Datensätze im Ergebnis hingegen nicht.

Um nur alle Kunden je einmal zu ermitteln, die jemals etwas gekauft haben (siehe Tipp "Gute Kunden, schlechte Kunden: Inner und Outer Join"), beschränken Sie Abfrage auf eine reine Gruppierung ohne Aggregatfunktionen:

SELECT kndName
FROM qryKundenArtikelVerkaeufe
GROUP BY kndName;

Das Gegenstück dazu ist etwa die Ermittlung des Gesamtumsatzes in einer solchen Gruppierungsabfrage ohne Gruppe:

SELECT Sum(Preis) As SummeVonPreis
FROM qryKundenArtikelVerkaeufe;

Auch berechnete Felder (siehe Tipp "Berechnete Felder in Abfragen") können gruppiert werden:

SELECT Year([vrkDatum]) AS Verkaufsjahr, Sum(Preis) As SummeVonPreis
FROM qryKundenArtikelVerkaeufe
GROUP BY Year([vrkDatum]);

Diese Abfrage ermittelt damit trotz des tagesgenauen Verkaufsdatums die jährliche Umsatzsumme.

Bei einer 1:n-Verknüpfung zwischen einer Kundentabelle und einer Verkaufstabelle mit den Feldern kndID (in tblKunden) und vrkkndIDRef (als Fremdschlüssel in tblVerkaeufe) sehen Sie in einer Abfrage alle Kunden und ihre Verkäufe. In SQL liest sich das beispielsweise so:

SELECT tblKunden.*, tblVerkaeufe.*
FROM tblKunden
INNER JOIN tblVerkaeufe ON tblKunden.kndID = tblVerkaeufe.vrkkndIDRef;

Falls Sie allen "guten" Kunden, also denjenigen, welche auch etwas gekauft haben, nun einen Dankesbrief schreiben wollen, lassen Sie auf obiger Abfrage qryKundenUndVerkaeufe basierend eine Gruppierungsabfrage erstellen:

SELECT kndID, kndFirma, kndStrasse, kndHausnummer, kndPLZ, kndOrt
FROM qryKundenundVerkaeufe
GROUP BY kndID, kndFirma, kndStrasse, kndHausnummer, kndPLZ, kndOrt;

Das geht übrigens in diesem Fall bedeutend kürzer und schneller, wenn Sie einfach das Schlüsselwort SELECT DISTINCT statt der Gruppierung benutzen:

SELECT DISTINCT kndID, kndFirma, kndStrasse, kndHausnummer, kndPLZ, kndOrt
FROM qryKundenundVerkaeufe;

Auch dadurch wird jeder Kunde nur einmalig genannt, obwohl er vielleicht mehrfach gekauft hat. Es könnte aber in der Tabelle tblKunden auch noch andere Datensätze geben, zu denen keine Verknüpfung mit tblVerkaeufe existiert, sozusagen die "schlechten" Kunden. Denen wollen Sie selbstverständlich auch schreiben und ihnen Ihr Warenangebot nochmals ans Herz legen. Doch wie ermitteln Sie diese?

Falls Sie in der Abfrage qryKundenUndVerkaeufe nun alle wegfiltern wollen, deren Einkaufsmenge gleich 0 oder NULL ist, bleiben leider keine mehr übrig. Der Inner Join verlangt nämlich, dass auf beiden Seiten der Verknüpfung gleiche Inhalte stehen. Daher können hier nur Datensätze aus tblKunden enthalten sein, welche auch auf der Verkaufseite ein Gegenstück finden. Kurz gesagt: Kunden ohne Verkauf stehen hier schon mal prinzipiell nicht drin.

Sie müssen am Inner Join, an der Verbindung zwischen den Tabellen, ansetzen. Machen Sie im Abfrageentwurf einen Doppelklick auf den mittleren Teil der Verknüpfungslinie. Im daraufhin erscheinenden Dialog wählen Sie als Typ "Beinhaltet ALLE Datensätze aus 'tblKunden' und nur die Datensätze aus 'tblVerkaeufe', ..." und bestätigen. Diese Verknüpfungslinie zeigt nun einen Pfeil.

Und das Abfrage-Ergebnis zeigt (vermutlich am Ende) erstmalig Kunden ohne Verkauf, falls es denn überhaupt solche gab. Jetzt erst kommen Sie wieder mit Filtern zum Erfolg, weil dort die vrk...-Felder NULL sind. Die Abfrage lautet also:

SELECT tblKunden.*, tblVerkaeufe.*
FROM tblKunden
LEFT JOIN tblVerkaeufe ON tblKunden.kndID = tblVerkaeufe.vrkkndIDRef
WHERE vrkID Is Null;

Obwohl die gesuchten Datensätze der "schlechten" Kunden scheinbar nur die Umkehrung eines Filters für "gute" Kunden sind, braucht es doch völlig unterschiedliche Methoden, sie zu ermitteln.

Wenn in Ihrer Datenbank beispielsweise Kunden und Lieferanten gespeichert sind, unterscheiden sich deren Informationen so sehr, dass es sinnvoll ist, sie in getrennten Tabellen zu speichern. Für eine Adreßänderung oder die Einladung zum Firmenjubiläum möchten Sie aber einen Brief an alle gemeinsam schreiben?

Dann braucht es eine UNION-Abfrage, um Datensätze frei zu mischen. Diese besteht im Grunde aus beliebig vielen Abfragen, die durch das Schlüsselwort UNION aneinandergehängt werden:

SELECT kndFirma, [kndStrasse] & " " & [kndHausnummer] AS Adresse, kndPLZ, kndOrt
FROM tblKunden
 
UNION
 
SELECT lfrName, lfrAdresse, lfrPLZ, lfrOrt
FROM tblLieferanten
 
ORDER BY kndFirma;

Wie Sie sehen, müssen die Feldnamen keineswegs identisch sein (bei Access nicht einmal die Datentypen!), sondern nur deren Anzahl. Für eine Sortierung wie hier sind immer die ersten Feldnamen maßgeblich.

Duplikate werden in dieser Variante übrigens automatisch weggefiltert, so dass diese Version bei großen Datenmengen langsam ist. Wenn Sie Duplikate im Ergebnis zulassen wollen oder wissen, dass ohnehin keine vorkommen, schreiben Sie UNION ALL.

Ein immer wieder diskutiertes Thema ist das fortlaufende Nummerieren von Datenzeilen in Access, für das es verschiedene Lösungen gibt. Die perfekte Version ist diese mit SQL-eigenen Methoden. Dabei wird in jeder Zeile die Anzahl der Datensätze ermittelt, welche kleiner ist als der laufende Datensatz.

Zwingende Voraussetzung für diese Technik ist aber, dass dieser Datensatz überhaupt eindeutig zu erkennen ist, am besten über eine ID. Wenn das nicht der Fall ist, können Sie immer noch auf den Tipp "Zeilen fortlaufend nummerieren mit VBA-Funktion?" zurückgreifen. Dabei sollten Sie deren Vor- und Nachteile nicht übersehen:

  • Die SQL-Version funktioniert für alle SQL-Datenbanken, also außer Access beispielsweise auch SQL-Server, Oracle o.ä.
  • Sie ist nicht schnell (furchtbare 50 sec. für knapp 15.000 Datensätze), aber zuverlässig
  • Für Access-Berichte ist sie nicht nötig, siehe dazu den Tipp "Zeilen fortlaufend nummerieren im Bericht"
Das folgende Beispiel geht von einer Tabelle tblAdressen mit Kunden-Adressen aus, bei denen das Feld KundenNr der Primärschlüssel und damit eindeutig ist. Die Nummern sind jedoch vielleicht frei vergeben worden und wären selbst als AutoWert nach Löschaktionen lückenhaft, sie sind also nicht direkt geeignet. Da sie jedoch eindeutig sind, lässt sich für jeden Datensatz feststellen, wieviele Nummern kleiner sind als seine eigene und jeweils 1 addieren:

SELECT (Select Count (*) FROM [tblAdressen] as X
WHERE [X].[KundenNr] < [tblAdressen].[KundenNr])+1 AS Nummer,
tblAdressen.*, tblAdressen.KundenNr
FROM tblAdressen
ORDER BY tblAdressen.KundenNr;

Im beliebigen Feldnamen X wird mittels Unterabfrage die Anzahl an Datensätzen ermittelt, welche kleiner als der aktuelle sind, und der Wert um 1 erhöht. Das bedeutet natürlich, dass diese Unterabfrage im obigen Beispiel 15.000-mal aufgerufen wird, was die lausige Performance erklärt.