Aug 29, 2017

2801

Access 2013: Summe in Monatsraten aufsplitten und in Tabelle ausgeben

Hallo und guten Morgen,

ich habe eine Frage zu Access.

Ich habe eine Tabelle mit folgenden Werten

ID, Titel, Beginn, Ende, Summe, etc.
1, CoTab 1, 10.10.2016, 10.10.2019, 56.343,10€
2, CoTab 2, 10.10.2014, 10.10.2012, 46.343,10€
etc.

nun möchte ich jeweils eine Abfrage erstellen, die mit folgende Werte liefert:

1. Umsatz nach Monat
2. Umsatz nach Jahren

Kann mir jemand da einen Denkansatz verpassen? Danke!

Please also mark the comments that contributed to the solution of the article

Content-Key: 347587

Url: https://administrator.de/contentid/347587

Printed on: April 27, 2024 at 02:04 o'clock

18 Comments

Latest comment

Moin,

kannst Du über einen Bericht mit Gruppierung erreichen.
Oder willst Du da was eigenes entwickeln?

Gruss

Ja, schon.. Aber dafür muss ich doch erst mal die Monate zwischen den Datumswerten berechnen, und dann für jeden Monat einen eigenen Datensatz in eine Tabelle schreiben. Daran hapert es aber schon

Ach so.
Beginn und Ende liegen wirklich so weit auseinander, wie in Deinem Beispiel?
Grundsätzlich würde ich nämlich den Datumstring auseinandernehmen und bspw. Teilstrings basteln und die abfragen.

Hilft das?

Ja, sind Projekte mit einer langen Laufzeit. Ich brauche auf jeden Fall eine Aufteilung nach Monaten.

Ok, immer noch den Datumstring untersuchen und danach gruppieren, kannste auch in einer Abfrage unterbringen.
Aber wie willst Du den Monatsumsatz errechnen, Summe / Anzahl Monate?

Im Prinzip müsste ich irgendwie für den Zeitraum die Anzahl der Monate berechnen, durch die Summe teilen und dann für jeden Monat des Zeitraums einen eigenen Datensatz in eine extra Tabelle oder Abfrage schreiben. Die könnte ich dann ja gruppieren oder wie in Excel in einer Pivot-Tabelle zusammenfassen.. Aber halt in Access..

Access kann ebenfalls Pivot Tabellen erstellen.
Wenn es Dir in Excel leichert fällt, dann würde ich nur den Datumsstring umbauen, dann die Tabelle in Excel exportieren und dann dort weiter bearbeiten.

In Excel hab ich es schon umgesetzt, aber Excel ist jeztz keine Option. Muss in Access gemacht werden!

Hi

also die anzahl Monate sollte mit DATEDIFF(month,Beginn,Ende) zu bekommen sein. Aber darf ich fragen was der Rest für einen Sinn macht?
In deinem Beispiel hättest du dann 36 Zeilen mit Monat1-36 und jeweils den gleichen Wert dahinter? Ich sehe da gerade keinen Sinn? Du errechnest ja nur aus dem Gesamtbetrag den Bruchteil der Monate aus. Der Betrag ist also jeden Monat gleich.

Ja, das ist alles kein Problem ich kann das alles ausrechnen und hab ich auch schon gemacht.

Der Sinn ist, dass ich am Ende Datensätze bzw. eine Liste benötige, in der der Monatsumsatz aller Projekte steht.
Seit beginn des ersten Projekts bis zum Ende der Laufzeit des letzten Projekts. Eine Summierung aller Projekte. Nicht die einzelne Monatsrate.

09/2015 = 20.000€
10//2015 = 16.435€
..
..
03/2019 = 4.000€

Wenn du einen besseren Weg kennst, her damit

.. deshalb fragich ja ;)

Und der Betrag ist auch nicht jeden Monat gleich.. Nur bei diesem Fall :D

Moin Seastorm,

Zitat von @SeaStorm:

Du errechnest ja nur aus dem Gesamtbetrag den Bruchteil der Monate aus. Der Betrag ist also jeden Monat gleich.

Nein, ist er nicht.

Aus den eigentlichen Daten
ID, Titel, Beginn, Ende, Summe, etc.
1, CoTab 1, 10.10.2016, 10.10.2019, 56.343,10€
2, CoTab 2, 10.10.2014, 10.10.2012, 46.343,10€

.. ergibt sich ja, wie du auch geschrieben hast, ruckzuck über DateDiff() die Anzahl in Tagen je Datensatz.
Daraus kannst du, wenn es insgesamt 1001 Tage sind und du die Gesamtkosten kennst-> prima die "Kosten pro Tag" ermitteln.
Ein Februar hat aber weniger Tage als ein Dezember, also sind doch bei 28 (29) Tagen im Monat die Kosten pro Monat kleiner als bei 30/31 Tagen.
Und wenn z.b die Laufzeit erst ab 10.10.2019 beginnt, dann sind die Kosten im Oktober natürlich auch nur die von 31 Oktobertagen abzgl. der ersten 10 Tage.

Aber - gute nachricht - evolution braucht nicht ganz viele neue Tabellen, es reicht eine neue Tabelle "Monate" mit Feld "Monat" und 12 Datensätzen, eine Tabelle "Jahre" mit Feld "Jahr" und den Jahren 2010....2030.
Den Rest können wir in der Abfrage zusammenbasteln inklusive der "Anzahl Tage des aktuellen Monats" mit einem CROSS JOIN.

Grüße
Biber

ja OK wenn das die Anforderung wäre, könnte man diesen Mini Unterschied da auch rausfitzeln. Aber das ist ja offenbar gar nicht nötig für den TO

OK. Jetzt habe ich verstanden was benötigt wird.
Ich hab hier kein Access installiert, kann dir also nicht sagen ob das so in Access auch umsetzbar ist, aber in SQL würde ich die Anzahl Monate zwischen Zwei Datumsangaben so auflisten:

declare @start DATE = '10.10.2016'  
declare @end DATE = '10.10.2019'  

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date) < @end
)
select   date
from months

guck mal ob das in Access machbar ist. Wenn das geht, ist der Rest ja relativ leicht zu machen . Schnitt pro Monat ausrechnen(oder gleich als Wert an den Datensatz anfügen, falls sich da nichts mehr ändert. Falls doch, funktionieren ComputedColumns in Access?), und dann die Summe der Schnitte von allen Datensätzen des Monats.

Am einfachsten wäre es wenn ihr die Daten in einen SQL Server kippt ;) Dann muss man sich nicht immer mit Access-Spezial-Müll rumärgern

Na ja,

ich sehe keinen Bedarf für mehr als reines SQL und keinen Bedarf für mehr als die paar Access-Möglichkeiten, wenn diese Hilfstabellen "Monate" und "Jahre" angelegt sind.

Natürlich wäre es einfacher, wenn CTEs (die WITH-Syntax) unterstützt werden würden, aber Hey! Das wird auch in einem Access 2055 noch nicht da sein.

Dennoch: alles, was berechnet werden muss (Laufzeit in Tagen, Kosten pro Tag, Anzahl Tage je Monat) bekommen wir mit normaler SQL-Syntax hin.

Und der TO wollte ja nur einen Denkanstoss für die Strategie.

So ein doofer CROSS JOIN von Monate x Jahre sollte nun nicht das Problem sein.

SELECT m.monat, j.jahr
FROM monate m, jahre j
ORDER BY j.jahr, m.monat

