ovu-p86
Jun 16, 2011, updated at 14:39:29 (UTC)
view6760
view5
0
Goto Top

Access, fehlende Monate in Abfrage ergänzen

GermansolvedQuestionMicrosoft OfficeMicrosoft
Hallo,

möchte in Access 2000 eine Tabelle "tbAuftraege" nach Umsatz und Stk. Aufträge je Monat auswerten.
Vorangegangen ist eine Selektion nach Auftragsart. Aber das ist hier nicht von Bedeutung.

Soweit ok.
SELECT Format([AuftragDatum],"yyyy.mm") AS Ausdr1, Count(tbAuftraege.AuftragNr) AS [Anzahl von AuftragNr], Sum(tbAuftraege.Nettopreis) AS [Summe von Nettopreis], Format([AuftragDatum],"mm.yy") AS Ausdr2  
FROM tbAuftraege
WHERE (((tbAuftraege.AuftragDatum) Between [Von4] And [Bis4]))
GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy")  
ORDER BY Format([AuftragDatum],"yyyy.mm");

Da aber nicht jeden Monat ein Auftrag vorliegt, bekomme ich dann als Auswertung
z.B. folgende Tabelle.
AuftragDatum    Stk.   Umsatz      Mon/Jh

2010.01          2      100,00       01.10
2010.02          1       50,00        02.10
2010.05          4      300,00       05.10
2010.07          2        40,00       07.10
u.s.w
Da ich das später in einem Diagramm auswerten, darstellen will,
möchte ich erreichen, das fehlende Monate ohne Aufträge aufgefüllt werden,
also wie folgendes Beispiel:
AuftragDatum    Stk.   Umsatz      Mon/Jh

2010.01          2      100,00       01.10
2010.02          1        50,00       02.10
2010.03          0          0,00       03.10
2010.04          0          0,00       04.10
2010.05          4      300,00       05.10
2010.06          0          0,00       06.10
2010.07          2        40,00       07.10
u.s.w
(Wie gesagt, die Auffüllung mit leeren Monaten ist notwendig, damit ich später
eine vernünftiges Diagramm über die Zeitachse bekomme)

Wie bekomme ich es mit einer weiteren Abfrage hin das die fehlenden Monate aufgefüllt werden?
Über Hinweise wäre ich dankbar.


Gruß
Uwe
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 168143

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

Printed on: April 25, 2024 at 08:04 o'clock

5 Comments
Latest comment
Goto Top
Member: Biber
Solution Biber Jun 16, 2011 at 16:07:53 (UTC)
Goto Top
Moin ovu-p86,

ein Ansatz wäre so:

select yyyymm, Sum(AnzAuftragNr) as  Stk , Sum(  [SumNetto]) as  Umsatz , mmyy
 from (
SELECT Format([AuftragDatum],"yyyy.mm") AS yyyymm, Count(AuftragNr) AS [AnzAuftragNr],   
        Sum(Nettopreis) AS [SumNetto], Format([AuftragDatum],"mm.yy") AS mmyy  
        FROM Auftrag
       WHERE (((AuftragDatum) Between [Von4] And [Bis4]))
      GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy")  
Union Select "2011.01" , 0 , 0, "01.11" from Auftrag  
Union Select "2011.02" , 0 , 0, "02.11" from Auftrag  
Union Select "2011.03" , 0 , 0, "03.11" from Auftrag  
Union Select "2011.04" , 0 , 0, "04.11" from Auftrag  
Union Select "2011.05" , 0 , 0, "05.11" from Auftrag  
Union Select "2011.06" , 0 , 0, "06.11" from Auftrag  
Union Select "2011.07" , 0 , 0, "07.11" from Auftrag  
Union Select "2011.08" , 0 , 0, "08.11" from Auftrag  
Union Select "2011.09" , 0 , 0, "09.11" from Auftrag  
Union Select "2011.10" , 0 , 0, "10.11" from Auftrag  
Union Select "2011.11" , 0 , 0, "11.11" from Auftrag  
Union Select "2011.12" , 0 , 0, "12.11" from Auftrag  
Union Select "2012.01" , 0 , 0, "01.12" from Auftrag  
Union Select "2012.02" , 0 , 0, "02.12" from Auftrag  

) x
       WHERE (x.yyyymm Between Format([Von4], "YYYY.mm") And Format([Bis4], "yyyy.mm" ))  
Group by x.yyyymm, x.mmyy
ORDER BY 1;

wobei natürlich diese Dummy-Sätze aus der Auftrag-Tabelle, die ich dazugeflanscht habe, sinnvollerweise aus einer vorbereiteten Monatjahr-Tabelle kommen könnten.
Und natürlich auch von Januar 2008 bis Dezember 2020 reichen könnten.

Wollte aber nur schnell mit Copy&Paste das Prinzip zeigen.

Ein Abfeuern dieser Query mit Von4/Bis4-Parametern = 01.03.2011 und 31.07.2011 ergibt bei meinen Spieldaten
yyyymm	Stk	Umsatz	mmyy
2011.03	2	356	03.11
2011.04	0	0	04.11
2011.05	0	0	05.11
2011.06	1	44	06.11
2011.07	0	0	07.11

...wobei ich vorliegende Aufträge nur 2 im März und 1 im Juni habe.

[meine Test-Tabelle heisst Auftrag statt tbAuftraege; Feldnamen sollten passen]

P.S. Ein wenig freundlicher sieht das Ergebnis aus, wenn du die SELECT-Spalten der Query áuf ein normales Niveau reduzierst.
also die erste Zeile z.B änderst auf:
select mmyy as Monat, Sum(AnzAuftragNr) as  Stk , Sum(  [SumNetto]) as  [Nettopreis] 
 from ( ...

Das ergibt:
Monat	Stk	Nettopreis
03.11	2	356
04.11	0	0
05.11	0	0
06.11	1	44
07.11	0	0

Grüße
Biber
Member: ovu-p86
ovu-p86 Jun 16, 2011 at 22:19:02 (UTC)
Goto Top
Hallo Biber,

eigentlich kenne ich mich nicht mit SQL aus, erstelle Abfragen in der Regel nur in der Entwurfansicht.
Dank deiner Anleitung hab ichs hinbekommen.

Der Schlüssel war das "UNION SELECT".

Hab entsprechend deiner Anregung vorher eine Dummy-Tabelle "ntbMonateDummy" mit den
Feldern yyyymm, AnzahlAuftragNr, SumNetto und mmyy in Excel erstellt und in Access importiert.

Die Abfrage lautet jetzt:
SELECT mmyy, Sum(AnzahlAuftragNr) AS Stk, Sum(SumNetto) AS Umsatz
FROM  (
SELECT Format([AuftragDatum],"yyyy.mm") AS yyyymm, Count(tbAuftraege.AuftragNr) AS AnzahlAuftragNr, Sum(tbAuftraege.Nettopreis) AS SumNetto, Format([AuftragDatum],"mm.yy") AS mmyy  
FROM tbAuftraege
WHERE (((tbAuftraege.AuftragDatum) Between [Von4] And [Bis4]))
GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy")  
UNION Select Format(yyyymm,"yyyy.mm"), AnzahlAuftragNr, SumNetto, Format(mmyy,"mm.yy")  
FROM ntbMonateDummy
WHERE (((ntbMonateDummy.yyyymm) Between [Von4] And [Bis4]))
) x
GROUP BY x.yyyymm, x.mmyy
ORDER BY Format(x.yyyymm,"yyyy.mm");

Ergebnis jetzt
mmyy          Stk     Umsatz

01.10          2      100,00
02.10          1       50,00
03.10          0        0,00
04.10          0        0,00
05.10          4      300,00
06.10          0        0,00
07.10          2       40,00
u.s.w

Nichts doppelt, Ergebnisse stimmen. Funktioniert.
Jetzt kann ich ein vernünftiges Diagramm erzeugen und hab auch noch "UNION" gelernt.

Hatte vorher geGoogle´t aber nichts passendes bzw. nur Script-Lösungen gefunden.
Aber das hier ist doch korrekt.

Danke.

Gruß
Uwe
Member: Biber
Solution Biber Jun 17, 2011 at 07:39:50 (UTC)
Goto Top
Moin ovu-p86,

jepp, ich sehe, du hast meine Skizze verstanden.
Aus Gründen der von mir hier im Forum schon viel beschworenen Les- und Wartbarkeit noch zwei, drei Anregungen zur Verfeinerung.

a) du bist jetzt beim Anflanschen der ntbMonateDummy zu dem Teile-Statement
...UNION Select Format(yyyymm,"yyyy.mm"), AnzahlAuftragNr, SumNetto, Format(mmyy,"mm.yy") FROM ntbMonateDummy
gekommen.
Die ntbMonate-Tabelle braucht aber weder fachlich noch technisch die Felder "AnzahlAuftragNr" und "SumNetto", die ja ohnehin nur "numerisch 0" enthalten dürfen.
Schmeiss die Felder raus aus der Tabelle - die sind nur durch ihre Namen und Feldtypen eine potentielle Fehlerquelle bei universeller Verwendung.
Reduziere das Statement auf
UNION Select Format(yyyymm,"yyyy.mm"), 0, 0, Format(mmyy,"mm.yy") FROM ...
--> Einziges Feld in der Monatsdummy-Tabelle ist das Datum.

