MS-Office-Forum

MS-Office-Forum (http://www.ms-office-forum.net/forum/index.php)
-   Microsoft Access (http://www.ms-office-forum.net/forum/forumdisplay.php?f=60)
-   -   Wie bildet ihr eine laufende Summe? (http://www.ms-office-forum.net/forum/showthread.php?t=349609)

rainerZZ 09.02.2018 19:00

Wie bildet ihr eine laufende Summe?
 
Hallo zusammen,

wenn ihr große Datenmengen (20.000 -50.000 DS) kumulieren müsst auf Basis einer Tabelle dieser Form:

Datum Kategorie Wert
01.01.2017 A 100
01.01.2017 B 200
01.02.2017 A 150
01.02.2017 B 400

und als Ergebnis haben wollt:

01.01.2017 A 100
01.01.2017 B 200
01.02.2017 A 250
01.02.2017 B 600

wie macht ihr das genau?

Ich habe die 2 Varianten von donkarl (Möglichkeit mit domSumme und Möglichkeit mit Unterabfrage: select sum(...) usw.) probiert. Beides sehr langsam.

Ich habe sogar probiert, die DS nach Excel zu schieben und dort mit einer Pivot zu kumulieren. Das geht erhebllich schneller, aber ist auch irgendwie nicht zielführend.

Habt ihr da irgendwelche Tipps?

ebs17 09.02.2018 19:25

Die erste Gegenfrage wäre, was mit diesem Ergebnis angestellt wird. Man könnte auch fragen, wer schaut sich 50.000 Datensätze wirklich an, mit großen Augen, großem Bildschirm und Dauerscrollen?

Zitat:

Möglichkeit mit domSumme und Möglichkeit mit Unterabfrage: select sum(...)
Die Langsamkeit ergibt sich aus dem systematischen Problem dieses Ansatzes, siehe auch Grundlagen - SQL ist leicht (10) - Nummerierung in Abfragen

Ein anderer Ansatz wäre, die Datensätze in richtiger Reihenfolge zu fixieren. Dann kann man jeweils zum Vorwert den Wert des Datensatzes hinzuzählen. Das kann man in einem Recordset vornehmen. Da Formulare und Berichte als Datenbasis in Wirklichlkeit immer ein Recordset haben und nicht eine Abfrage, kann man dort unmittelbar gleiches erledigen. Womit sich dann der Kreis schließt hin zur ersten Frage.

rainerZZ 09.02.2018 19:48

Zitat:

Die erste Gegenfrage wäre, was mit diesem Ergebnis angestellt wird. Man könnte auch fragen, wer schaut sich 50.000 Datensätze wirklich an, mit großen Augen, großem Bildschirm und Dauerscrollen?
Die Datensätze beinhalten Materialnummern mit Stückzahlen und generierten Umsätzen. Jeden Monat kommen ca. 3000 DS dazu. Es ist notwendig alle zu kumulieren, da diese in einem Hauptformular alle sichtbar sein müssen. Der User filtert dann sehr dynamisch seine DS, die er sehen will, heraus. Z.B. eine Kalkulation von Materialnummer xy der letzten 24 Monate.


Zitat:

Ein anderer Ansatz wäre, die Datensätze in richtiger Reihenfolge zu fixieren. Dann kann man jeweils zum Vorwert den Wert des Datensatzes hinzuzählen. Das kann man in einem Recordset vornehmen. Da Formulare und Berichte als Datenbasis in Wirklichlkeit immer ein Recordset haben und nicht eine Abfrage, kann man dort unmittelbar gleiches erledigen. Womit sich dann der Kreis schließt hin zur ersten Frage.
ok, ich würde das gerne mal testen. Die DS in die richtige Reihenfolge bringen, wäre kein Problem. Was meinst du mit fixieren? Und wie erkenne ich, wann das kumulieren von neu beginnen muss, was ja bei jeder Materialnummer erfolgen muss? Und das Recordset schreibe ich dann in eine Tabelle, oder wie verwende ich die kumulierten Daten für andere Abfragen bspw.?
Hast du vlt. ein kleines Bsp. mit Pseudocode?

fraeser 09.02.2018 20:46

Folgende Abfrage mal ins Ungewisse:
(Felder Datum1 (Datum/Uhrzeit); Kategorie (Text); Wert (Zahl/Double)):

Code:

SELECT A.Datum1, A.Kategorie, (Select Sum(Wert) From Tabelle1 B
Where B.Datum1 <= A.Datum1 and B.Kategorie = A.Kategorie) AS Betrag_Kum
FROM Tabelle1 AS A
ORDER BY A.Datum1, A.Kategorie;

Das müsstest Du dann bzgl. der Jahre anpassen etc.!
... und der Feldnamen, Tabellenname.

Bei größeren Datenmengen alle Felder indizieren (in der Tabelle)!

Frage wäre auch, ob alle Datensätze jeweils zum 01. eines Monats geschrieben werden....

ebs17 09.02.2018 21:37

Zitat:

Was meinst du mit fixieren?
In einem Recordset wie auch in einem Formular(recordset) kann man datensatzweise vorwärts und rückwärts springen (MoveNext & Co.). Das geht ja nur, wenn es da eine verwendbare Reihenfolge gibt. EIne solche Reihenfolge gibt es in Tabellen und Abfragen nicht, die Darstellung von Datenblattansichten sind auch "nur" Umsetzungen von Recordsets.
Hat man eine feste Reihenfolge, wie man das aus Excel kennt, kann man zeilenweise aufsummieren.

Zitat:

Und wie erkenne ich, wann das kumulieren von neu beginnen muss
Code:

ORDER BY Datum, Kategorie
Ich denke, wenn eine neue Kategorie kommt, könnte man das mitkriegen.

Ein Recordset kann man unmittelbar einem Formular als Datenherkunft zuweisen und damit Inhalte anzeigen, man kann es aber nicht per SQL in einer Abfrage verwenden oder einer Tabelle zuweisen.
Daher die manchen unbedeutend erscheinende Frage:
Zitat:

was mit diesem Ergebnis angestellt wird
Gemeint ist da nicht nur der nächste Tippelschritt, sondern die ganze Verwendung.
Also erst nachdenken, was man wirklich braucht. Dahingehend erfolgt dann eine Umsetzung.

fraeser 09.02.2018 21:55

Hallo Eberhard,
kannst Du mir erklären, warum Du zum o.g. Thema auf Recordset rumhackst?
Ich kann das zur Lösung zum Thema des TS nicht ganz nachvollziehen...
Kannst Du mich (auch) aufklären, bitte?

ebs17 09.02.2018 22:13

@Jan: Hast Du das verlinkte Thema gelesen? Laufende Nummer ist das gleiche Thema wie laufende Summe.

Meine Maßzahl, wo ich da mit der herkömmlichen Abfrage etwa aussteige, liegt bei 10.000. Dann wird es grottig langsam.

fraeser 09.02.2018 22:24

Ich habe beim TS eine PK-ID in der Tabelle vorausgesetzt.
Ich verstehe aber - trotz Deines Links - nicht Dein "Anliegen" zu diesem Thread!?

ebs17 09.02.2018 22:34

Zitat:

Ich verstehe aber - trotz Deines Links - nicht ...
Probiere es aus mit steigenden DS-Zahlen. Manche Dinge muss man fühlen.

rainerZZ 09.02.2018 23:14

@ebs17,

ich habe jetzt ein wenig rumgetestet und habe ein erstes Ergebnis, welches die Werte im Direktbereich, kumuliert nach Kategorie und Datum, ausgibt:


Code:

Dim RSa As DAO.Recordset
Dim sum, Kat_vorher, kat_jetzt
sum = 0

Set RSa = CurrentDb.OpenRecordset("SELECT * FROM TEST_KUMM order by Kategorie, Datum")
Do While Not RSa.EOF

If RSa.AbsolutePosition = 0 Then
    sum = RSa.Fields("wert")
Else

    RSa.MovePrevious
    Kat_vorher = RSa.Fields("kategorie")
    RSa.MoveNext
    kat_jetzt = RSa.Fields("Kategorie")
   
    If Kat_vorher <> kat_jetzt Then
    sum = 0
    End If

sum = sum + RSa.Fields("wert")

End If

Debug.Print sum

RSa.MoveNext

Loop
RSa.Close
Set RSa = Nothing

Aus deiner Sicht passend?

Was ich jetzt nicht ganz verstehe, wie ich dieses Ergebnis als Datenbasis für eine weitere Abfrage bekomme (ohne das Ergebnis bspw. in eine Tabelle zu schreiben). Weil ich mit den jetzt kumulierten Werten in einer Abfrage Berechnungen durchführen möchte...

fraeser 09.02.2018 23:22

unabhängig Deiner Lösung:
Hast Du das aus #4 probiert?

fraeser 10.02.2018 06:53

@ebs17

Habe mal eine Tabelle mit über 100.000 DS erstellt (indizierte Spalten).
Wenn ich in der Abfrage das gesuchte Jahr und den Monat - bis wohin kumuliert werden soll - wähle, dauert die Abfrage keine halbe Sekunde.

Code:

SELECT A.Datum1 AS Datum, A.Kategorie, (Select Sum(Wert) From Tabelle1 B Where B.Datum1 <= A.Datum1 and B.Kategorie = A.Kategorie) AS kumuliert
FROM Tabelle1 AS A
WHERE YEAR(A.Datum1)=3000 And MONTH(A.Datum1)=12
ORDER BY A.Datum1, A.Kategorie;


fraeser 10.02.2018 06:59

Bzw. NUR kumuliert im gesuchten Jahr:

Code:

SELECT A.Datum1 AS Datum, A.Kategorie, (Select Sum(Wert) From Tabelle1 B Where B.Datum1 <= A.Datum1 and B.Kategorie = A.Kategorie AND YEAR(B.Datum1) = 2019 ) AS kumuliert
FROM Tabelle1 AS A
WHERE (((Year([A].[Datum1]))=2019) AND ((Month([A].[Datum1]))=12))
ORDER BY A.Datum1, A.Kategorie;


rainerZZ 10.02.2018 08:54

Zitat:

unabhängig Deiner Lösung:
Hast Du das aus #4 probiert?
Ja, das ist ja die Lösung, wie sie donkarl auch beschreibt. Wie ebs17 sagt, ab einer gewissen Datenmenge extrem langsam.

Zitat:

Habe mal eine Tabelle mit über 100.000 DS erstellt (indizierte Spalten).
Wenn ich in der Abfrage das gesuchte Jahr und den Monat - bis wohin kumuliert werden soll - wähle, dauert die Abfrage keine halbe Sekunde.
Wenn du Jahr und Monat vorher schon filterst, hast du ja nur noch ein Bruchteil der 100.000 DS zum kumulieren. Klar könnte man hingehen und nur die Daten bspw. von 2017 kumulieren, aber das sind bei mir dann auch für eine Dezemberbetrachtung ca. 12*3000 DS.

Nouba 10.02.2018 08:54

@Jan,

hast Du innerhalb der halben Sekunde auch einmal den Datensatzzeiger auf den letzten Datensatz gestellt?


Alle Zeitangaben in WEZ +1. Es ist jetzt 06:11 Uhr.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.