Ich habe eine Abfrage erstellt welche mit gewisse Umsatzstände nach Quartal und Produkt Anzeigt. Also das Ergebnis ist so:
Quartal Produkt Umsatz Vertragseingang
2015-1 Produkt 1 10000 Alles
Nun möchte ich aber diese Abfrage um den Vorjahresumsatz ergänzen welcher zum gleichen Zeitpunkt wie heute vor einem Jahr dort stand.
Quartal Produkt Umsatz Vertragseingang VorjahresQuartal Vertragseingang VJ-Umsatz
2015-1 Produkt 1 10000 Alles 2014-1 <=05.01.14 5000
2015-1 Produkt 2 15000 Alles 2014-1 <=05.01.14 7000
2015-2 Produkt 1 7000 Alles 2014-2 <=05.01.14 3000
Die Spalte Umsatz beinhaltet also die Summe aller vorliegenden Verträge bezogen auf Quartal und Produkt. Die Spalte VJ-Umsatz soll die Summe der Verträge beinhalten, welche bis heute vor einem Jahr eingegangen sind, bezogen auf das Produkt und das Quartal des Vorjahres.
Nur kurz zur Erklärung: Meine Produkte (es sind Magazine) erscheinen 4 mal jährlich neu, daher die Quartalsaufteilung.
In der abgefragten Tabelle befinden sich alle Informationen. Also Quartal, Produkt, Umsatz, Vertragseingang. Das Vorjahresquartal ist anhand des Quartals zu berechnen und der VJ-Umsatz als Summe aller Verträge beschränkt auf den Vertragseingang <=heute vor einem Jahr. Alle Umsätze sind als Summe notwendig.
Ziel ist einen aktuellen Umsatzvergleich zum Vorjahr zu haben. Also wie stellt sich mein aktueller Umsatz pro Quartal pro Produkt im Vergleich zum Vorjahr Stand heute vor einem Jahr dar.
Das ganze möchte ich dann fein in einen Bericht integrieren, Gruppiert nach Quartal und dann nach Produkt.
Also
Quartal Produkt aktueller Umsatz Vorjahresumsatz
2015-1
Produkt 1 10000 5000
Produkt 2 15000 7000
Produkt 3 usw usw
Quartalssumme Vorquartalssumme
(Wie) Ist dies zu realisieren?
Hallo,
nach meiner Meinung ist das Datenmodell grundlegend falsch.
Die Umsätze sind in einer Tabelle zu führen. Und zwar jeweils ein Datensatz je Quartal und Produkt.
Für den Bericht kannst Du dann eine Kreuztabellenabfrage verwenden, die auch gleich summiert.
Wie ist denn die jetzige Tabelle mit den Umsätzen genau aufgebaut?
Danke für Deine Antwort. Die Datensätze sind genau so erfasst.
Also Tabelle:
ID Vertragseingang Produkt Quartal(Ausgabe) Umsatz
1 10.12.14 1 2015-3 405,5
2 15.03.14 1 2014-3 809,5
3 25.04.14 2 2014-4 1009
usw.
Vielleicht ist auch der Begriff Quartal irreführend... Besser kann man auch Ausgabe oder Version dafür verwenden....
Über eine Kreuztabelle ist der Vertragseingang aber pro Ausgabe nicht variabel definierbar. Es sollen ja für die Vorjahresangaben nur Verträge in die Umsatzsumme einbezogen werden, welche bis genau heute vor nem Jahr vorlagen.
Zeig doch bitte mal den SQL-Code deiner bisherigen Abfrage, evtl. erweitert um "Spalte2" (s. anderen Thread), dann haben wir eine gemeinsame Grundlage.
Bisher ist es nur eine ganz normale Abfrage nach den aktuellen Werten da ich keine Ahnung davon habe, wie ich das mit den Vorjahreswerten hinbekommen kann.
SELECT Magazine.Magazintitel, AngebotAuftrag_Inhalte.Ausgabe, Sum(AngebotAuftrag_Inhalte.Angebotspreis) AS SummevonAngebotspreis
FROM ((AngebotAuftrag_Inhalte INNER JOIN Magazine ON AngebotAuftrag_Inhalte.ID_Magazin = Magazine.ID) INNER JOIN AngebotAuftrag ON AngebotAuftrag_Inhalte.AngebotAuftrag_ID = AngebotAuftrag.ID_AngebotAuftrag) INNER JOIN Vertrieb ON AngebotAuftrag.Vertrieb_ID = Vertrieb.ID_Vertrieb
WHERE (((AngebotAuftrag.Art)="Auftrag") AND ((AngebotAuftrag_Inhalte.ID_Verlag)=1) AND ((AngebotAuftrag.Art)="Auftrag") AND ((Vertrieb.ID_Vertrieb)=GetLoginID()))
GROUP BY Magazine.Magazintitel, AngebotAuftrag_Inhalte.Ausgabe;
Die Abfrage hat leider keinerlei Ähnlichkeit mit dem was du bisher beschrieben hast... :(
Es sollte schon wenigstens die Auswahl des Zeitraums bzw. ein Datenfeld das diesen Zeitraum (-> Quartal) enthält darin vorkommen. Und bislang ging es um Umsätze und nicht um Angebotspreise.
BTW:
Das Kriterium ((AngebotAuftrag.Art)="Auftrag") ist doppelt.
Freilich ist meine Datenbank und deren Namen komplizierter, hier ist es aber ab und an besser, die Dinge einfacher zu benennen.
Die Spalte Vertragseingang wurde eingefügt. Angebotspreis = Umsatz
SELECT Magazine.Magazintitel, AngebotAuftrag_Inhalte.Ausgabe, Sum(AngebotAuftrag_Inhalte.Angebotspreis) AS SummevonAngebotspreis, AngebotAuftrag.Vertragseingang
FROM ((AngebotAuftrag_Inhalte INNER JOIN Magazine ON AngebotAuftrag_Inhalte.ID_Magazin = Magazine.ID) INNER JOIN AngebotAuftrag ON AngebotAuftrag_Inhalte.AngebotAuftrag_ID = AngebotAuftrag.ID_AngebotAuftrag) INNER JOIN Vertrieb ON AngebotAuftrag.Vertrieb_ID = Vertrieb.ID_Vertrieb
WHERE (((AngebotAuftrag.Art)="Auftrag") AND ((AngebotAuftrag_Inhalte.ID_Verlag)=1) AND ((Vertrieb.ID_Vertrieb)=GetLoginID()))
GROUP BY Magazine.Magazintitel, AngebotAuftrag_Inhalte.Ausgabe, AngebotAuftrag.Vertragseingang;
ZitatFreilich ist meine Datenbank und deren Namen komplizierter, hier ist es aber ab und an besser, die Dinge einfacher zu benennen.
Für wen ist das besser? Wozu ist das besser?
Bei einer Gebärenden ist die Geschichte mit dem Storch dann auch nicht sonderlich hilfreich.
Generell: Einen Vorjahreswert erhält man, wenn man beim Datum(!) ein Jahr zurückgeht:
DateAdd("yyyy", -1, Datumswert)Sowie: Eine halbswegs vernünftige Lösung wird man eher nicht gewinnen, wenn man auf eine Abfrage mit drei INNER JOIN's etwas aufpfropft, sondern eher, indem man innerhalb der Abfrage Tabellen anders verarbeitet und verknüpft. Dazu wären dann Halbwissen und Unwissen zu realen Tabellen und deren Beziehungen nicht hilfreich.
@ebs17: Klasse der Vergleich mit der Gebärenden ;D
Egal wie... manchmal weiß ich einfach nicht wie ich anfangen soll die Dinge schlüssig zu erklären. Daher habe ich nun mal eine Beispieldatei angehangen. Der dortige Bericht beinhaltet das Feld Vorjahresquartal.
In der einfachen Variante sollte dort z.B. im Bereich "2015" in der Zeile des Quartals "2015-1" hinter der 3134 der Betrag des Vorjahresquartals stehen... also hier am Beispiel konkret 1390.
In einer erweiterten Variante sollen bei "Betrag" alle Werte ohne Einschränkung berücksichtigt werden, in der Spalte "Vorjahresvergleich" aber nur Werte mit Vertragsdatum "<= Heute vor einem Jahr" um zu sehen wie der Vertragsstand genau vor einem Jahr aussah.
Freilich kann ich dafür auch immer seperate Berichte erstellen, es wäre aber schön, wenn man das so übersichtlich abbilden könnte. Wenn ich z.B. eine Auswertung meines Steuerberaters erhalte so sehe ich auch meinen aktuellen Umsatz z.B. 01.01.2014 bis 31.05.2014 und in einer Spalte dahinter der Umsatz des Vorjahreszeitraumes also z.B. 01.01.2013 bis 31.05.2013. Damit hat man einen direkten Vergleich der Zahlen und diese Art der Lösung strebe ich für mein Access Projekt auch an.
Ggf. ist es nötig, die Quartalsangabe durch eine zusätliche Spalte mit fixer Datumsangabe zu ergänzen... also 2014-1 = 01.01.2014, 2014-2 = 01.04.2014, 2014-3 = 01.07.2014 usw. um so ggf. rechnerisch die Quartale besser abbilden zu können....
Beste Grüße
ZitatVielleicht ist auch der Begriff Quartal irreführend... Besser kann man auch Ausgabe oder Version dafür verwenden....
In der "erklärenden" DB: Ist da Quartal eine Ausgabe, die da mal 150, dann mal 380 oder 750 oder 110 kostet? Oder sind das Zahlungseingänge in dem Zeitraum?
2015-4 mit drei Beträgen: Was bedeutet das? Schon verkauft und verbucht?
Bei einer Tabelle in meiner Vorstellung könnten monatliche Umsätze inkl. Vorjahres-Monatsumsätze so ermittelt werden:
PARAMETERS
Starttag DATE,
Endetag DATE
;
SELECT
AJ.ProduktID,
AJ.Monat,
AJ.Umsatzsumme AS Umsatz_AJ,
VJ.Umsatzsumme AS Umsatz_VJ
FROM
(
SELECT
ProduktID,
Year(Umsatztag) * 100 + Month(Umsatztag) AS Monat,
SUM(Umsatz) AS Umsatzsumme
FROM
TabelleU
WHERE
Umsatztag BETWEEN [Starttag] AND [Endetag]
GROUP BY
ProduktID,
Year(Umsatztag) * 100 + Month(Umsatztag)
) AS AJ
INNER JOIN
(
SELECT
ProduktID,
Year(DATEADD("yyyy", - 1, Umsatztag)) * 100 + Month(DATEADD("yyyy", - 1, Umsatztag)) AS Monat,
SUM(Umsatz) AS Umsatzsumme
FROM
TabelleU
WHERE
Umsatztag BETWEEN DATEADD("yyyy", - 1, [Starttag])
AND
DATEADD("yyyy", - 1, [Endetag])
GROUP BY
ProduktID,
Year(Umsatztag) * 100 + Month(Umsatztag)
) AS VJ
ON AJ.ProduktID = VJ.ProduktID
AND
AJ Monat = VJ.Monat + 100
@ebs17:
kannst Du mal die von dir bearbeitete Datei hier einstellen damit ich mir das mal genauer anschauen kann?`
Man kann das Quartal vielleicht wirklich ähnlich eines Produktes sehen. 2015-4 mit drei Beträgen bedeutet dass bereits jetzt drei Buchungen für dieses Quartal vorliegen.
Ich kann die "bearbeitete" Datei nicht einstellen, weil sie nicht existiert.
Ich habe die Abfrage live geschrieben, mir dabei eine Tabelle vorgestellt. Deren Struktur (unvollständig) lässt sich dann wiederum aus der Abfrage ablesen:
ProduktID ... Long (Fremdschlüssel)
Umsatztag ... Date
Umsatz ... Currency
Langweilige Routinearbeiten wie Tabellen nach Vorlage erstellen und mit sinnvollen Probedaten füllen überlasse ich gern anderen.
Ok. Ich habe es hinbekommen. Und zwar mit einem zusätzlichen Feld in der Abfrage "AusgabeVJ" welche die Vorjahresausgabe via
Val([Ausgabe])-1 & "-" & Mid([Ausgabe],6) AS AusgabeVJ
ermittelt. Danke hierfür an MzKlMu. ::)
Im Bericht selbst habe ich dann die DomSumme - Funktion benutzt. Neues Textfeld im Berichtskopf (versteckt) mit Vorjahresdatum "TxtDatVJ"
=DatAdd("jjjj";-1;Datum())
Und in den Detailbereich ein weiteres neues Textfeld mit Steuerinhalt =
=DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe=" & '[AusgabeVJ]' & " AND Magazinname=" & '[Magazintitel]' & " And Vertrieb_ID=" & GetLoginID() & " AND Vertragseingang <= TxtDatVJ")
Weiterhin war eine neue Abfrage nötig da in der Abfrage welche dem Bericht zugrunde liegt Felder berechnet werden und diese Abfrage sich nicht mit dem DomSummen Befehl nutzen ließ.
Jedenfalls habe ich nun soweit das was ich wollte. Bis auf eins.
Das Textfeld in dem nun die Werte stehen nennt sich im Moment noch "Text60".. Das ist eigentlich egal.. Ich würde aber gern nun die Summe von "Text60" im Berichtsfuß bilden. Also habe ich ein neues Feld dort eingefügt mit Steuerelementinhalt
=summe([Text60])
Leider passiert da gar nix. Keine Fehlermeldung, kein Wert, nix.. Das Feld bleibt leer. Woran könnte das liegen? Ggf. dass die Berechnung der Text60 Felder noch dauert und das Summenfeld einfach noch keine Summe hat???
Hallo,
ich denke, du musst die Gesamtsumme ebenfalls mit Hilfe der Domänenfunktion ermitteln. Berechnungen auf Basis von berechneten Feldern unterstützt Access nicht.
Ich verstehe allerdings nicht, warum du die Zwischensummen nicht bereits in der Abfrage berechnest.
ZitatIch verstehe allerdings nicht, warum du die Zwischensummen nicht bereits in der Abfrage berechnest.
Weil ich nicht weiß wie...
Hallo,
du kannst die DSum-Funktion auch in einer Abfrage einsetzen. Die Gesamtsumme kannst du dann im Bericht mit der Summe-Funktion ermitteln.
Dann brauche ich nochmal Deine Hilfe....
Wenn ich die DomSum in eine Abfrage packe, wie muss dieser Befehl dann aufgebaut werden? Leider enden meine Versuche nicht erfolgreich:
DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe=" & Str(Wert([Ausgabe])-1 & "-" & Teil([Ausgabe];6)) & " AND Magazinname=" & Str([Magazinname])) AS UmsatzVJ
Vieleicht kannst Du da nochmal drüber schauen ::)
Hi,
"Ausgabe" ist offensichtlich ein String, also musst du den Wert in Hochkommata setzen.
Und dann vermischst du SQL mit dem Abfrageentwurf, im Entwurf vergibt man einen Aliasnamen indem man das Wort mit Doppelpunkt vor den Ausdruck setzt.
Versuche es mal hiermit:UmsatzVJ: DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe='" & Wert([Ausgabe])-1 & "-" & Teil([Ausgabe];6) & "' AND Magazinname='" & [Magazinname] & "'")
Diese blöden Hochkommas... >:( Ob ich es je lerne???
Besten Dank MaggieMay.
Warum ist die Hochkommasetzung in der Abfrage anders als im Formular? Im Formular genügte folgendes:
=DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe=" & '[AusgabeVJ]'& " AND Magazinname=" & '[Magazintitel]' & " And Vertrieb_ID=" & GetLoginID() & " AND Vertragseingang <= TxtDatVJ")
Warum ist das so?
Bitte schau nochmal drauf. Ein weiteres Kriterium ist ja der Vertragseingang... Aber hier erhalte ich gar keinen Wert
Hier der aktuelle Code:
DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe='" & Wert([Ausgabe])-1 & "-" & Teil([Ausgabe];6) & "' AND Magazinname='" & [Magazinname] & "' And Vertragseingang="<DatAdd("jjjj";-1;Datum()))
Woran liegt das? Auch alle anderen Versuche scheiterten, diesen Datumswert als Kriterium erfassen zu können.... Bitte hilfe.
Hallo,
dass ein Datum in einer Where-Condition, die als Teil eines SQL-Statements (Abfrage) aufzufassen ist, im USA- oder ISO-Format vorliegen muss, ist bestimmt schon mehr als 10000 Mal erläutert worden... :'(
..And Vertragseingang<= Format(DateAdd('yyyy',-1,Date()),'\#yyyy-mm-dd\#') " )
Hallo,
wieso musst du noch zusätzlich das Datum abfragen, du hast doch das Jahr bereits in der Spalte "Ausgabe" drin?
Lieber DF6GL: Besten Dank für Deine Antwort.. Habe es noch nicht getestet, aber das wird wohl die Lösung sein. ABER: Es wird sicher noch 10000 mal nachgefragt, da wir hier keine Profis sind und dieses VBA, SQL Ding für zig Leute hier einfach totales Neuland ist. Wäre es so einfach zu verstehen, würde es nicht ständig zu solchen Nachfragen kommen. Ich habe mir diesen Käse nicht ausgedacht. Allein diese Hochkommas..... :o Wenn Du bei mir arbeiten würdest, würdest Du sicher nach ein paar Monaten der Einarbeitung auch noch Dinge Fragen, die ich Dir schon hundert mal erklärt habe. ;) So ist es halt. Manchmal findet man gar keine Worte um eine vernünftige Suche im Forum oder bei Google zu starten weil man einfach nicht weiß, wie man die Dinge benennen soll. Also ich bitte weiterhin um Verständis und einen langen Atem mit Leuten wir mir. :'( :'( :'(
@MaggieMay: Das Vertragseingangsdatum muss gesondert mit aufgeführt werden da es ein bestimmtes Datum ist und nicht nur einfach ein Jahr.
:o :o :o
Immer noch #Fehler.... Was ist falsch???
DSum("Angebotspreis","AbfrageDOMSUM","Ausgabe='" & Val([Ausgabe])-1 & "-" & Mid([Ausgabe],6) & "' AND Magazinname='" & [Magazinname] & "' AND Vertragseingang<=Format(DateAdd('yyyy',-1,Date()),'\#yyyy-mm-dd\#')") AS UmsatzVJ
Zum Verzweifeln....
Ich fragte danach, warum du es mit in die Kriterien nehmen musst, das sehe ich irgendwie nicht, wo das Jahr doch bereits über die Spalte "Ausgabe" abgefragt wird.
Wenn ich die Ausgabe von z.B. 2015-3 auf 2014-3 setze habe ich zwar genau die Vorjahresausgabe, ich weiß dann aber immer noch nicht, wie der Vertragsstand in dieser genau ein Jahr vorher ausgesehen hat. Von daher sollen dann auch nur Verträge berücksichtigt werden die bis genau heute vor einem Jahr auch eingegangen sind. Daher das zusätzliche Kriterium nach dem Vertragseingang. Die Ausgaben musst Du eher wie ein Produkt sehen. Ich möchte wissen wie die Umsatzstände in den einzelnen Vorjahresquartalen genau heute vor einem Jahr aussahen.
Hallo,
zeig doch bitte auch mal den Code der Abfrage "AbfrageDOMSUM".
Und der oben gezeigte Code ist ein Ausschnitt aus der SQL-Sicht?
Magazinname ist wie vermutet ein Text?
Nachtrag:
Versuche es mal hiermit:DSum("Angebotspreis","AbfrageDOMSUM","Ausgabe='" & Val([Ausgabe])-1 & "-" & Mid([Ausgabe],6) & "' AND Magazinname='" & [Magazinname] & "' AND Vertragseingang<=" & Format(DateAdd("yyyy",-1,Date()),"\#yyyy-mm-dd\#")) AS UmsatzVJ
Ich habs endlich hinbekommen: 8) ;D 8)
SQL:
DomSumme("Angebotspreis";"AbfrageDOMSUM";"Ausgabe='" & Wert([Ausgabe])-1 & "-" & Teil([Ausgabe];6) & "'" & " And Magazinname='" & [Magazintitel] & "'" & " And Ausgabe='" & Wert([Ausgabe])-1 & "-" & Teil([Ausgabe];6) & "'" & " And Vertragseingang< format(dateadd('yyyy',-1,date()),'yyyy-mm-dd')") AS UmsatzVJ
Hallo,
OT
Zitat"würdest Du sicher nach ein paar Monaten der Einarbeitung auch noch Dinge Fragen, die ich Dir schon hundert mal erklärt habe."
mhmm, da habe ich Zweifel... 8) ;) :)
:) Das lustige daran ist, dass der Ausgangspunkt dieses Lösungsansatzes (also die DomSumme nicht im Berichtsfeld sondern bereits in der Abfrage zu bilden) darin lag, im Bericht die Summe der Felder bilden zu können.... Dies klappt trotzdem nicht! Gebe ich z.B. im Kopfbereich als Steuerelementinhalt eines Textfeldes dann "=Summe([Textfeldname])" ein, so fordert er in einem Fenster gleich einen Parameter für [Textfeldname] obwohl das Textfeld im Detailbereich vorhanden ist. Gebe ich als Steuerelementinhalt des Kopfbereichtextfeldes ein "=Summe([Abfragespalte])" rechnet er sich tot und kommt zu gar keinem brauchbaren Ergebnis mehr...
War alles umsonst?
@datekk:
Die Wiederholung des Kriteriums bzgl "Ausgabe" ist überflüssig.
..ups.. ja das stimmt MaggieMay... Hast Du noch ein Tip wie man das mit den Summen im Bericht hin bekommt? Bin schon nach dieser Anleitung vorgegangen, jedoch ohne Ergebnis: http://www.ms-office-forum.net/forum/showthread.php?t=85570 (http://www.ms-office-forum.net/forum/showthread.php?t=85570)
Wäre schön, wenn man sich den Bericht mal ansehen könnte, um zu sehen was passiert.
Oftmals fällt man mit der (Standard-) Einstellung "Bereich zusammenhalten" auf die Nase. Das kannst du mal probeweise überall auf "Nein" stellen.
Ich habe mich dazu entschlossen, die Domsumme wieder im Bericht zu bilden.