Access - Berechnete Felder in Abfragen

von Philipp Stiefel, ursprünglich veröffentlicht 2016-05-25

Letzte Revision am 2021-01-19

Einer der wesentlichen Unterschiede zwischen Microsoft Access und Excel ist, dass man in Excel in Tabellen rechnen kann. In Access sollte man Tabellen in der Regel als reine Datenspeicher betrachten. Wenn man etwas berechnen möchte, kann man dies in einer Abfrage tun. Die berechneten Felder aus der Abfrage kann man dann in Formularen und Berichten anzeigen.

In diesem Artikel zeige ich das grundsätzliche Vorgehen zur Erstellung von berechneten Feldern in Abfragen. - Sowohl mit einfachen Berechnungen als auch mit Funktionen.

Ich rate zwar in der Regel davon ab, aber wenn du unbedingt direkt in einer Tabelle Berechnungen ausführen willst, kannst du den Artikel zu berechneten Feldern in Tabellen lesen.

Update: Ich habe die Inhalte dieses Artikels jetzt auch als Video aufgenommen. Du kannst direkt an das Ende des Textes scrollen und das Video ansehen.

In Access können Ausdrücke in Abfragen verwendet werden. Ein Ausdruck ist praktisch eine Formel, die im Kontext der laufenden Access Anwendung ein Ergebnis liefert. Ein Ausdruck kann eine einfache mathematische Berechnung sein, aber genauso der Aufrufe von einer oder mehreren, verschachtelten Funktionen. – Der Fantasie sind kaum Grenzen gesetzt.

Die einfachste, mögliche Abfrage mit einer Berechnung ist:

Dies kann man in Access allerdings nur dann ausführen, wenn man die Abfrage in der SQL Ansicht öffnet. Der grafische Abfragedesigner erwartet zwingend mindestens eine Tabelle als Datenherkunft.

Üblicherweise will man in einer Datenbank aber ohnehin mit den Daten aus einer Tabellen rechnen, daher ist das obige Beispiel eher realitätsfern.

Eine einfache relativ einfache Berechnung ist die Ermittlung des Bruttopreises, wenn nur der Nettopreis in einer Datenbanktabelle gespeichert ist. Die entsprechende Spalte in meiner Tabelle heißt „Nettopreis“. Unter genau diesem Namen kann ich sie auch in dem Ausdruck ansprechen.

Die Formel für den Bruttopreis ist dann [Nettopreis] + [Nettopreis] / 100 * 19. So kannst du sie auch in Access verwenden. Im Abfrageeditor wird diese Formel anstelle des Feldnamens in die Zeile Feld eingetragen.

Das sieht dann ungefähr so aus:

Der Text „BruttoPreis“ gefolgt vom Doppelpunkt vor dem Ausdruck definiert einen Aliasnamen für dieses berechnete Feld bei der Ausgabe der Ergebnisse. Dies ist reiner Freitext. Ich hätte dort genauso gut „Wurstsalat:“ schreiben können oder den Teil ganz weglassen können. In letzterem Fall vergibt Access selbst einen Namen wie „Ausdr1“ oder „Expr1000“ für diese Spalte. - Ich empfehle dringend, immer einen aussagefähigen Alias für berechnete Spalten zu definieren.

In SQL sieht die Abfrage aus wie folgt:

Das „AS Bruttopreis“ ist auch hier nur die Definition des Namens unter dem diese Spalte im Ergebnis ausgegeben wird.

Das Ergebnis der Abfrage siehst du hier:

So weit, so gut. Technisch einwandfrei, aber…

In Deutschland haben wir keinen pauschal einheitlichen Umsatzsteuersatz auf alle Waren. Daher ist unsere Berechnung in dieser Form fachlich falsch. Es gibt Warengruppen mit ermäßigtem Umsatzsteuersatz (7%) und umsatzsteuerbefreite Waren (z.B. Briefmarken) und Dienstleistungen.

Also müssen wir pro Warengruppe den Steuersatz in der Datenbank speichern. Ich habe der Einfachheit halber eine weitere Spalte in meine Tabelle eingefügt, die pro Artikel den Steuersatz enthält. (In einer echten Datenbank wäre diese Lösung nicht ideal.)

Die Geänderte Formel sieht dann so aus: [Nettopreis] + [Nettopreis] / 100 * [UstSteuersatz]. Diese angepasste Formel tragen wir dann in der Entwurfsansicht der Abfrage ein.

Da ich für die Spalte UstSteuersatz den Datentyp Zahl/Dezimal verwendet habe, ist für Access jetzt nicht mehr automatisch klar, dass das Ergebnis unserer Berechnung als Währung formatiert werden soll. Um, dies zu erreichen Stelle ich in dem Eigenschaftsblatt für die berechnete Spalte explizit dieses Format ein.

Und hier das Ergebnis:

Die Spalte UstSteuersatz muss nicht im Abfrageergebnis ausgegeben werden, um sie in der Berechnung zu verwenden. Ich habe sie weggelassen, um dies zu demonstrieren.

