Hallo zusammen,
Dank diesem Forum nimmt mein Haushaltsbuch langsam Gestalt an. Einige Baustellen habe ich noch. ;D
Eine davon ist die Auswertung der gesammelten Daten.
In meiner Tabelle Buchungen, werden die Umsätze in einem Feld mit Vorzeichen gespeichert. (Die Variante mit 2 Feldern für Einnahmen und Ausgaben habe ich auch mal ausprobiert, da hatte ich Schwierigkeiten mit der laufenden Summe, deshalb speichere ich die Beträge in einem Feld)
Nun möchte ich wissen, wie hoch sind die Einnahmen, die Ausgaben, die Differenz und der Durchschnitt pro Monat pro Konto.
Die Einnahmen und Ausgaben pro Monat pro Konto kann ich mir durch eine Kreuztabelle ausgeben lassen. Aber eine Kreuztabelle für Einnahmen und eine für Ausgaben.
1. Muss ich jetzt eine weitere Abfrage erstellen, die beide Kreuztabellen beinhaltet und die Felder Jan bis Dez jeweils addieren oder löst man das anders?
2. Den Durchschnitt kann ich ja auch mit je einer Kreuztabelle errechen oder?
Wie kann man dann diese 4 Kreuztabellen zusammenfügen, um einen Bericht zu erstellen?
Mfg
AbsolutNeu
Ps.: Ich habe mir die 61 Beiträge in der Suche nach Kreuztabellen durchgelesen, aber nichts passendes gefunden. :(
Was würdest Du unter Differenz verstehen?
Ansonsten würde ich eine einfache Auswahlabfrage verwenden. Ansatz:
SELECT
A.Konto,
A.Monat,
A.Alles,
A.Durchschnitt,
B.Ein,
C.Aus
FROM
(
(
SELECT
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag) AS Monat,
SUM(Buchung) AS Alles,
Avg(Buchung) AS Durchschnitt
FROM
tblBuchungen
WHERE
Buchungstag Between DateSerial(Year(Date()), 1, 1)
AND
DateSerial(Year(Date()), 12, 31)
GROUP BY
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag)
) AS A
INNER JOIN
(
SELECT
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag) AS Monat,
SUM(Buchung) AS Ein
FROM
tblBuchungen
WHERE
Buchungstag Between DateSerial(Year(Date()), 1, 1)
AND
DateSerial(Year(Date()), 12, 31)
AND
Buchung > 0
GROUP BY
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag)
) AS B
ON A.Monat = B.Monat
AND
A.Konto = B.Konto
)
INNER JOIN
(
SELECT
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag) AS Monat,
SUM(Buchung) AS Aus
FROM
tblBuchungen
WHERE
Buchungstag Between DateSerial(Year(Date()), 1, 1)
AND
DateSerial(Year(Date()), 12, 31)
AND
Buchung < 0
GROUP BY
Konto,
Year(Buchungstag) * 100 + Month(Buchungstag)
) AS C
ON A.Monat = C.Monat
AND
A.Konto = C.Konto
ORDER BY
A.Konto,
A.Monat
Wenn Du mehrere (Kreuztabellen-) Abfragen verwendest, könntest Du darauf basierend mehrere Berichte erstellen und diese als Unterberichte in einen Gesamtbericht einbinden.
Hallo Eberhard,
vielen Dank für Deine Antwort.
Das ich gut finde: :D
ZitatAnsonsten würde ich eine einfache Auswahlabfrage verwenden
Für mich sieht das nicht gerade einfach aus. Eine Abfrage mit mehreren Unterabfragen. Das ist für mich Neuland und ich muss mich erst an die Syntax gewöhnen.
Ich werde mal versuchen, Deinen Ansatz umzusetzen. Kann etwas dauern! ;D
Ich lasse den Beitrag vorerst noch offen, falls noch Fragen auftauchen.
Unter der Differenz verstehe ich, was übrig bleibt bzw. fehlt, wenn man die Einnahmen und Ausgaben gegenrechnet.
Änderung:
-> Sorry, hab gerade gemerkt, meine Differenz ist einfach die Summe der Buchungen. ::) also A.Alles. Deshalb die Frage nach der Differenz oder?
Mfg
AbsolutNeu
Hallo nochmal,
endlich habe ich die Abfrage umgesetzt. ;D
Da ich in der Buchungstabelle nur eine ID für die Konten(bei mir Depots, die werden in einer extra Tabelle unterschieden, ob Portemonnaie, Spardose oder Konto mit Bankverbindung) habe, musste ich noch eine Tabelle einbinden, um den Namen des Kontos anzeigen zu lassen. die Tabelle habe ich in den From Klauseln der Unterabfragen eingebunden. Alle Felder mit Konto habe ich mit dem neuen Feld des Depotnamen ersetzt.
Außerdem habe ich statt Inner Join Left Join benutzt, damit er mir auch die Datensätze anzeigt, die keine Ausgaben oder Einnahmen haben.
Das gleiche Prinzip kann ich bestimmt für die Auswertung der einzelnen Kategorien nehmen.
Was mir noch einfällt, mein Buchungsdatum erhällt immer nach verlassen des Datumfeldes die aktuelle Uhrzeit, um die Datensätze bei der laufenden Summe, die am gleichen Tag sind, zu unterscheiden. Mit meinem Filter kann ich die Buchungen nach Konto, Kategorie und Zeit filtern. Will ich die Buchungen bis zum 16. anzeigen lassen, dann heißt die Bedingung eigentlich:
Datum <= 16
aber die Buchungen werden nur bis zum 15. angezeigt. Weil das Datum Zeitwerte enthällt. Also immer < nächsten Tag.
Wie verhält sich das bei der Funktion Month()? Wird der Zeitanteil dann mitberücksichtigt oder muss ich die Formeln dann ändern?
Mfg
AbsolutNeu
Month und Year picken sich Monats- und Jahreszahl aus dem DateTime-Wert heraus und dienen der Gruppierung. Zeitanteile haben da keine Auswirkungen.
Im Unterschied dazu eine Filterung. DateTime-Werte sind intern Double-Werte. Dabei stellt in Access die Ganzzahl eine fortlaufende Tageszahl dar, 0 entspricht dem Start der Zählung zum 30.12.1899. Die Zeitanteile werden als Dezimalanteil geführt, eine Stunde = 1/24 usw. Als Test:
?Date() * 1
41783
?Now() * 1
41783,9177546296 Daher muss man wie erkannt als obere (nichtinklusive) Grenze den Folgetag verwenden.
Zum Sinn des Month/Year-Konstrukts gegenüber einer Variante Format(Buchungstag, "mmm yyyy") (Performance) siehe auch SQL ist leicht (3) - Kalendertabelle (http://www.ms-office-forum.net/forum/showthread.php?t=298670).
Zitat... Left Join benutzt, damit er mir auch die Datensätze anzeigt, die keine Ausgaben oder Einnahmen haben
Streng genommen müsste man auf die linke Seite des LEFT JOIN eine zusätzliche Tabelle/Abfrage stellen, die die benötigten Monate
vollständig enthält, denn bei wenigen Buchungen oder auch bei einer entsprechenden Filterung kann leicht der Fall eintreten, dass es für einen Monat gar keine Buchungen gibt.
Hallo Eberhard,
hat ein bisschen gedauert!
Ich habe die Abfrage um eine weitere Abfrage erweitert, mit der Funktion Count(), da konnte ich nachprüfen, dass alle Datensätze berücksichtigt wurden auch die vom 31.. Naja ist eigentlich auch klar, es wird ja nach enthaltenem Monat nicht Tag gefragt ::)
Das Einfügen der Monatstabelle (ich denke nur Januar bis Dezember oder für jedes Jahr?) wird machbar sein.
Ich habe mir auch überlegt, das die Daten nicht vom aktuellem Jahr angezeigt werden, sondern von den letzten 12 Monaten. Also muss ich doch nur die Where Klausel der Abfragen ändern oder?
Ich versuche mich in letzter Zeit immer mehr in die SQL Syntax, vorallem in die Struktur mit Unterabfragen einzuarbeiten. Irgendwie lässt der Aha Effekt noch auf sich warten ;D oder eher :'(
Daher meine letzte Frage:
Prinzip Deiner Abfrage:
Select...
From ((Abfrage A)(Abfrage B))(Abfrage C)
Meine mit Count:
Select...
From (((Abfrage A)(Abfrage B))(Abfrage C))(Abfrage Count)
Könnte ich noch eine Abfrage für Abweichung so anhängen?
Select...
From ((((Abfrage A)(Abfrage B))(Abfrage C))(Abfrage Count))(Abfrage Abweichung)
Die Count Abfrage wird noch gelöscht, es geht nur ums Prinzip!
Und die Monatsabfrage setze ich so?:
Select...
From (Abfrage Monat((((Abfrage A)(Abfrage B))(Abfrage C))(Abfrage Count))(Abfrage Abweichung))
Mfg
AbsolutNeu
Änderung:
Mir ist gerade erst aufgefallen, das die Abfrage die Durchschnittswerte aller Buchungen im Monat berechnet. Wie kann ich denn die Durchschnittswerte der Einnahmen und Ausgaben pro Monat mit Berücksichtigung der Vormonate berechnen?
Also:
Januar Ein. 20 Ausg. 20 EinDurchschnitt 20 Ausg.Durchschnitt 20
Februar Ein.30 Ausg. 10 EinDurchschnitt 25 Ausg.Durchschnitt 15
..
....
Sorry, das ist mir eben erst im Bericht aufgefallen! :-[
ZitatKönnte ich noch eine Abfrage für Abweichung so anhängen?
Prinzipiell ja. Eine SQL-Anweisung darf bis etwa 64.000 Zeichen lang werden. Da hast Du noch ein wenig Luft. Über sonstige Grenzen kannst Du Dich in der Hilfe unter Spezifikationen informieren.
Neben dem, was man machen kann und was funktioniert, könnte man sich auch dafür interessieren, was GUT (performant dann auch bei größeren Datenmengen) funktioniert.
Ich hatte mehrere Unterabfragen verwendet, weil die benutzten Berechnungen nur so mit Indexnutzung (nach erstem Empfinden) darstellbar waren. Der JOIN mit einem berechneten Wert ist dann aber nicht mehr so toll, der Nachteil aber bei jeweils 12 Datensätzen (Monaten) nicht mehr so gravierend.
Man würde aber trotzdem wenn möglich mehrere Berechnungen in einem Zug (in einer Abfrage) erledigen wollen.
ZitatUnd die Monatsabfrage setze ich so?
Auf eine solche grobe Prinzipdarstellung kann man alles antworten. Der Teufel steckt im Detail.
Bezüglich Verschiebung des betrachteten Zeitraums: Ja.
ZitatIch versuche mich in letzter Zeit immer mehr in die SQL Syntax, vorallem in die Struktur mit Unterabfragen einzuarbeiten.
Wenn man sich vor Augen hält, dass der SQL-Interpreter beim Abarbeiten mit dem FROM-Teil beginnt, wird klar, dass man selber auch dort zuerst die Abfrage lesen sollte. Und dort sind die Unterabfragen nur eine andere Darstellung einer Tabelle / gespeicherten Abfrage und könnten, z.B. für eine Teilverdeutlichung, als solche ausgelagert / getestet werden.
ZitatWie kann ich denn die Durchschnittswerte der Einnahmen und Ausgaben pro Monat mit Berücksichtigung der Vormonate berechnen?
Eine Aggregierung über jeweils zwei Monate ist nicht ganz simpel, aber mal ganz interessant. Mal sehen, wie ich zu einer Beispieltabelle komme ...
Hallo Eberhard,
ich habe mir mal die Mühe gemacht, einen Ausschnitt aus meiner Datenbank zu zippen und anzuhängen. Da hättest Du Deine Beispieltabelle ;D (Ist doch so gewollt oder?
Manche Beträge oder Kategorien stimmen irgendwie nicht mehr, aber Dir geht es ja nur um die Tabelle.
Ich werde sehen, das ich mich morgen wieder an dem laufenden Durchschnitt arbeite.
Mfg
AbsolutNeu
P.S:Wenn Du Verbesserungsvorschläge zu meiner Datenbank hast, dann darfst Du Dich auch äußern :)
Hallo,
darf ich auch was sagen zum Datenmodell?
Ich setze es mal voraus. ;D
Das Datenmodell kann stark vereinfacht werden.
Da es zu einer Unterkategorie nur eine Kategorie gibt, ist die Zwischentabelle "tbl_KategorieUnterkategorie" überflüssig. Dass dies zutrifft, erkennt man auch an der gleichen Datensatzzahl (100) der beiden Tabellen. In die Unterkategorietabelle muss nur ein Fremdschlüssel zur Kategorie. In die Transaktionstabelle dann nur ein Fremdschlüssel zur Unterkategorie, da ja wenn die U-Kat festliegt man automatisch auch die dazugehörende Kategorie hat.
Deine ganzen Abfragen, Formulare und Berichte vereinfachen sich dadurch erheblich.
Was willst Du eigentlich mit den Ja/Nein Feldern ..Archivieren ?
Beziehungsbild anbei.
Hallo MzKlMu,
natürlich darfst Du etwas zu meiner Datenbank sagen - auch wenn es für mich mehr Arbeit bedeutet. ;D
Die Verbindung zwischen Kategorie und Unterkategorie stammt noch von einem Vorgänger Modell, bei der ich die Unterkategorie mit Firmen/Kontakte abhängig hatte. Das fand ich dann überflüssig, da man es jetzt ja über eine Combobox in das Feld Notizen schreiben kann. Das ist ja viel flexibler.
Was mir in den letzten Tagen noch durch den Kopf gegangen ist, das ich gerne Sparpläne verwalten möchte. Also ich habe zum Beispiel ein Sparbuch. Dort zahle ich jeden Monat 150€ für Öl ein. Auf diesem Sparbuch zahle ich auch 50€ fürKFZ Versicherung ein. Jetzt möchte ich zum einen das der aktuelle Stand des Sparbuches minus den jeweiligen Sparplan dargestellt wird. Zum anderen möchte ich die Sparpläne in den Buchungen mit Geld versorgen. Eine Umbuchung auf Sparplan. Oder als Depot Sparplan Öl auswählen können. Sozusagen als Unterkonto.
Meine Überlegung ist:
Eine Tabelle Sparpläne eine Zwischentabelle mit Fremdschlüssel von Sparpläne und Fremdschlüssel vom Depot und die ID der Zwischentabelle in die Buchungstabelle statt die Depot ID.
Ist die Lösung richtig? Oder gibt es eine bessere?
Nee, so kann es doch nicht gehen Über die ID der Zwischentabelle weiß man nicht ob es das Depot oder der Sparplan ist: :'(
Ich hoffe einer von Euch kann mich retten?
Nach Deiner Aufbauänderung muss ich die Daten eh neu eingeben, da die Zuordnungen nicht mehr stimmen richtig? oder gibt es noch einen Weg die Daten anders zu sichern?
Mfg
AbsolutNeu
Zusatz:
Deine Frage habe ich erst heute morgen gesehen ???
ZitatWas willst Du eigentlich mit den Ja/Nein Feldern ..Archivieren ?
Da ich die Kategorien nicht löschen kann, ohne sie aus den Buchungen zu löschen, wollte ich sie einfach nicht mehr anzeigen lassen (in der Auswahlbox). Abfrage filtert die ungewollten Kategorien. Ist in dieser Version noch nicht vorhanden!
Zusatz2:
Ich hänge mal ein Bild von den Beziehungen an, wie ich das mit den Sparplänen meine. Nur so kann es nicht funktionieren. Ich bekomme die ID der Sparpläne nicht in die Tabelle der Transaktionen.
Zu Durchschnitt Zahlungen pro Kategorie aus Monat + Vormonat:
SELECT
B.MyMonth,
B.katID,
(
SELECT
Avg(X.traBetrag)
FROM
tbl_Transaktionen AS X
WHERE
X.trakatIDRef = B.katID
AND
X.traDatum Between DateSerial(Year(B.LastDate), Month(B.LastDate) - 1, 1)
AND
B.LastDate
) AS TwoMonthAvg
FROM
(
SELECT
VT.MyMonth,
VT.katID,
Nz(A.MaxDatum, CDate(Format(CStr(VT.MyMonth), "@@@@-@@") & "-01")) AS LastDate
FROM
(
SELECT
tblH_Monate.MyMonth,
tbl_Kategorien.katID
FROM
tblH_Monate,
tbl_Kategorien
) AS VT
LEFT JOIN
(
SELECT
Year(traDatum) * 100 + Month(traDatum) AS MyMonth,
trakatIDRef,
Max(traDatum) AS MaxDatum
FROM
tbl_Transaktionen
GROUP BY
Year(traDatum) * 100 + Month(traDatum),
trakatIDRef
) AS A
ON VT.MyMonth = A.MyMonth
AND
VT.katID = A.trakatIDRef) AS B
Dazu einige Anmerkungen zum Nachvollziehen:
- Ich habe eine Hilfstabelle tblH_Monate eingeführt, die die vollständige Liste der Monate im Format yyyymm (Feld MyDate Long, Primärschlüssel) enthält. Diese dient zum Auffüllen von Lücken.
- Darauf aufbauend: Als Gruppe für die Buchungen habe ich nur die Kategorien verwendet (könnte man ausweiten). Daher ergibt sich dann als VT (vollständige Tabelle) das Kreuzprodukt aus Monaten und Kategorien, die dann (obige Frage zum LEFT JOIN) mit vorhandenen Daten zu verknüpfen wäre.
Hallo Eberhard,
vielleicht solltest Du mal Deine Systemzeit umstellen! ;D Oder sitzt Du wirklich noch so spät am PC und beantwortest meine Fragen??? Dafür schon mal ein großes Danke schön!!!!
Mit dem Hinweis auf ein besseres Datenmodell, ist mir in den Sinn gekommen, meine Datenbank nochmal zu überarbeiten. Den Tipp von MzKlMu werde ich berücksichtigen (keine Zwischentabelle mehr), die Tabelle Transaktionen werde ich in Buchungen umbenennen. (Deine und vieler meiner Abfragen muss ich dann anpassen). Manche Formulare mit ufo_ufo wieder sinnvoll umbennen. Und den Code verbessern, teilweise kann ich Funktionen im Modul erstellen, und diese in verschiedenen Formularen nutzen. Unbrauchbares löschen.
Was mir zur Zeit am wichtigsten ist, wie ich die Sparpläne oder Unterkonten noch in meinem Datenmodell einbinden kann. Wenn ich jetzt die Datenbank überarbeite und die Daten wieder eingebe, dann fange ich vielleicht wieder von vorne an.
Nun zu Deiner Lösung:
Auf den ersten Blick verstehe ich nur Bahnhof!
Auf den zweiten Bick erkenne ich ein System!
Die Zusammenhänge muss ich mir noch verdeutlichen. Was passiert in jeder Unterabfrage und wie sind jeweils die Zusammenhänge.
Ich denke, ich überarbeite meine Datenbank erstmal (schätze 3 Tage) und werde mich dann (oder parallel dazu) mit Deiner Abfrage befassen und integrieren.
Mir ist halt zur Zeit sehr wichtig, ob und wie ich die Sparpläne/Unterkonten mit einbinden kann.
Mfg
AbsolutNeu
Zitaterkenne ich ein System!
Das ist doch mehr wert als bloßes Abkopieren.
Hallo Eberhard,
ich hab gerade 4,5 Stunden den Rasen gemäht, deshalb habe ich Deine Antwort erst gerade gelesen.
Der erste Schritt ist das System zu erkennen.
Der zweite Schritt das System zu verstehen.
Der dritte Schritt wäre das System auf andere Dinge anzuwenden.
Ich bin erst beim 2. Schritt ;D
Könntest Du Dich vielleicht zu meinem Vorhaben äußern, wie ich die Sparpläne/Unterkonten noch in meinem Datenmodell intergrieren kann.
Es soll ein Konto(bei mir noch Depots) mehrere Sparpläne haben können, oder auch keinen. Außerdem sollen die Sparpläne/Unterkonten in den Buchungen/Transaktionen als Konto/Depot auswählbar sein.
Mfg
AbsolutNeu
PS.: Danke das Du die Systemzeit umgestellt hast, ich hatte schon ein schlechtes gewissen! ;D
Hallo,
anbei habe ich ein Bild meines geänderten Datenmodells.
Durch die Zwischentabelle kann jedem Konto ein oder mehrere Sparpläne zugeordnet werden. Das heißt auch, das ich für Konten, die eigentlich kein Sparplan haben, ein Sparplan anlegen muss, um eine Zuordnung zu haben.
Es reicht ein Sparplan z.B. Normal für alle Konten ohne Sparplan.
Die Felder Ausblenden in der Zwischentabelle, der Kategorie u. der Unterkategorie dienen zum Ausblenden in den Auswahlfeldern. Wenn diese gelöscht werden, fehlen die Zuordnungen in den Buchungen richtig?
Könnte jemand meine Theorie bestätigen oder verbessern?
Mfg
AbsolutNeu
Zusatz:
Ich bin gerade dabei meine Formulare anzupassen und bekomme das Formular für die Kategorien nicht hin. Nach dem neuen Modell kann ich die Unterformulare nicht mehr verknüpfen. Es kommt die Fehlermeldung kann Datensatz nicht finden. Ist auch logisch, da eine neu angelegte Kategorie noch nicht in einer Unterkategorie gespeichert ist. Ich müsste also zuerst eine Kategorie anlegen, dann kann ich eine Unterkategorie anlegen und die entsprechende Kategorie auswählen. Das finde ich ein bißchen unpraktisch, die alte Version finde ich besser. Bekommt man denn mit dem neuen Modell die alte Version hin? Oder muss ich das Datenmodell wieder ändern?