Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Access, fehlende Monate in Abfrage ergänzen

Frage Microsoft Microsoft Office

Mitglied: ovu-p86

ovu-p86 (Level 1) - Jetzt verbinden

16.06.2011, aktualisiert 16:39 Uhr, 4661 Aufrufe, 5 Kommentare

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.
01.
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 
02.
FROM tbAuftraege 
03.
WHERE (((tbAuftraege.AuftragDatum) Between [Von4] And [Bis4])) 
04.
GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy") 
05.
ORDER BY Format([AuftragDatum],"yyyy.mm");
Da aber nicht jeden Monat ein Auftrag vorliegt, bekomme ich dann als Auswertung
z.B. folgende Tabelle.
01.
AuftragDatum    Stk.   Umsatz      Mon/Jh 
02.
 
03.
2010.01          2      100,00       01.10 
04.
2010.02          1       50,00        02.10 
05.
2010.05          4      300,00       05.10 
06.
2010.07          2        40,00       07.10 
07.
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:

01.
AuftragDatum    Stk.   Umsatz      Mon/Jh 
02.
 
03.
2010.01          2      100,00       01.10 
04.
2010.02          1        50,00       02.10 
05.
2010.03          0          0,00       03.10 
06.
2010.04          0          0,00       04.10 
07.
2010.05          4      300,00       05.10 
08.
2010.06          0          0,00       06.10 
09.
2010.07          2        40,00       07.10 
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
Mitglied: Biber
16.06.2011 um 18:07 Uhr
Moin ovu-p86,

ein Ansatz wäre so:

01.
select yyyymm, Sum(AnzAuftragNr) as  Stk , Sum(  [SumNetto]) as  Umsatz , mmyy 
02.
 from ( 
03.
SELECT Format([AuftragDatum],"yyyy.mm") AS yyyymm, Count(AuftragNr) AS [AnzAuftragNr],  
04.
        Sum(Nettopreis) AS [SumNetto], Format([AuftragDatum],"mm.yy") AS mmyy 
05.
        FROM Auftrag 
06.
       WHERE (((AuftragDatum) Between [Von4] And [Bis4])) 
07.
      GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy") 
08.
Union Select "2011.01" , 0 , 0, "01.11" from Auftrag 
09.
Union Select "2011.02" , 0 , 0, "02.11" from Auftrag 
10.
Union Select "2011.03" , 0 , 0, "03.11" from Auftrag 
11.
Union Select "2011.04" , 0 , 0, "04.11" from Auftrag 
12.
Union Select "2011.05" , 0 , 0, "05.11" from Auftrag 
13.
Union Select "2011.06" , 0 , 0, "06.11" from Auftrag 
14.
Union Select "2011.07" , 0 , 0, "07.11" from Auftrag 
15.
Union Select "2011.08" , 0 , 0, "08.11" from Auftrag 
16.
Union Select "2011.09" , 0 , 0, "09.11" from Auftrag 
17.
Union Select "2011.10" , 0 , 0, "10.11" from Auftrag 
18.
Union Select "2011.11" , 0 , 0, "11.11" from Auftrag 
19.
Union Select "2011.12" , 0 , 0, "12.11" from Auftrag 
20.
Union Select "2012.01" , 0 , 0, "01.12" from Auftrag 
21.
Union Select "2012.02" , 0 , 0, "02.12" from Auftrag 
22.
 
23.
) x 
24.
       WHERE (x.yyyymm Between Format([Von4], "YYYY.mm") And Format([Bis4], "yyyy.mm" )) 
25.
Group by x.yyyymm, x.mmyy 
26.
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
Bitte warten ..
Mitglied: ovu-p86
17.06.2011 um 00:19 Uhr
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:
01.
SELECT mmyy, Sum(AnzahlAuftragNr) AS Stk, Sum(SumNetto) AS Umsatz 
02.
FROM  ( 
03.
SELECT Format([AuftragDatum],"yyyy.mm") AS yyyymm, Count(tbAuftraege.AuftragNr) AS AnzahlAuftragNr, Sum(tbAuftraege.Nettopreis) AS SumNetto, Format([AuftragDatum],"mm.yy") AS mmyy 
04.
FROM tbAuftraege 
05.
WHERE (((tbAuftraege.AuftragDatum) Between [Von4] And [Bis4])) 
06.
GROUP BY Format([AuftragDatum],"yyyy.mm"), Format([AuftragDatum],"mm.yy") 
07.
UNION Select Format(yyyymm,"yyyy.mm"), AnzahlAuftragNr, SumNetto, Format(mmyy,"mm.yy") 
08.
FROM ntbMonateDummy 
09.
WHERE (((ntbMonateDummy.yyyymm) Between [Von4] And [Bis4])) 
10.
) x 
11.
GROUP BY x.yyyymm, x.mmyy 
12.
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
Bitte warten ..
Mitglied: Biber
17.06.2011 um 09:39 Uhr
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
Bitte warten ..
Mitglied: ovu-p86
19.06.2011 um 02:35 Uhr
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:
01.
SELECT mmyy AS Monat, Sum(AnzahlAuftragNr) AS Stk, Sum(SumNetto) AS Umsatz 
02.
FROM ( 
03.
SELECT Format(AuftragDatum,"yyyy.mm") AS yyyymm, Count(tbAuftraege.AuftragNr) AS AnzahlAuftragNr, Sum(tbAuftraege.Nettopreis) AS SumNetto, Format(AuftragDatum,"mm.yy") AS mmyy 
04.
FROM tbAuftraege 
05.
GROUP BY Format(AuftragDatum,"yyyy.mm"), Format(AuftragDatum,"mm.yy") 
06.
UNION  Select Format(yyyymm,"yyyy.mm"), 0, 0, Format(yyyymm,"mm.yy") 
07.
FROM ntbMonateDummy 
08.
) AS x 
09.
WHERE (x.yyyymm Between Format([Von4], "yyyy.mm") And Format([Bis4], "yyyy.mm" ))  
10.
GROUP BY x.yyyymm, x.mmyy 
11.
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
Bitte warten ..
Mitglied: Biber
19.06.2011 um 03:14 Uhr
Moin Uwe,

besten Dank.

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
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Datenbanken
gelöst Access 2010 komplizierte Abfrage (3)

Frage von Dr.Cornwallis zum Thema Datenbanken ...

Datenbanken
gelöst Access 2010 Abfrage erster Werktag (10)

Frage von Dr.Cornwallis zum Thema Datenbanken ...

Microsoft Office
Access Abfrage Sotieren (2)

Frage von charmeur zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...