Grundsätzlich beziehen sich die Feldnamen immer auf die Felder desselben Datensatzes. In den meisten Fällen ist dies auch so gewünscht. Solltest du in deiner Berechnung einen Wert aus einem anderen Datensatz benötigen, brauchst du eine Funktion, um diesen Wert zu ermitteln. – Darauf gehe ich ein anderes Mal ein.

Zum Abschluss dieses Artikels möchte ich noch zeigen, wie man eine eigene Funktion in einer Abfrage verwenden kann. Der Einfachheit halber erstelle ich eine ganz einfache Funktion, die erneut unsere Bruttopreisberechnung durchführt.

Dazu erstelle ich erst ein neues VBA Modul und schreibe dort dann folgenden VBA Code.

Wichtig dabei:

1. Das Modul darf nicht denselben Namen wie die Funktion haben.
2. In Abfragen können nur öffentliche (Public) Funktionen aus einem normalen Modul direkt angesprochen werden.

Meine Funktion heißt nun BerechneBruttopreis. Unter diesem Namen kann ich sie jetzt auch in meiner Abfrage verwenden.

Die einzelnen Argumente für die Funktion werden, durch Semikolon getrennt in runden Klammern hinter den Funktionsnamen geschrieben. Im Abfrageentwurf erstellen wir jetzt diesen Ausdruck für unsere Berechnung.

Der SQL-Code der Abfrage lautet jetzt:

Beachte dabei, dass in der SQL-Ansicht nicht das Semikolon, sondern das Komma verwendet wird, um die einzelnen Argumente zu trennen.

Das Ergebnis ist unverändert zu der vorigen Variante, mit der kompletten Berechnung direkt in der Abfrage.

Für eine sehr einfache Berechnung, wie in diesem Beispiel, ist in der Praxis sicherlich die direkte Berechnung in der Abfrage einer VBA-Funktion vorzuziehen.

Wenn die Ermittlung der gewünschten Ergebnisse komplexer ist, wird der Ausdruck innerhalb der Abfrage jedoch schnell unübersichtlich, oder es ist gar nicht mehr möglich das gewünschte Ergebnis ohne VBA-Code zu ermitteln. Dann ist es sinnvoll (oder erforderlich) eine eigene VBA-Funktion für die Berechnung zu schreiben.

Nach dem oben gezeigten Schema kannst du beliebig komplexe VBA-Funktionen in deinen Abfragen aufrufen. Einzige Bedingung ist, dass sowohl die Argumente, die an die Funktion übergeben werden, als auch der Rückgabewert der Funktion Datentypen sind, die in Textform dargestellt werden können.

Ebenso kannst du auch die zahlreichen, in Access eingebauten Funktionen in einer Abfrage verwenden. Dabei bist du keineswegs auf mathematische Berechnungen beschränkt, sondern kannst sämtliche Funktionen verwenden, die textbasierende Eingangsparameter und Rückgabewerte haben.

Ich habe die in diesem Artikel dargestellte Vorgehensweise, um Berechnungen und Funktionen in Abfragen zu verwenden, jetzt auch als Video aufgenommen und veröffentlicht. Das Video zeigt genau die oben erläuterten Inhalte und Beispiele. Wenn du also den genauen Ablauf zur Erstellung der Abfragen ansehen möchtest, schau dir das Video an.

Möglicherweise hast du gehört oder gelesen, dass die Verwendung von Funktionen in Abfragen die Performance der Abfrage beeinträchtigt. - So einfach ist das nicht. Natürlich benötigen Funktionen und andere Berechnungen Rechenzeit, um die Ergebnisse der Berechnung / Funktion zu ermitteln. Wenn du nur Funktionen und Berechnungen auf die Ausgabespalte deiner Abfrage anwendest, ist der Performanceverlust linear. Wenn deine Funktion beispielsweise 1 Millisekunde benötigt, um ein einzelnes Ergebnis zu berechnen, und deine Abfrage 1000 Datensätze zurückgibt, dauert die Abfrage 1 Sekunde länger, um die vollständige Ergebnismenge abzurufen. - Beachte, dass Access beim Scrollen in einer größeren Liste von Datensätzen Ergebnisse abruft oder aktualisiert und dabei Funktionen und Berechnungen (erneut) ausführt. - Insgesamt ist der Performanceverlust in diesem Szenario angemessen und sollte nicht überraschen.

Die Situation ist völlig anders, wenn du eine Funktion in der Where-Klausel deiner Abfrage verwendest. Dann muss sie nicht nur auf die Datensätze im Endergebnis der Abfrage angewendet werden, sondern auf eine viel größere Anzahl, bis zur Gesamtanzahl der Datensätze in den Quelltabellen der Abfrage. Dies kann es der Datenbankengine auch unmöglich machen, bei der Verarbeitung der Abfrage Indizes zu verwenden. Der Performanceverlust für die Abfrage, der durch die Funktionen oder die Berechnung verursacht wird, kann sehr schwerwiegend sein.

Ich habe einen eigenen Text zu Regeln zur Optimierung der Abfrageperformance geschrieben, der auch die Auswirkungen von Funktionen in Abfragen ausführlich behandelt.

Wenn dir dieser Text und das Video gefallen haben, abonniere doch unten meinen Newsletter, um über neue Veröffentlichungen auf meiner Seite informiert zu werden.

Share this article: