Maximum je Auftragsnummer ermitteln

[K a t j a]

Hallo Zusammen,
ich habe gerade ein Brett vorm Kopf und komme nicht weiter. Außerdem bin ich Anfänger in Access und hoffe auf Eure Hilfe.

Ich habe angehängte Tabelle und möchte gern nur die Maxima des Nettoumsatzes aber je Auftragsnummer und Kunde. Also praktische immer den Artikel, für den der Kunde am meisten Geld ausgibt.Jeder Auftrag muss am Ende genau einmal vorkommen mit dem teuersten Artikel.


Jemand eine Idee für mich?

[Knobbi38]

Hallo Katja,

ohne jetzt deine Datei heruntergeladen zu haben, ist übrigens eine Excel Datei, sollte das mit einer Abfrage und der Aggregat-Funktion Max() gehen, wenn du auf die gewünschten Kriterien gruppierst:

Code [Auswählen] Erweitern

SELECT Max(Nettoumsatz) AS MaxvonNettoumsatz, AuftrNr, KDNr
FROM tblTestdaten
GROUP BY AuftrNr, KDNr;

Gruß Knobbi38

[Debus]

Hallo, versuch mal so:

Code [Auswählen] Erweitern


SELECT
    AuftrNR,
    KDNr,
    Max(Nettoumsatz) AS MaxNettoumsatz
FROM
    Umsatz
GROUP BY
    AuftrNR,
    KDNr;

Holger

Sorry da war Ulli schneller

[Bitsqueezer]

Hallo Katja,

also ich würde die Abfrage eher so formulieren:

Code [Auswählen] Erweitern

SELECT U.JahrMonat
    ,U.KW
    ,U.AuftrNr
    ,U.KDNr
    ,U.ArtNr
    ,U.Nettoumsatz
FROM Umsatz AS U
INNER JOIN (
    SELECT U1.AuftrNR
        ,U1.KDNr
        ,Max(U1.Nettoumsatz) AS MaxNettoumsatz
    FROM Umsatz AS U1
    GROUP BY U1.AuftrNR
        ,U1.KDNr
    ) AS qMNA ON (U.Nettoumsatz = qMNA.MaxNettoumsatz)
    AND (U.AuftrNr = qMNA.AuftrNR)
    AND (U.KDNr = qMNA.KDNr)
ORDER BY U.JahrMonat
    ,U.KW
    ,U.KDNr;
Denn Du möchtest ja wissen, welche Auftragsnummer sich hinter dem höchsten Umsatz verbirgt.

Das Problem bei gruppierten Abfragen ist, daß Du Details weglassen mußt. Denn wenn alle Details eingeschlossen sind, funktioniert keine Aggregierung mehr, hier MAX.

Bei den oben gezeigten Abfragen findest Du zwar das Maximum heraus und erhältst außerdem Auftrags- und Kundennummer, mußt dafür aber die Artikelnummer weglassen. Denn würdest Du diese mit einschließen, erhieltest Du das Maximum auch noch aufgeteilt je Artikelnummer. Und das ist in Deinen Beispielen in jeder Zeile eine andere. Im Ergebnis hättest Du dann die Ausgangstabelle.

Man sieht dann oft, daß dann einfach "MIN(Artikelnr)" verwendet wird im Glauben, man erhielte dann die passende Artikelnummer. Das ist aber falsch, denn jede Aggregatfunktion arbeitet für sich, Du würdest dann im Rahmen der Gruppierung die kleinste Artikelnummer erhalten, die kann (wie hier bei nur je zwei Zeilen zu gruppieren) zufällig die sein, die zum Netto-Maximum paßt, aber eben auch jede andere.

Du mußt also erst den Maximumwert herausfinden und dann aus der Ursprungstabelle die Zeile finden, die diesen Maximumwert identifiziert. Hier ist das mangels anderer Daten die Kundennummer, die Auftragsnummer und der Max-Wert selbst. Das sollte hier genau genug sein, aber in anderen Szenarien mußt Du berücksichtigen, daß der gleiche Max-Wert auch in einer anderen Zeile vorkommen kann, die übrigen Kriterien müssen also genau genug sein, um exakt die gewünschte Zeile der Ursprungstabelle zu ermitteln.

Wenn Du diese zuverlässig ermittelt hast, kannst Du dafür dann auch alle anderen Informationen der Ursprungstabelle listen, also hier Jahr und Monat, KW, Auftragsnummer, Kundennummer, Artikelnummer und den gefundenen Max-Wert.

Access kann so eine Gesamtabfrage oft nicht im Designmodus anzeigen, nur im SQL.
Du kannst dann einfach die Unterabfrage als einzelne Abfrage erstellen und diese dann genauso in einer zweiten Abfrage verknüpfen. Funktioniert genauso, läßt sich dann auch im Designmodus verarbeiten und die Max-Abfrage könntest Du dann auch für etwas Anderes verwenden.

Du hast jetzt hier eine Excel-Tabelle gezeigt, in einer Datenbank solltest Du Namen wie "Jahr&Monat" vermeiden (das "&"), generell alle Sonderzeichen und Leerzeichen.


Gruß

Christian

[Normalo0815]

Hallo Katja,
nachfolgend eine andere Lösung mit dem gleichen Ergebnis, wie die von #3 Bitsqueezer, jedoch kommt diese ohne Gruppierung aus (Nebeneffekt der in dem Fall vermutlich nicht so wichtig ist: die Datensätze sind bearbeitbar):

Code [Auswählen] Erweitern

SELECT
  [Jahr&Monat],
  KW,
  AuftrNr,
  KDNr,
  ArtNr,
  Nettoumsatz
FROM
  Umsatz as T1
WHERE
  Not Exists ( SELECT
                Null
              FROM
                Umsatz AS T2
              WHERE
                T2.AuftrNr = T1.AuftrNr And T2.Nettoumsatz > T1.Nettoumsatz )
ORDER BY
  [Jahr&Monat],
  KW,
  KDNr,
  AuftrNr,
  ArtNr
"Umsatz" muss durch den tatsächlichen Tabellennamen ersetzt werden!

Die Filterung orientiert sich nur nach der Auftragsnummer, weil es vermutlich pro Auftrag nur eine Kundennummer sein kann (wenn nötig könnte die Filterung aber auch um die Kundennummer erweitert werden).

Wenn es allerdings pro Auftrag mehrere gleiche Höchstpreise gibt, dann werden mehrere Höchstpreis-Datensätze pro Auftrag ausgegeben (auch bei der Lösung in #3)!

Nebenbei bemerkt:
Indizes für Tabellenfelder, die zum Filtern oder Sortieren verwendet werden, wären bei vielen Datensätzen auch sinnvoll.

[K a t j a]

Vielen Da schon mal an alle, für die Hilfe! Ich probier einfach mal rum...

[RolfV]

Hallo, wenn die Tabelle in Access so aufgebaut ist wie in Excel gezeigt, scheint das Datenmodell verkehrt zu sein. Rolf