b) Wenn du schon diese Tabelle universeller verwendbar machen willst/musst für weitere Nutzung, dann nimm noch ein Feld "Zeittyp" mit den möglichen Werten "M" für Monat", "Q" für Quartal" "J" für Jahr etc auf.
Dann kannst du dort alle für Auswertungen benötigten Abstufungen z.B. für Quartalsberichte als Datumswerte anlegen und dann die gewünschten Datumswerte mit einem "WHERE Zeittyp = "Q" holen.

c) in deinem Statement oben ist die letze Zeile überkandidelt.
Statt "..ORDER BY Format(x.yyyymm,"yyyy.mm");" reicht ganz, ganz sicher ein "ORDER BY x.yyyymm" oder ein "ORDER by 1"

Grüße
Biber
Member: ovu-p86
ovu-p86 Jun 19, 2011 at 00:35:33 (UTC)
Goto Top
Hallo Biber,

hab die Anregungen umgesetzt.
Für die, die ein ähnliches Problem haben, hier noch mal als Zusammenfassung:

Problem:
Möchte in Access 2000 eine Tabelle "tbAuftraege" nach Umsatz und Stk. Aufträge je Monat auswerten.
(auszuwertende Felder in der Tabelle sind "AuftragDatum", "AuftragNr", "Nettopreis")
Da nicht jeden Monat ein Umsatz vorliegt ergeben bei reiner Abfrage der Tabelle Lücken. (s. ganz oben, 1. Fragestellung)

Um später ein vernünftiges Diagramm über die Zeitachse zu bekommen, sollen Monate in denen kein Auftrag
vorliegt, mit 0 Stk und 0 Umsatz ergänzt werden.

Lösung:
1. eine Monatstabelle erstellen, hier als "ntbMonateDummy" mit Feldbezeichnung "yyyymm" benannt.
(z.B. in Excel eine Reihe mit Monatsabständen erzeuegen und anschließend als Tabelle in Access importieren)
2. diese Tabelle in der Abfrage durch "Union Select" integrieren. Die fehlenden Monate werden jetzt ergänzt.
(entscheidend ist bei "Union" nur, das hier die gleiche Reihenfolge der Felder und der gleiche Datentyp wie
in den gewählten Feldern der Haupttabelle vorliegt)

Das wars.
(Unten das"WHERE ...." dient nur der Eingrenzung des abzufragenden Zeitraums)

Im Vergleich zu meiner anfänglichen Fragestellung, ganz am Anfang, sieht die Abfrage jetzt so aus:
SELECT mmyy AS Monat, Sum(AnzahlAuftragNr) AS Stk, Sum(SumNetto) AS Umsatz
FROM (
SELECT Format(AuftragDatum,"yyyy.mm") AS yyyymm, Count(tbAuftraege.AuftragNr) AS AnzahlAuftragNr, Sum(tbAuftraege.Nettopreis) AS SumNetto, Format(AuftragDatum,"mm.yy") AS mmyy  
FROM tbAuftraege
GROUP BY Format(AuftragDatum,"yyyy.mm"), Format(AuftragDatum,"mm.yy")  
UNION  Select Format(yyyymm,"yyyy.mm"), 0, 0, Format(yyyymm,"mm.yy")  
FROM ntbMonateDummy
) AS x
WHERE (x.yyyymm Between Format([Von4], "yyyy.mm") And Format([Bis4], "yyyy.mm" ))   
GROUP BY x.yyyymm, x.mmyy
ORDER BY x.yyyymm;

Ergebnis:
Monat         Stk     Umsatz 

01.10          2      100,00 
02.10          1       50,00 
03.10          0        0,00 
04.10          0        0,00 
05.10          4      300,00 
06.10          0        0,00 
07.10          2       40,00 
u.s.w
die fehlenden Monate sind jetzt aufgefüllt.

Dank noch mal, hat mir richtig weitergeholfen.
Sympathisch auch, daß es eine "Abfrage" und keine "Script"-Lösung ist.

Gruß
Uwe
Member: Biber
Biber Jun 19, 2011 at 01:14:25 (UTC)
Goto Top
Moin Uwe,

besten Dank. face-wink

An manchen Tagen finde ich es in diesem Forum richtig gut - heute ist das dein Verdienst.

Schönen Sonntag und Grüße aus Bremen
Biber
GermansolvedQuestionMicrosoft OfficeMicrosoft
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments