✔ Access / SQL Query: Umsätze von zwei Jahren separat anzeigen

[WiZdooM]

Hallo

Ich soll "mal eben schnell" eine Abfrage zusammenstricken, die mir alle aktiven Händler gibt von 01.01.2008 bis heute. Neben Händlerdaten sollen noch das letzte Verkaufsdatum (FzProvDat), den Umsatz separat für 2008 und 2009 (FzGarPreis als Summe), die Nettosumme, die Stückzahl, die Summe offener Schadenszahlungen und die Summe offener Rechnungszahlungen (2008+2009 addiert) gelistet werden.

Bisher habe ich folgendes Query, welches mir zumindest schonmal Händlerdaten, Umsatz 2008, Nettosumme, Stückzahl ausgibt:

SELECT tbHaendler.mkHdNr, tbHaendler.HdFirma1, tbHaendler.HdFirma2, tbHaendler.HdStrasse, tbHaendler.HdPlz, tbHaendler.HdOrt, Max(tbFahrzeug.FzProvDat) AS FzProvDat, Sum(tbFahrzeug.FzGarPreis) AS [Summe von FzGarPreis], Sum([FzGarPreis]/(1+[FzMwSt]/100)) AS Netto, Count(tbFahrzeug.FzRechDat) AS Stueck, tbMitarbeiter.MaNachname
FROM tbMitarbeiter INNER JOIN (tbHaendler LEFT JOIN tbFahrzeug ON tbHaendler.mkHdNr = tbFahrzeug.fkHdNr) ON tbMitarbeiter.mkMaNr = tbHaendler.fkMaNr
WHERE (((tbFahrzeug.FzRechDat) Is Not Null) AND ((tbFahrzeug.FzRechStat)=No) AND ((tbHaendler.mkHdNr)<>"0" And (tbHaendler.mkHdNr)<>"00") AND ((tbFahrzeug.FzProvDat)>#1/1/2008# And (tbFahrzeug.FzProvDat)<#1/1/2009#))
GROUP BY tbHaendler.mkHdNr, tbHaendler.HdFirma1, tbHaendler.HdFirma2, tbHaendler.HdStrasse, tbHaendler.HdPlz, tbHaendler.HdOrt, tbMitarbeiter.MaNachname;

Ich begrenze die Umsätze moment mit

((tbFahrzeug.FzProvDat)>#1/1/2008# And (tbFahrzeug.FzProvDat)<#1/1/2009#))

in der WHERE-Klausel
Ist es überhaupt möglich, die Umsätze 2008 und die Umsätze 2009 getrennt im Query zu berechnen und auszugeben?

Offene Schäden und offene Rechnungen weiß ich auch nicht wie ich das mit abfrage...

 

[Alex F.]

ja ist es eigentlich musst du nur in deiner Group by bedingung mit Year (deindatum) groupieren


Grüsse bb

 

[WiZdooM]

Hallo

Ich hatte es gestern auch mal mit Union versucht, nur ist Access so doof und wirft alles in eine Spalte. Damit kam ich also nicht weiter.
Ich habe eben mal geschaut, wie es mit
GROUP BY tbFahrzeug.FzProvDat
HAVING (((tbFahrzeug.FzProvDat)=Year(2008)) AND ((tbFahrzeug.FzProvDat)=Year(2009)));
aussieht aber es werden damit 0 Zeilen zurückgeliefert...

 

[jeipack]

Hi
Ja sowas ist möglich. Würde es aber direkt im SQL Mode machen und nicht zusammen klicken

Gehen wir von dieser einfachen Tabelle aus:
mytab
--------
id
anzahl
datum

select year(m.datum), sum(m08.anzahl), sum(m09.anzahl) from mytab as m
left join mytab as m08 where m.id=m08.id and year(m08.datum)=2008
left join mytab as m09 where m.id=m09.id and year(m09.datum)=2009
where year(m.datum) between 2008 and 2009
group by year(m.datum)

Hab leider keine Zeit mehr, hoffe stimmt so und du vestehst wie ich es meine.


Gruss & viel Glück beim eben mal schnell ne Abfrage erstellen (Kommt mir sehr bekannt vor )

 

[WiZdooM]

Ich glaube ich weiß, was du meinst Jeipack. Ich werd mal mit deinem Denkanstoß rumbasteln.

Edit:

Ich hab jetzt etwas herumgebastelt:
1. Where-Klausel im Join ist nicht zulässig. Es muss ON heißen.
2. Habe ich das mal genommen und verschachtelt, aber beim zweiten JOIN akzeptiert mir Access nicht das ON

SELECT tb0809.fkHdNr, Max(tb0809.FzProvDat), Sum(tb08.FzGarPreis), Sum(tb09.FzGarPreis)
FROM tbFahrzeug AS tb0809
LEFT JOIN (tbFahrzeug AS tb08
LEFT JOIN (tbFahrzeug AS tb09 ON tb0809.fkHdNr = tb09.fkHdNr WHERE YEAR(tb09.FzProvDat)=”2009”) ON tb0809.fkHdNr = tb08.fkHrNr WHERE YEAR(tb08.FzProvDat)=”2008”) WHERE YEAR(tb0809.FzProvDat) BETWEEN “2008” AND “2009” GROUP BY tb0809.fkHdNr;

 

[Biber2]

Moin WiZdooM,

ich würde es so versuchen:

SELECT year(fzProvdat) as Jahr,fkHdnr, Max(FzProvDat) as maxFzProvDat, Sum(FzGarPreis) as SumFzGarPreis
FROM tbFahrzeug
WHERE YEAR(FzProvDat) BETWEEN 2008 AND 2009
GROUP BY year(fzProvdat), fkhdnr
order by 1, 2;

Wenn Du die Daten 2008/2009 nebeneinander stehen haben willst:

SELECT fkHdnr, 
Max(FzProvDat2008) as maxFzProvDat2008, 
 Max(FzProvDat2009) as maxFzProvDat2009, 
Sum(FzGarPreis2008) as SumFzGarPreis2008, 
Sum(FzGarPreis2009) as SumFzGarPreis2009
FROM (
Select fzProvdat as fzProvdat2008, null as FzProvDat2009,  fkHdnr, FzgarPreis as FzGarPreis2008, 0 as FzGarPreis2009 from  tbFahrzeug WHERE YEAR(FzProvDat) =2008 
UNION ALL
Select null, fzProvdat, fkHdnr, 0,  FzgarPreis from  tbFahrzeug WHERE YEAR(FzProvDat) =2009 
)
GROUP BY  fkhdnr
order by 1, 2;

Grüße
Biber

 

[jeipack]

HI

WHERE anstatt ON.. wie gesagt, ich war im Stress.

Hmm aber trotzdem hast du mehrere WHEREs in deine Abfrage genommen.. *?*

Ich versuchs mal mit deinen Tabellen:

select tb0809.fkHdNr, Max(tb0809.FzProvDat), Sum(tb08.FzGarPreis), Sum(tb09.FzGarPreis) 
FROM tbFahrzeug as tb0809
LEFT JOIN tbFahrzeug AS tb08 ON tb08.fkHdNr=tb0809.fkHdNr AND YEAR(tb08.FzProvDat)="2008"
LEFT JOIn tbFahrzeug AS tb09 ON tb09.fkHdNr=tb0809.fkHdNr AND YEAR(tb09.FzProvDat)="2009"
WHERE YEAR(tb0809.FzProvDat) BETWEEN “2008” AND “2009” 
GROUP BY tb0809.fkHdNr;

Bei Access kommt ja immer noch dazu dass es manchmal [Klammern] um die Tabellen/Feldnamen will..

Ahja noch was zu deiner Abfrage. Du gruppierst anhand von fkHdNr. Ist das nicht die eindeutige ID der Tabelle? Weil wenn ja bringt es nicht gerade viel MAX und SUM zu verwenden.

Gruss

 

[WiZdooM]

Hey

Die where's brauche ich ja schon um meine Bedingungen zu beschreiben, die erfüllt sein müssen.

fkHdNr ist ein Fremdschlüssel in der Tabelle tbFahrzeug (Nummer des Händlers der das Fahrzeug verkauft hat) und meine Verbindung zur Händlertabelle.

Ich schaue mir die beiden Lösungsvorschläge im Laufe des Vormittags mal genauer an.

Edit:
*staunt einen Bauklotz* Danke dir Biber2. Hatte mein Bekannter mit seinem Union doch nicht so unrecht. Aber dass es im Nachhinein so "einfach" ist, hätte ich jetzt nicht gedacht *schäm*

@Jeipack : Ich bin mir nicht sicher ob ein AND in einer JOIN-Klausel erlaubt ist. Funktionieren tuts in Access jedenfalls nicht so wirklich.

 

[jeipack]

@Jeipack : Ich bin mir nicht sicher ob ein AND in einer JOIN-Klausel erlaubt ist. Funktionieren tuts in Access jedenfalls nicht so wirklich.

Doch klar. Hingegen mehrere WHEREs in einer Abfrage (Subselects nicht mitgezählt) hingegen nicht.

Die AbfrageLOGIK so wie ich sie geschrieben haben funktioniert eigentlich ganz gut, habe ich schon öffters benutzt. Aber hast ja jetzt die UNION Abfrage, genau so schlau

Gruss

 

[WiZdooM]

Von der Logik her, müssten alle meine Abfragen funktionieren Klemmt immer nur etwas an der Syntax...

In diesem Sinne schönes Wochenende!

Update 03.07.2009

Nachdem nun obriger Lösungsvorschlag etwas Fehlerbehaftet war (es wurden nicht alle Ergebnisse zurückgeliefert, den genauen Fehler konnte ich nicht finden), habe ich mal versucht die Abfrage zu simplifzieren. Das Ergebnis ist von der Logik her identisch, aber vom Verständnis her deutlich leichter, da alles linearer aufgebaut ist und (mir) eher wie ein kleines C-Programm vorkommt.

SELECT tbMitarbeiter.mkMaNr, tbMitarbeiter.MaNachname, tbHaendler.mkHdNr, tbHaendler.HdAnrede, tbHaendler.HdFirma1, tbHaendler.HdFirma2, tbHaendler.HdStrasse, tbHaendler.HdPlz, tbHaendler.HdOrt, tbHaendler.HdTelefon, tbHaendler.HdTelefax, tbHaendler.HdMobil, tbHaendler.eMail, Sum(-(Year([FzProvDat])=2008)) AS Stueck2008, Sum(IIf(Year([FzProvDat])=2008,[FzGarPreis],0)) AS Umsatz2008, Max(IIf(Year([FzProvDat])=2008,[FzProvDat],Null)) AS LetzterUmsatz2008, Sum(-(Year([FzProvDat])=2009)) AS Stueck2009, Sum(IIf(Year([FzProvDat])=2009,[FzGarPreis],0)) AS Umsatz2009, Max(IIf(Year([FzProvDat])=2009,[FzProvDat],Null)) AS LetzterUmsatz2009
FROM tbMitarbeiter RIGHT JOIN (tbHaendler RIGHT JOIN tbFahrzeug ON tbHaendler.mkHdNr = tbFahrzeug.fkHdNr) ON tbMitarbeiter.mkMaNr = tbHaendler.fkMaNr
WHERE (((Year([FzProvDat])) Between 2008 And 2009) AND (Not (tbHaendler.mkHdNr)="0" And Not (tbHaendler.mkHdNr)="00" And Not (tbHaendler.mkHdNr)="000" And Not (tbHaendler.mkHdNr)="0000"))
GROUP BY tbMitarbeiter.mkMaNr, tbMitarbeiter.MaNachname, tbHaendler.mkHdNr, tbHaendler.HdAnrede, tbHaendler.HdFirma1, tbHaendler.HdFirma2, tbHaendler.HdStrasse, tbHaendler.HdPlz, tbHaendler.HdOrt, tbHaendler.HdTelefon, tbHaendler.HdTelefax, tbHaendler.HdMobil, tbHaendler.eMail
ORDER BY tbHaendler.HdPlz;