MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 09.02.2018, 20:00   #1
rainerZZ
MOF Profi
MOF Profi
Standard Acc2010 - 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?
rainerZZ ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 20:25   #2
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 20:48   #3
rainerZZ
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

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?
rainerZZ ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 21:46   #4
fraeser
MOF Meister
MOF Meister
Standard

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....

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"


Geändert von fraeser (09.02.2018 um 21:51 Uhr).
fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 22:37   #5
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (09.02.2018 um 22:40 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 22:55   #6
fraeser
MOF Meister
MOF Meister
Standard

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?

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"

fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 23:13   #7
ebs17
MOF Guru
MOF Guru
Standard

@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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 23:24   #8
fraeser
MOF Meister
MOF Meister
Standard

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

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"

fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.02.2018, 23:34   #9
ebs17
MOF Guru
MOF Guru
Standard

Zitat:

Ich verstehe aber - trotz Deines Links - nicht ...

Probiere es aus mit steigenden DS-Zahlen. Manche Dinge muss man fühlen.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 00:14   #10
rainerZZ
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

@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...
rainerZZ ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 00:22   #11
fraeser
MOF Meister
MOF Meister
Standard

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

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"

fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 07:53   #12
fraeser
MOF Meister
MOF Meister
Standard

@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;

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"

fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 07:59   #13
fraeser
MOF Meister
MOF Meister
Standard

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;

__________________

Gruß, Jan

Zitat:

Programmieren und Microsoftail: "Gestern stand ich noch am Abgrund.....heute bin ich einen Schritt weiter....!"

fraeser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 09:54   #14
rainerZZ
Threadstarter Threadstarter
MOF Profi
MOF Profi
Standard

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.
rainerZZ ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.02.2018, 09:54   #15
Nouba
MOF Guru
MOF Guru
Standard

@Jan,

hast Du innerhalb der halben Sekunde auch einmal den Datensatzzeiger auf den letzten Datensatz gestellt?
Nouba ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


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


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

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

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.