bow-bonn
Feb 29, 2008, updated at Mar 31, 2008 (UTC)
view21994
view16
0
Goto Top

Daten nach Kriterien in Access-Abfrage addieren

GermansolvedQuestionMicrosoft OfficeMicrosoft

Ein Klassisches Problem beim Monatsabschluß

Hallo allerseits!

Ich versuche am Ende des Monats aus unseren Geschäftsdaten eine Abfrage zu erstellen, bei der ich direkt sehen kann, welche Kunden, welche Produkte, in welcher Menge bestellt haben.

Leider (Gott sei Dank!!) bestellen viele Kunden nicht nur einmal im Monat ein Produkt, sondern mehrfach das gleiche. Diese Produkte möchte ich jetzt direkt addiert haben.

Ein Beispiel:

Kunde A Produkt 1 Anzahl3
Kunde B Produkt 2 Anzahl2
Kunde A Produkt 1 Anzahl2

Soll werden:

Kunde A Produkt 1 Anzahl5
Kunde B Produkt 2 Anzahl2

Herzlichen Dank


Marc
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 82007

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

Printed on: April 19, 2024 at 15:04 o'clock

16 Comments
Latest comment
Goto Top
Member: misterdemeanor
misterdemeanor Feb 29, 2008 at 11:05:13 (UTC)
Goto Top
Grüß Dich Marc,

da müsste man mehr über die Daten/Tabellenstrukturen wissen:

Üblich hat man dann etwa 3 (hierfür relevante) Tabellen: Kunden, Bestellungen, Bestellungsdetails. Und diese stehen dann entsprechend miteinander in Beziehung.

Aber bevor man ins blaue schießt kannst Du vielleicht nochmal posten wie Ihr das in der DB implementiert habt.

BG, Felix -misterdemeanor-
Member: petenicker
petenicker Feb 29, 2008 at 11:08:29 (UTC)
Goto Top
Hallo,

SELECT <Tabellenname>.<Spalte_Kunde>, <Tabellenname>.<Spalte_Produkt>, Sum(<Tabellenname>.<Spalte_Menge>)
FROM <Tabellenname>
GROUP BY <Tabellenname>.<Spalte_Kunde>, <Tabellenname>.<Spalte_Produkt>;

Das ist eigentlich schon alles, vorausgesetzt dass alle Daten in einer Tabelle stehen (was ich natürlich nicht hoffe).
Member: Bow-Bonn
Bow-Bonn Mar 06, 2008 at 15:44:03 (UTC)
Goto Top
Bis hierher erst einmal:"DANKE",

leider stehen die Daten nicht in einer Tabelle:

Hier der Code:

