Top-Themen

Aktuelle Themen (A bis Z)

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

Mitglied: ovu-p86

ovu-p86 (Level 1) - Jetzt verbinden

16.06.2011, aktualisiert 16:39 Uhr, 5027 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 ..
Ähnliche Inhalte
Microsoft Office

Access Wochenübersicht für aktuellen Monat

gelöst Frage von Florian86Microsoft Office4 Kommentare

Hallo, wie kann ich ich im Excel eine Abfrage bauen, dass ich von meiner Tabelle alle Wochensummen basierend auf ...

Microsoft Office

Access Abfrage Sotieren

Frage von charmeurMicrosoft Office2 Kommentare

Hallo Ihr da ich blutiger nichts wisser in Access bin wende ich mich an euch ich möchte so eine ...

Microsoft Office

ACCESS - Problem - Bericht - Abfrage

Frage von caschoplMicrosoft Office6 Kommentare

Hallo, ich möchte eine kleine Datenbank für unsere Handy und Mobilverträge erstellen, damit wir diese einfach besser Verwalten können. ...

Batch & Shell

Powershell Access Abfrage auslesen?

gelöst Frage von Druide83Batch & Shell2 Kommentare

Hallo, langsam bin ich am verzweifeln. Ich versuche Werte aus einer Access-Abfrage auszulesen. Selbe Methode funktioniert einwandfrei wenn ich ...

Neue Wissensbeiträge
Windows 7

Windows 7 - Server 2008 R2: Exploit für Total Meltdown verfügbar

Information von kgborn vor 19 StundenWindows 7

Kleine Information für Administratoren, die für die Updates von Windows 7 SP1 und Windows Server 2008 R2 SP1 verantwortlich ...

Sicherheit

Zero Day-Schwachstelle im Internet Explorer - wird von APT bereits ausgenutzt

Information von kgborn vor 1 TagSicherheit

Im Kernel des Internet Explorer scheint es eine Zero Day-Lücke zu geben, die von staatlichen Akteuren (APT) im Rahmen ...

Microsoft
Folder Security Viewer-Lizenzen zu gewinnen
Information von kgborn vor 1 TagMicrosoft

Ich nehme das Thema mal in Absprache mit Frank hier auf, da es für den einen oder anderen Administrator ...

Hardware

Feueralarm killt Festplatten in Rechenzentrum - führt zu größerem Ausfall

Information von kgborn vor 1 TagHardware12 Kommentare

Noch ein kleiner Beitrag für Administratoren, die in Rechenzentren aktiv sind - so als Fingerzeig. Denn es gibt Szenarien, ...

Heiß diskutierte Inhalte
Linux
Linux Server oder Windows Server - lohnt eine Umstellung auf Linux und ebenso basierende SW bei einer langfristigen Planung?
Frage von motus5Linux27 Kommentare

Wir brauchen bei uns einen neuen Server. Dieser wird als Fileserver, Domäne Controller sowie Exchange Server verwendet. Wir versuchen ...

C und C++
Frage1 C Programmierung-Makefile Frage2 PHP-Programmierung HTTP-Fehler 404
Frage von KatalinaC und C++24 Kommentare

Hallo, ich habe 2 Fragen, die nichts miteinander zu tun haben aber mit denen ich mich gerade beschäftige: 1. ...

LAN, WAN, Wireless
Watchguard T15 VPN Einrichtung
gelöst Frage von thomasjayLAN, WAN, Wireless22 Kommentare

Hallo zusammen, wir möchten gerne über unsere Watchguard T15 einen VPN-Tunnel (Mobile VPN with IPSec) einrichten! Als Client nutzen ...

DSL, VDSL
ISP Wechsel auf Vodefone Koax, Gebäudeverkabelung nur per Cat 7
gelöst Frage von wusa88DSL, VDSL18 Kommentare

Hallo Zusammen, ich bin momentan bei Mnet als Glasfaser Kunde und möchte Preis/Leistungs-Technisch zu Kabel Deutschland / Vodafone wechseln. ...