Access, fehlende Monate in Abfrage ergänzen

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

01          2      100,00       01.10
02          1        50,00       02.10
03          0          0,00       03.10
04          0          0,00       04.10
05          4      300,00       05.10
06          0          0,00       06.10
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

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 168143

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

Ausgedruckt am: 21.11.2024 um 13:11 Uhr

5 Kommentare

Neuester Kommentar

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
03	2	356	03.11
04	0	0	04.11
05	0	0	05.11
06	1	44	06.11
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
11	2	356
11	0	0
11	0	0
11	1	44
11	0	0

Grüße
Biber

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

10          2      100,00
10          1       50,00
10          0        0,00
10          0        0,00
10          4      300,00
10          0        0,00
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

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

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 

10          2      100,00 
10          1       50,00 
10          0        0,00 
10          0        0,00 
10          4      300,00 
10          0        0,00 
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