SELECT IIf([Invoice].[Vorgangsnummer]<>"0","044") AS Entity, Year([InvoiceDetail].[Datum]) AS [Year], Month([InvoiceDetail].[Datum]) AS [Month], Invoice.Artikelnummer AS ProductId, Material.Bezeichnung1 AS ProductName, InvoiceDetail.Kundennummer AS CustomerId, [Customer].[Name1] & " " & [Customer].[Name2] AS CustomerName, Invoice.Gesamtpreis AS Revenue, Invoice.Menge AS [Qty Sold], InvoiceDetail.Datum
FROM ((InvoiceDetail INNER JOIN Invoice ON InvoiceDetail.Nummer = Invoice.Vorgangsnummer) LEFT JOIN Material ON Invoice.Artikelnummer = Material.Artikel) LEFT JOIN Customer ON InvoiceDetail.Kundennummer = Customer.Kundennummer
WHERE (((InvoiceDetail.Datum)>=#1/1/2008# And (InvoiceDetail.Datum)<=#12/31/2008#) AND ((InvoiceDetail.Vorgang)="D" Or (InvoiceDetail.Vorgang)="G" Or (InvoiceDetail.Vorgang)="T" Or (InvoiceDetail.Vorgang)="R") AND ((Invoice.Positionstyp)=1))
GROUP BY InvoiceDetail.Kundennummer, Invoice.Artikelnummer;

Jetzt wirft er mir eine Fehlermeldung aus, daß jeweils das erste Feld "nicht Teil der Aggregatfunktion" sei (???). Mache ich aus dem GROUP ein ORDER, meckert er nicht, addiert aber auch nicht...

Hoffe es gibt noch Hoffnung face-wink

Marc
Member: Biber
Biber Mar 06, 2008 at 18:16:34 (UTC)
Goto Top
Sach ma, Bow-Bonn,

hast Du schon mal ein SQL-Statement aus der Nähe gesehen?
Ist ja nicht schlimm, wenn noch nicht.

Ich rate dringendst davon ab, eines der über den Access-Zusammenklick-Assistent automatisch generierten Skripte für "Zeig mir alles als eine meterlange Einzelsatzliste" zu nehmen und hier einfach ein GROUP BY druntersetzen zu lassen ohne dass Du die handwerklichen Kenntnisse mitbringst und ohne dass wir das Datenmodell kennen.

Kann dumm laufen und unser Statement führt dazu, dass nächsten Monat 20 eurer Kunden eine Inkasso-Mahnung bekommen oder so etwas...

Tipp: stöbere ein bisschen nach GROUP BY und Aggregatfunktionen wie SUM(), FIRST(), MAX().. und dann lass uns evtl vom Datenmodell und von einem lesbaren SQL-Statement ausgehend anfangen...
SELECT IIf([Invoice].[Vorgangsnummer]<>"0","044") AS Entity,   
Year([InvoiceDetail].[Datum]) AS [Year], 
Month([InvoiceDetail].[Datum]) AS [Month], 
Invoice.Artikelnummer AS ProductId, 
Material.Bezeichnung1 AS ProductName, 
InvoiceDetail.Kundennummer AS CustomerId, 
[Customer].[Name1] & " " & [Customer].[Name2] AS CustomerName,   
Invoice.Gesamtpreis AS Revenue, 
Invoice.Menge AS [Qty Sold], 
InvoiceDetail.Datum
FROM ((InvoiceDetail 
    INNER JOIN Invoice ON InvoiceDetail.Nummer = Invoice.Vorgangsnummer) 
        LEFT JOIN Material ON Invoice.Artikelnummer = Material.Artikel) 
           LEFT JOIN Customer ON InvoiceDetail.Kundennummer = Customer.Kundennummer
WHERE ((
  (InvoiceDetail.Datum)>=#1/1/2008# And (InvoiceDetail.Datum)<=#12/31/2008#) 
          AND ((InvoiceDetail.Vorgang)="D"   
                 Or (InvoiceDetail.Vorgang)="G"   
                 Or (InvoiceDetail.Vorgang)="T"   
                 Or (InvoiceDetail.Vorgang)="R")   
          AND ((Invoice.Positionstyp)=1))
GROUP BY InvoiceDetail.Kundennummer, Invoice.Artikelnummer;


Das Dingen da ist für einen Klicki-Bunti-Generator von M$ schon ganz gut, aber hat auch noch Potentiale.

Grüße
Biber

P.S. Wenn Du Monatssummen haben willst, dann wirst Du Details wie "InvoiceDetail.Datum" wohl knicken können.
Welche Felder/Spalten willst Du?
Member: Bow-Bonn
Bow-Bonn Mar 07, 2008 at 16:14:07 (UTC)
Goto Top
Moin Biber,

das ganze ist ein teils zusammengeklickter, teils händisch geschriebener Versuch einer Datenzusammenstellung aus Vorgaben.

Ich habe Listen aus unserem ERP (Sage) und bastele mir danach die Listen, die der Mutterkonzern als Spalten haben will - auch im Format, das man möchte.

Daher kommt es zu so lustigen sachen wie der "044" in Spalte 1 oder der Selektionsbeschränkungen D, T, G, ....

Das ORDER BY -Statement funktioniert soweit (es werden nur die gleichen Artikel in der Periode NICHT addiert) und ist durch händischen Vergleich mit den Rechnungen auch richtig so, nur leider funktioniert es nicht mit einem - irgendwie gearteten - GROUP BY-Statement.

That's all....
Member: Biber
Biber Mar 10, 2008 at 06:53:27 (UTC)
Goto Top
Moin Bow-Bonn,

is' ja Montagmorgen... ich formuliere meine Frage noch mal um:
Welche Felder/Spalten willst Du?

Grüße
Biber
Member: misterdemeanor
misterdemeanor Mar 15, 2008 at 17:09:49 (UTC)
Goto Top
Der gute Mann antwortet weder Montag-Morgens, noch Samstag-Nachmittags.

Also:
  • Entweder ist der Mutterkonzern Pleite gegangen weil einfach essentielle Daten gefehlt haben und keine Rechnungen mehr gestellt werden konnten-->Insolvenz
  • Mutterkonzern wurde übernommen und unrelationale Stellen wurden wegrationalisiert
  • Jemand ist im Urlaub und hat dreist keine Anichtskarte geschickt
  • Jemand meldet sich auf einem Board an um Hilfe zu bekommen schert sich aber selbst einen Dreck um sein eigenes Problem und die Leute die Ihre Zeit investieren um zu helfen
Member: Biber
Biber Mar 18, 2008 at 15:54:33 (UTC)
Goto Top
Tja, misterdemeanor,

diese bislang 4 von Dir aufgeführten möglichen mildernden Umstände lassen mein Moderatorenherz nur für ein paar Zehntelsekunden etwas weicher werden.

Aus meiner Sicht ist ein andiskutiertes Problemchen ohne abschließende Lösung und ohne Antwort auf Nachfragen für dieses Forum so sinnvoll wie ein bildungspolitischer Sprecher der F.D.P. für die Schulpolitik.

Also verschiebe ich den Beitrag mal auf den Kompost.

Grüße und Dank für die Erinnerung
Biber
Member: Bow-Bonn
Bow-Bonn Mar 18, 2008 at 16:57:29 (UTC)
Goto Top
Sorry und Asche über mein Haupt,

ich war einige Tage im Urlaub und ein paar Tage unterwegs und hatte das Thema auf Halt gestellt....

Also: Ich möchte, daß die Summe jedes Artikels pro Kunde gebildet wird!

Mit dem ORDER BY Statement kann ich die Bestellungen nach Kunden sortieren - das klappt auch ganz gut. Hat der aber mehrfach bestellt, wird bei fünf Bestellungen von Artikel XY, fünf mal hintereinander der Artikel aufgeführt:

Kunde A, XY, 2, 2.00
Kunde A, XY, 4, 4.00
...

statt:

Kunde A, XY, 6, 6.00

Nur wenn eich statt eines ORDER BY Statements ein GROUP BY verwende meckert Access.

Grüße

Bow

P.S.: Ich werde jetzt regelmäßiger reinschauen - schwör!
Member: Biber
Biber Mar 18, 2008 at 19:20:08 (UTC)
Goto Top
Na gut, Bow-Bonn,

tasten wir uns mal ran.

Wenn dieses hier Dein SQL-Schnipsel war, der zumindest die Einzelsatz-Meterware auf den Schirm gebracht hat...

SELECT IIf([Invoice].[Vorgangsnummer]<>"0","044") AS Entity,   
Year([InvoiceDetail].[Datum]) AS [Year], 
Month([InvoiceDetail].[Datum]) AS [Month], 
Invoice.Artikelnummer AS ProductId, 
Material.Bezeichnung1 AS ProductName, 
InvoiceDetail.Kundennummer AS CustomerId, 
[Customer].[Name1] & " " & [Customer].[Name2] AS CustomerName,   
Invoice.Gesamtpreis AS Revenue, 
Invoice.Menge AS [Qty Sold], 
InvoiceDetail.Datum
FROM ((InvoiceDetail 
    INNER JOIN Invoice ON InvoiceDetail.Nummer = Invoice.Vorgangsnummer) 
        LEFT JOIN Material ON Invoice.Artikelnummer = Material.Artikel) 
           LEFT JOIN Customer ON InvoiceDetail.Kundennummer = Customer.Kundennummer
WHERE ((
  (InvoiceDetail.Datum)>=#1/1/2008# And (InvoiceDetail.Datum)<=#12/31/2008#) 
          AND ((InvoiceDetail.Vorgang)="D"   
                 Or (InvoiceDetail.Vorgang)="G"   
                 Or (InvoiceDetail.Vorgang)="T"   
                 Or (InvoiceDetail.Vorgang)="R")   
          AND ((Invoice.Positionstyp)=1))
Order BY InvoiceDetail.Kundennummer, Invoice.Artikelnummer;

...und wir mal mangels genauerer Instruktionen Deinerseits unterstellen, dass wir
die Verkaufsinformationen nach Monat/Jahr, Kunde und Artikelnummer zusammenschaufeln sollen, dann...
[diese Invoice-Vorgangsnummer tue ich mal als nicht bilanzrelevant ab..**g]
SELECT 
-- Year([InvoiceDetail].[Datum]) AS [Year], 
Month([InvoiceDetail].[Datum]) AS [Month], 
Invoice.Artikelnummer AS ProductId, 
FIRST(Material.Bezeichnung1) AS ProductName,
InvoiceDetail.Kundennummer AS CustomerId, 
FIRST([Customer].[Name1] & " " & [Customer].[Name2]) AS CustomerName,   

SUM(Invoice.Gesamtpreis) AS Revenue, 
SUM(Invoice.Menge) AS [Qty Sold] 
FROM ((InvoiceDetail 
    INNER JOIN Invoice ON InvoiceDetail.Nummer = Invoice.Vorgangsnummer) 
        LEFT JOIN Material ON Invoice.Artikelnummer = Material.Artikel) 
           LEFT JOIN Customer ON InvoiceDetail.Kundennummer = Customer.Kundennummer
WHERE 
  (InvoiceDetail.Datum >=#1/1/2008# And InvoiceDetail.Datum<=#12/31/2008#) 
          AND  InvoiceDetail.Vorgang IN ("D", "G", "R", "T")   
          AND Invoice.Positionstyp=1
GROUP BY Month([InvoiceDetail].[Datum]), InvoiceDetail.Kundennummer, Invoice.Artikelnummer;

Das wäre jetzt die Variante mit den wenigsten/nur den nötigsten Anpassungen.
Die Spalte YEAR(InvoiceDetail.Datum) habe ich auskommentiert, da diese bei der WHERE-Clause eher albern aussähe.

Natürlich würde ich die JOINs in der Praxis nicht auf der unaggregierten Ebene setzen bzw. dort belassen, sondern mit dem GROUP BY-Resultset verknibbeln.

Aber um die Diskussion erst mal wieder in Schwung zu bringen....

BTW - ich verschiebe den Beitrag mal wieder zurück von Papierkorb nach Access.

Grüße
Biber
Member: Bow-Bonn
Bow-Bonn Mar 19, 2008 at 18:16:01 (UTC)
Goto Top
Hallo Biber,

das sieht ja sehr wild aus; den FIRST-Befehl habe ich noch nie irgendwo gesehen...
Mal sehen ob Access das frisst! Werde das morgen gleich mal testen.
Bis hierher herzlichen Dank

Bow-Bonn
Member: Bow-Bonn
Bow-Bonn Mar 20, 2008 at 14:27:16 (UTC)
Goto Top
Hallo Biber,

mal abgesehen davon, daß sowohl die sowohl die YEAR, als auch die MONTH-Funktion als Ausgabespalte nicht funktioniert (????), ist der Rest tadellos!

Danke!

Nur warum läßt sich Jahr und Monat nicht aus dem Datum "extrahieren"?

Bow
Member: Biber
Biber Mar 20, 2008 at 22:28:55 (UTC)
Goto Top
Moin Bow,

es gibt -selbst bei dem sehr schmalbrüstigen SQL-Sprachumfang von M$-Access - keinen Grund, warum die Year()/Month()-Spalten nicht angezeigt werden sollten.

Es darf innerhalb eines Access-Statements AFAIK keine "auskommentierte" Zeile enthalten sein, d.h. die bei mir oben mit "-- " beginnende Zeile, die auskommentierte Year()-Spalte sollte raus.

Weil Kommentare was für Weicheier sind und deshalb in M$-Access nicht nötig...

Sonst poste noch mal das NICHT funktionierende Statement bitte.

Grüße
Biber
Member: Bow-Bonn
Bow-Bonn Mar 25, 2008 at 11:37:40 (UTC)
Goto Top
Hallo Biber,

hier noch mal das Statement:

SELECT IIf([Invoice].[Vorgangsnummer]<>"0","044") AS Entity, Year([InvoiceDetail].[Datum]) AS [Year], Month([InvoiceDetail].[Datum]) AS [Month], Invoice.Artikelnummer AS ProductId, First(Material.Bezeichnung1) AS ProductName, InvoiceDetail.Kundennummer AS CustomerId, First([Customer].[Name1] & " " & [Customer].[Name2]) AS CustomerName, Sum(Invoice.Gesamtpreis) AS Revenue, Sum(Invoice.Menge) AS [Qty Sold]
FROM ((InvoiceDetail INNER JOIN Invoice ON InvoiceDetail.Nummer = Invoice.Vorgangsnummer) LEFT JOIN Material ON Invoice.Artikelnummer = Material.Artikel) LEFT JOIN Customer ON InvoiceDetail.Kundennummer = Customer.Kundennummer
WHERE (((InvoiceDetail.Datum)>=#1/1/2008# And (InvoiceDetail.Datum)<=#12/31/2008#) AND ((InvoiceDetail.Vorgang) In ("D","G","R","T")) AND ((Invoice.Positionstyp)=1))
GROUP BY Month([InvoiceDetail].[Datum]), Invoice.Artikelnummer, InvoiceDetail.Kundennummer;

Dabei bekomme ich immer folgende Fehlermeldung:

"Sie sollten eine Abfrage ausführen, die den angegebenen Ausdruck '(IIf([Invoice].[Vorgangsnummer]<>"0","044") AS Entity)' nicht als Teil der Aggregatfunktion einschließt." ... was immer das heißt!?

Wenn ich den Ausdruck rausnehme geht das solange weiter, bis auch die folgenden YEAR- und MONTH-Ausdrücke gelöscht wurden, was ja nicht Sinn der Übung ist, denn das Format ist vorgegeben.

Gruß

Bow
Member: Biber
Biber Mar 27, 2008 at 18:27:29 (UTC)
Goto Top
Moin Bow,

sorry für die späte Rückmeldung.

Also - ein bisschen rantasten können wir uns, aber an anderen Stellen verstehe ich Dich nicht.
Fangen wir mit dem Erklärlichen an.
... nicht als Teil der Aggregatfunktion einschließt."
Ich hatte Dich vor einigen Postings gebeten, Dir mal ein Zeilen zum Thema GROUP BY, Aggregatfunktionen wie Sum(), First(), Last() etc durchzulesen.
Wenn Du meinst, dass Erklärungen von mir leichter verständlich sind, fühle ich mich zwar geehrt, aber realistisch gesehen gibt es Sinnvolleres im Netz.

Wenn Du eine Zusammenfassungsklausel wie GROUP BY verwendest, dann stehen in der GROUP BY-Clause alle Felder, die in den betrachteten Datensätzen den gleichen Wert haben.
Alle anderen Felder müssen entweder summiert (Sum()) oder als Durchschnitt (Avg()) oder kleinste/größte/erste/letzte (Min()/Max()/First()/Last()) oder mit sonst einer Aggregatfunktion "zusammengefasst" werden.
Auf diese "IIf( ...Vorgangsnummer )" trifft nicht zu, dass sie im Group-By-Teil erwähnt wird - und es wird aber auch keine Agggatsfunktion angewendet. Genau das besagt auch die Fehlermeldung.

Also entweder dieses IIF(...) einschließen ins GROUP BY oder das Feld (dessen tieferer Sinn mir nach wie vor unklar ist) weglassen.

Unter Beachtung der obigen Regel sollte es dann funktionieren.
Unabhängig davon:
Wenn Du doch diese funktionierene Abfrage (ohne GROUP By) vorliegen hast z.B. als "Abfrage_InvoiceDetails", dann liefert diese Abfrage doch noch außen schon einen View mit den Spalten
  • Entity, Year, Month, ProductId, ProductName, CustomerId, CustomerName, Revenue, [Qty Sold]

Dann mach doch Deine draufgesattelte Abfrage einfach als
SELECT Entity, Year, Month, ProductId, First(ProductName), CustomerId,  First(CustomerName), 
 Sum(Revenue),  Sum([Qty Sold]) From Abfrage_InvoiceDetails
GROUP By Entity, Year, Month, ProductId, CustomerID
ORDER By Year, Month, ProductId, CustomerID, EntityID

Is' doch lesbarer.
Allerdings musst Du bedenken, dass die "Abfrage_InvoiceDetails" momentan auch eine ZEIT-bezogene WHERE-Clause enthält. Diese Einschränkung würde ich rausnehmen und bei Bedarf in Deine Group-By-Abfrage setzen.

Grüße
Biber
Member: Bow-Bonn
Bow-Bonn Mar 31, 2008 at 14:32:21 (UTC)
Goto Top
Moin Biber,

die ersten Zeilen sind eh nur gefüllte Platzhalter, damit der Mutterkonzern den ### in ihr System einspeisen können.

Egal, habe mich jetzt genau so beholfen, indem ich einfach eine weitere Abfrage gemacht habe - nicht sehr elegant, aber es hilft.

Also Danke für die erhellenden Erklärungen und bis dann

Bow
GermansolvedQuestionMicrosoft OfficeMicrosoft
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 CommentsjstrickerWIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 3 CommentsDaniEnd of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment