Hallo allerseits!
Ich bastle gerade an einer Kostenübersicht.
In einer tab_Ein_Aus gibt es die Felder Datum, Buchung, Kategorie, V-Zweck , Betrag. Im Feld Buchung steht der Wert 1 für Eingang oder 2 für Ausgang.
Jetzt möchte ich eine Kostenübersicht (Formular und Bericht) erstellen, in der nach Kategorie getrennt jeden Monat aufgelistet ist, wie hoch Die Ausgaben bzw. Einnahmen für die jeweilige Kategorie sind (also eine Summe monatlich für jede Katagorie).
Meine Idee war, folgende Abrage:
SELECT tab_Ein_Aus.Buchungsart, tab_Ein_Aus.Kategorie AS Kat_Haus, Sum(tab_Ein_Aus.Betrag) AS Summe_Betrag, Month([Datum]) AS Monat, Year([Datum]) AS Jahr
FROM tab_Ein_Aus
GROUP BY tab_Ein_Aus.Buchungsart, tab_Ein_Aus.Kategorie, Month([Datum]), Year([Datum])
HAVING (((tab_Ein_Aus.Buchungsart)=2) AND ((tab_Ein_Aus.Kategorie)="Haus"));
und ein EndlosFormular als Unterformular mit einem Feld Summe[Betrag].
Es würde mir somit angezeigt die Ausgaben für jeden Monat in Summe.
So und jetzt mein Problem. Die Monate in denen es keine Ausgaben für die jeweilige Kategorie gibt, werden auch nicht angezeigt.
Ich möchte aber, das in solchen Monaten der Wert 0,00 angezeigt wird.
Ich könnte jetzt für die Monate einen Datensatz anlegen mit Ausgaben =0,00. Ist aber aufwendig (für viele Kategorien) und eigentlich auch nicht der Sinn von Datensätzen...
Gibt es da noch ne andere Möglichkeit? Wäre schön, wenn mir jemand helfen könnte...
Danke... Rico
Mit einer Kalendertabelle kannst Du Dir über eine Abfrage fehlende Datumswerte hereinholen. Nebenbei kannst Du damit auch die Gruppierung nach Monat+Jahr performancetechnisch aufrüsten:
Grundlagen - SQL ist leicht (3) - Kalendertabelle (http://www.ms-office-forum.net/forum/showthread.php?t=298670)
MfGA
ebs
Danke erstmal...
Ich habs mir mal angeschaut, aber da brauch ich bissel Beistand...
Muss ich die Kalendertabelle in meine DB reinkopieren? Muss ich noch irgendwelche Daten eingeben? Und wo muss der Code genau hin, und welcher...
LG Rico
Hi Rico,
der einfachste Weg ist es, die Kalendertabelle in Deine db zu kopieren.
Im zweiten Schritte erstellst Du eine Abfrage aus dieser Kalendertabelle und Deinen zuvor genannten Tabellen.
Verknüpft sind diese per left-join (ist der zweite Punkt bei den Verknüpfungseigenschaften).
Harald
Die Kalendertabelle ist ja leer...
Dort gebe ich dann selbst beliebige Daten ein? In meinem Fall würde pro Monat ein Tag (Bsp. der erste) reichen?
Sehe ich das richtig?..
Moin,
die Kalendertabelle braucht eine Spalte für das Jahr und eine für den Monat.
Harald
ZitatDie Kalendertabelle ist ja leer...
In der Demo: Na klar. Wozu soll man einen Forums-Downloadbereich mit Massendaten vollstopfen, die man sich denken bzw. erzeugen kann? In der Demo gibt es aber Code (FillCalendarTable), der jahresweise Daten für die Tabelle erzeugt. Ersatzweise könntest Du auch die Daten in Excel erzeugen (durch Herunterziehen bzw. Unten ausfüllen) und dann in die Kalendertabelle importieren.
PARAMETERS
[:Buchungsart] SMALLINT,
[:Kategorie] TEXT,
[:ZeitraumStart] DATE,
[:ZeitraumEnde] DATE
;
SELECT
KT.kMonat,
KT.kJahr,
KT.Buchungsart,
KT.Kategorie,
Sum(IIF(EA.Betrag Is Null, 0, EA.Betrag)) AS Summe_Betrag
FROM
(
SELECT
K.kTag,
K.kMonat,
K.kJahr,
T.Buchungsart,
T.Kategorie
FROM
(
SELECT
kTag,
kMonat,
kJahr
FROM
tblH_Kalender
WHERE
kTag Between [:ZeitraumStart] AND [:ZeitraumEnde]
) AS K,
(
SELECT DISTINCT
Buchungsart,
Kategorie
FROM
tab_Ein_Aus
WHERE
Buchungsart = [:Buchungsart]
AND
Kategorie = [:Kategorie]
) AS T) AS KT
LEFT JOIN
(
SELECT
Datum,
Buchungsart,
Kategorie,
Betrag
FROM
tab_Ein_Aus
WHERE
Buchungsart = [:Buchungsart]
AND
T.Kategorie = [:Kategorie]
AND
Datum Between [:ZeitraumStart] AND [:ZeitraumEnde]
) AS EA
ON KT.kTag = EA.Datum
AND
KT.Buchungsart = EA.Buchungsart
AND
KT.Kategorie = EA.Kategorie
GROUP BY
KT.kMonat,
KT.kJahr,
KT.Buchungsart,
KT.Kategorie
;Das wäre jetzt eine Anwendung Deiner Abfrage mit der Kalendertabelle. Da Du Gruppen verwendest (Buchungsart+Kategorie), muss für jede Gruppe eine Kalendertabelle erzeugt werden. Das erfolgt über ein Kreuzprodukt aus Deiner Tabelle mit der Kalendertabelle (Unterabfrage linke Seite vom LEFT JOIN).
Da durch ein solches Kreuzprodukt sehr schnell sehr große Datenmengen entstehen und dieser Effekt abgemildert werden soll, werden die Ausgangstabellen frühzeitig gefiltert.
MfGA
ebs
Danke erstmal für die Hilfe...
Das dauert bissel bei mir - werd mal probieren ob ich es hinkrieg...
Also ich verstehe hier Bahnhof... Sorry...
Habs aber ausprobiert mit der Abfrage.
Es werden dann folgende Parameter nachgefragt:
:Buchungsart
:Kategorie
:ZeitraumStart
:Zeitraum Ende
T.Kategorie
Beim Zeitraum habe ich dann 01.01.13 - 31.12.13 eingegeben
Das Ergebnis ist genau das was ich wollte, jeder Monat einzeln für die festgelegte Kategorie...
Nur wollte ich Zeitraum festlegen über Hauptformular und Unterformular. Im HF das Jahr auswählen... Also der Zeitraum muß nicht nochmal extra nachgefragt werden.
Als Summe steht aber jeden Monat 0,00. Also irgendwo hängt da was!?..
ZitatParameter nachgefragt: T.Kategorie
Das "T." in der fünfzehnten Zeile von unten ist zu entfernen.
ZitatIm HF das Jahr auswählen...
Aus einem Jahr kann man intern (in der Abfrage) oder auch extern (im Formular nach Auswahl des Jahres) die Zeitraumgrenzen ermitteln:
2012 => 01.01.2012 / 31.12.2012 (DateSerial wäre hier ein Hilfsmittel)
Extern wäre praktischer und flexibler, weil man dann trotzdem beliebige Zeiträume (Kalenderwochengruppen, Quartal, Halbjahr, mehrere Jahre) auswerten kann.
Parameterübergabe aus Formular:
Parameterabfrage per VBA öffnen (http://www.donkarl.com?FAQ6.16)
oder Du streichst die Parameterauflistung und setzst Formularbezüge ein:
[:ZeitraumStart] => Forms!NameFormular!txtStart
ZitatAlso irgendwo hängt da was!?..
Wenn ich Daten hätte, könnte ich mir das anschauen.
MfGA
ebs
Wie kommst du an meine Daten ohne das jeder draufschauen kann?
ok, hab den Fehler gefunden...
jetzt funktioniert die Abfrage.
Ich würde das Formular nur gerne so gestalten:
- ein Hauptformular, in dem ich wie schon erwähnt das Jahr auswähle (hab ich schon...)
- und dann für jede Kategorie (z.B. Haus, KfZ, Altersvorsorge, Versicherung usw...) ein Unterformular einfügen das mit der Jahreszahl verknüpft wird (krieg ich auch hin)
Das heißt ich benötige für jede Kategorie eine Abfrage, in der die Parameter (Buchungsart, Kategorie) schon inculdiert sind.
Der Zeitraum (Jahr) wird übers Formular festgelegt.
Wenn jetzt die Abfrage von "ebs17" da auch funktioniert, dann müssten die Parameterabfragen raus...?!
Danke schonmal für Hilfe...
Rico