Da noch diese Cotabs-Tabelle dranflanschen mit Bedingung
...WHERE (j.jahr >year(cotabs.beginn) AND j.JAHR < year(cotabs.ende))
OR ( (j.jahr =year(cotabs.beginn) and m.monat>=MONTH(cotabs.beginn)
OR (( (j.jahr =year(cotabs.ende) and m.monat<=MONTH(cotabs.ende)

...und dann noch die paar Kennzahlen berechnen und ein GROUP BY auf j.jahr, m.monat für die Monatssummen über mehrere Projekte.

Grüße
Biber

[Edit] gerade erst den Kommentar von evolution von 14:36 entdeckt.
Dann also bei meinem Kommentar "Kosten" durch "Umsatz" ersetzen.
Nichtsdestotrotz: die Strategie mit Hilfstabellen würde ich trotzdem fahren.
[/Edit]

Danke, das war der Anstoß, den ich brauchte. Kam erst jetzt dazu mich weiter damit zu beschäftigen.

Es funktioniert auch bis auf die Gruppierung,
Ich bekomme immer die Meldung, das der angegebene Ausdruck nicht Teil der Agregatfunktion ist. Damit kann ich nichts anfangen

SELECT 
sys_hobj_jahre.jahr, sys_hobj_monate.monat, sys_coTab.beginn, sys_coTab.ende, sys_coTab.massnahmekosten, sys_coTab.zusatz, sys_coTab.massnahmesnummer, DateDiff("m",[sys_kunden].[beginn],[sys_kunden].[ende]) AS monate, funcRunden([sys_coTab].[massnahmekosten]/[Monate],2) AS monatsrate, IIf([sys_kunden].[beginn]=[sys_coTab].[beginn],funcRunden([sys_coTab].[massnahmekosten]/[Monate],2),"Ungleich" & DateDiff("m",[sys_coTab].[beginn],[sys_coTab].[ende])) AS test  
FROM sys_hobj_jahre, sys_hobj_monate, sys_coTab INNER JOIN sys_kunden ON sys_coTab.id = sys_kunden.kurs
WHERE 
(((sys_hobj_jahre.jahr)>Year([sys_kunden].[beginn]) And (sys_hobj_jahre.jahr)<Year([sys_kunden].[ende]))) OR (((sys_hobj_jahre.jahr)=Year([sys_kunden].[beginn])) AND ((sys_hobj_monate.monat)>=Month([sys_kunden].[beginn]))) OR (((sys_hobj_jahre.jahr)=Year([sys_kunden].[ende])) AND ((sys_hobj_monate.monat)<=Month([sys_kunden].[ende])))
ORDER BY 
sys_hobj_jahre.jahr, sys_hobj_monate.monat;

SELECT 
sys_hobj_jahre.jahr, sys_hobj_monate.monat, sys_coTab.beginn, sys_coTab.ende, sys_coTab.massnahmekosten, sys_coTab.zusatz, sys_coTab.massnahmesnummer
FROM sys_hobj_jahre, sys_hobj_monate, sys_coTab INNER JOIN sys_kunden ON sys_coTab.id = sys_kunden.kurs
GROUP BY 
sys_hobj_jahre.jahr, sys_hobj_monate.monat, sys_coTab.beginn, sys_coTab.ende, sys_coTab.massnahmekosten, sys_coTab.zusatz, sys_coTab.massnahmesnummer
HAVING 
(((sys_hobj_jahre.jahr)>Year([sys_kunden].[beginn]) And (sys_hobj_jahre.jahr)<Year([sys_kunden].[ende]))) OR (((sys_hobj_jahre.jahr)=Year([sys_kunden].[beginn])) AND ((sys_hobj_monate.monat)>=Month([sys_kunden].[beginn]))) OR (((sys_hobj_jahre.jahr)=Year([sys_kunden].[ende])) AND ((sys_hobj_monate.monat)<=Month([sys_kunden].[ende])))
ORDER BY 
sys_hobj_jahre.jahr, sys_hobj_monate.monat;

Moin evolution,

hmm... das erste Statement/erste Abfrage kann ich in etwa nachvollziehen.

Da wird etwas plausibles angezeigt?
Wenn ja, dann speicher diese Abfrage unter einem Namen, den du wiederfindest (Qry_coTabDetails oder so).

So, wenn wir dann ein
SELECT * from Qry_coTabDetails
machen... was willst du da GROUP BYen , was summieren oder zählen?

Das verstehe ich nicht an der Abfrage #2...
Okay, die fehlermeldung kann ich auch nicht nachvollziehen, aber ich würde auch nie einen GROUP BY über alle Felder machen und einfach die WHERE-Klausel eins zu eins zu einer HAVIG-Klausel umwidmen.

Bestimmt steht irgendwo in der Doku, was denn alles bei HAVING nicht verwendet werden darf.
Aber einfacher wäre es vermutlich (falls Abfrage #1 funktionierenden sollte), den Details-Resultset zu nehmen und darauf rumzuaggregieren.

Grüße
Biber

Moin evolution,

ähmm, hatte ich mich zu kompliziert ausgedrückt?
Oder ist die Frage für dich erledigt?

Grüße
Biber

Ja, danke.. Die Frage ist erledigt. Hab es hinbekommen.
Hatte vergessen Bescheid zu geben

German solved Question Databases Development