Home > Artikel > Ausgabe 1/2019 > Laufende Summen in Abfragen

Laufende Summen in Abfragen

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Immer wieder taucht die Frage auf, wie man in Access die Inhalte von Zahlenfelder mehrerer Datensätze aufsummiert. Unter Excel ist das einfach – dort trägt man einfach die Summe aus der Summe bis zur vorherigen Zeile plus dem Wert der aktuellen Zeile ein. Ändert man Werte, ändern sich auch die Summen. Im Artikel »Laufende Summen in Tabellen« haben wir uns angesehen, wie dies mit einem zusätzlichen Feld in Tabelle gelingt. In diesem Artikel schauen wir uns an, wie dies unter Access unter Verwendung von Abfragen gelingt.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1901_KumulierenInAbfragen.accdb.

Beispieltabelle

Wie im oben bereits erwähnten Artikel Laufende Summen in Tabellen verwenden wir auch in diesem Artikel die Tabelle tblAusgaben als Beispiel (siehe Bild 1).

Tabelle für Beispielzwecke

Bild 1: Tabelle für Beispielzwecke

Laufende Summe per Abfrage

Dort finden Sie allerdings kein Feld namens LaufendeSumme, mit dem wir im Artikel Laufende Summen in Tabellen gezeigt haben, wie Sie die Werte eines Feldes in einem anderen Feld aufsummieren. Stattdessen wollen wir ein solches Feld mithilfe einer Abfrage hinzufügen.

Während wir bei der Methode mit dem Aufsummieren der Werte direkt in der Tabelle die Datensätze der Tabelle durchlaufen und diese dabei Datensatz für Datensatz aufsummiert haben, sieht die Technik bei Abfragen ein wenig anders aus.

Hier können wir zwar durch eine geschickt formulierte Abfrage mit einem berechneten Feld auf die Werte des vorherigen Datensatzes zugreifen, aber nicht auf den Wert, den das berechnete Feld für den vorherigen Datensatz geliefert hat.

Laufende Summe nach Primärschlüsselwert

Stattdessen müssen wir, wenn wir etwa den Wert eines Feldes über alle Datensätze aufsummieren wollen, immer die Summe über das Feld für alle Datensätze ermitteln, die sich in der Reihenfolge vor dem aktuellen Datensatz befinden. Dazu nutzen wir eine Abfrage, die alle Felder der Tabelle tblAusgaben enthält. Außerdem fügen wir dieser Abfrage ein weiteres Feld hinzu, das wir als berechnetes Feld auslegen.

Also geben wir den Namen für die anzuzeigende Spalte vorn an, gefolgt von einem Doppelpunkt und dem Ausdruck, der die anzuzeigenden Werte festlegt.

Für den ersten Datensatz soll dieser Ausdruck den Wert des Feldes Betrag des aktuellen Datensatzes ausgeben, für die übrigen Datensätze jeweils die Summe aller Werte des Feldes Betrag aller Datensätze, die in der Reihenfolge über dem aktuellen Datensatz liegen und dazu noch den Wert des Feldes Betrag des aktuellen Datensatzes addieren. Dieser Ausdruck sieht wie folgt aus:

LaufendeSumme: DomSumme("Betrag";"tblAusgaben";"AusgabeID <=" & [AusgabeID])

Die Funktion DomSumme entspricht der DSum-Funktion unter VBA. Sie erwartet als ersten Parameter den Namen des Feldes, dessen Werte aufsummiert werden sollen. Der zweite Parameter gibt den Namen der Tabelle oder Abfrage an, welche die Werte liefert.

Und der dritte Parameter liefert das Kriterium, das festlegt, welche Datensätze von der Summenbildung berücksichtigt werden sollen. In diesem Fall lautet dieser "AusgabeID <=" & AusgabeID.

Das bedeutet, dass der Wert des Feldes AusgabeID der Datensätze der Datenquelle aus dem zweiten Parameter mit dem Wert des Feldes AusgabeID des aktuellen Datensatzes verglichen wird.

Der Entwurf dieser Abfrage sieht schließlich wie in Bild 2 aus. Für den Datensatz mit dem Wert 4 im Feld AusgabeID liefert diese Funktion also die Summe der Werte des Feldes Betrag für die Datensätze, die im Feld AusgabeID einen Wert kleiner oder gleich 4 aufweisen.

Abfrage mit laufender Summe, sortiert nach dem Primärschlüsselwert

Bild 2: Abfrage mit laufender Summe, sortiert nach dem Primärschlüsselwert

Das Ergebnis dieser Abfrage sieht für unsere Beispieldaten wie in Bild 3 aus.

Ergebnis der Summenbildung, sortiert nach dem Primärschlüsselwert

Bild 3: Ergebnis der Summenbildung, sortiert nach dem Primärschlüsselwert

Laufende Summe nach Datum

Um die laufende Summe nach dem Datum zu ermitteln, benötigen wir erst einmal ein entsprechendes Kriterium für die Sortierreihenfolge der Datensätze. In diesem Fall wollen wir die Daten zuerst nach dem Datum sortieren, das im Feld Ausgabedatum gespeichert ist, und bei gleichen Datumsangaben nach dem Primärschlüsselwert. Der Entwurf der dazu notwendigen Abfrage sieht wie in Bild 4 aus.

Sortierung nach Ausgabedatum und AusgabeID

Bild 4: Sortierung nach Ausgabedatum und AusgabeID

Wichtig ist hier, dass wir nicht einfach die beiden Felder AusgabeID und Ausgabedatum mit dem Wert Aufsteigend in der Zeile Sortierung versehen können. Dann würde die Sortierung nämlich erst nach dem Primärindex erfolgen und dann nach dem Ausgabedatum, was praktisch bedeuten würde, dass die Datensätze nur nach dem Primärschlüsselfeld sortiert werden würden – die Werte dieses Feldes sind ja eindeutig und somit ist kein zweites Sortierkriterium erforderlich.

Da wir aber dennoch wollen, dass das Feld AusgabeID ganz links als erstes Feld angezeigt wird, müssen wir es ein zweites Mal zur Abfrage hinzufügen. Dieses zweite Feld können wir dann mit der aufsteigenden Sortierung versehen. Außerdem sollten wir seine Anzeige deaktivieren, indem wir das Häkchen aus dem Kontrollkästchen in der Zeile Anzeigen entfernen.

Summenfeld hinzufügen

Nun fügen wir der Abfrage noch ein weiteres Feld hinzu, das als berechnetes Feld ausgelegt wird. Das heißt, dass wir das Feld nicht aus der Feldliste in das Entwurfsraster ziehen, sondern den Ausdruck selbst formulieren. Wo ist der Unterschied dieses Summenfelds im Vergleich zur ersten Abfrage, wo wir nach dem Primärschlüsselfeld sortiert haben? Wir müssen das Kriterium ändern, da wir ja nun nach dem Feld Ausgabedatum sortieren.

Normalerweise sollte dies also funktionieren, wenn wir das Feld zur Ermittlung der laufenden Summe etwa wie folgt hinzufügen:

LaufendeSumme: DomSumme("Betrag";"tblAusgaben";"Ausgabedatum <=" & [Ausgabedatum])

Das Ergebnis sieht allerdings wie in Bild 5 aus – wir erhalten #Fehler als Ergebnis. Zum Glück erhalten wir, nachdem wir auf den Wert #Fehler klicken, noch eine aufklärende Fehlermeldung. Diese gibt den Abfrageausdruck aus, der im Falle des ersten Datensatzes so lautet (die letzte Stelle wurde aus offensichtlich aus Platzgründen abgeschnitten):

Sortierung nach Ausgabedatum und AusgabeID mit Fehler

Bild 5: Sortierung nach Ausgabedatum und AusgabeID mit Fehler

Ausgabedatum <= 02.01.201

Das sieht logisch aus – mit dem Ausdruck <= 02.01.2019 hätten wir auch in der Zeile Kriterien des Entwurfs einer Abfrage Probleme, weil 02.01.2019 kein gültiges Datumsformat ist.

Also wandeln wir den Ausdruck noch in eine gültige Form um. Wir könnten das Datum in Raute-Zeichen einfassen, aber das ist nicht zuverlässig genug. Also wandeln wir das Datum einfach in das Format um, in dem Datumswerte auch in den Tabellen gespeichert werden, nämlich in Long.

Wenn Sie Datum und Zeit verwenden wollen, benötigen Sie den Datentyp Double wegen der Nachkommastellen, welche die Uhrzeit repräsentieren.

Die Spalte sieht nun also wie folgt aus:

LaufendeSumme: DomSumme("Betrag";"tblAusgaben";"Ausgabedatum <=" & ZLong([Ausgabedatum]))

Damit erhalten wir nun das Ergebnis aus Bild 6. Wir erhalten zwar nicht #Fehler als Ergebnis, aber die laufende Summe ist auch noch nicht korrekt. Sie ermittelt die laufende Summe nämlich immer für alle Tage eines Datums plus der vorhergehenden Tage. Auch das ist logisch, denn wir haben ja das Kriterium so formuliert.

Laufende Summe nach dem Datum – noch nicht perfekt.

Bild 6: Laufende Summe nach dem Datum – noch nicht perfekt.

Also ist noch eine Anpassung notwendig, nämlich die Ergänzung um das Primärschlüsselfeld als weiteres Kriterium. Das Feld LaufendeSumme sieht dann wie folgt aus:

LaufendeSumme: DomSumme("Betrag";"tblAusgaben";"Ausgabedatum <=" & ZLong([Ausgabedatum] & " AND AusgabeID <= " & [AusgabeID]))

Wir fügen also mit dem AND-Schlüsselwort ein weiteres Kriterium hinzu, mit dem wir festlegen, dass die AusgabeID der aufsummierten Einträge auch noch kleiner sein soll als die AusgabeID des aktuellen Datensatzes. Das schlägt allerdings fehl (wieder mit #Fehler als Ergebnis). Warum das? Ein Anklicken des Feldes mit dem Inhalt #Fehler liefert leider keine Fehlermeldung wie beim vorherigen Versuch.

Eine kurze Analyse unseres Ausdrucks ergibt dann auch einen Denkfehler: Wir wollen nicht die laufende Summe der Beträge für alle Einträge, deren AusgabeID und Ausgabedatum kleiner sind als die Werte im aktuellen Datensatz plus dem Betrag des aktuellen Datensatzes ermitteln, sondern zunächst die Werte für alle Datensätze, deren Datum kleiner oder gleich als das Datum des aktuellen Datensatzes ist (plus dem Betrag des aktuellen Datensatzes) und dann noch die Reihenfolge der Primärschlüsselwerte berücksichtigen. Das lässt sich in Form zweier einzelner Kriterien in der DomSumme-Funktion nicht realisieren.

Hier benötigen wir einen kleinen Trick. Wie können wir das Primärschlüsselfeld AusgabeID berücksichtigen, wenn es in der Priorität der Sortierung dem Feld Ausgabedatum untergeordnet ist?

Bevor wir uns das anschauen, müssen wir allerdings den Grund für den Fehler in der aktuellen Version des Abfrageergebnisses herausfinden.

Dieser hängt mit den Ländereinstellungen des Systems zusammen. In Deutschland wird beispielsweise das Komma als Dezimaltrennzeichen verwendet, etwa wie in 1,234. Access SQL verwendet allerdings den Punkt als Dezimaltrennzeichen, also 1.234. Nun versuchen wir in obigem Ausdruck also, einen Zahlenwert mit einem Wert zu vergleichen, der kein gültiges Zahlenformat aus Sicht von Access SQL aufweist, da dieser das Komma als Dezimaltrennzeichen enthält. Dieses Problem können wir bereits lösen, indem wir mit der Replace-Funktion das Komma durch den Punkt ersetzen:

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!