Guten Tag zusammen,
ich habe ein sehr kompliziertes Problem und hoffe, dass ich es verständlich zu Schrift bringen kann.
Ich entwickle für die Arbeit gerade eine Access-Datenbank, die mir eine Liste mit zu überprüfenden Produkten/Kunden ausgibt.
Soweit steht die Datenbank und funktioniert auch, gibt jedoch nicht ganz genau das aus, was ich mir vorstelle.
Ich habe eine Tabelle die alle Produkte mit definierten Soll-Prüf-Werten enthält.
Eine weitere Tabelle enthält alle Produkte und Kunden, die in Kombination stehen, sowie 2 Ist-Prüf-Werte.
Meine Abfrage prüft, ob für ein Produkt für einen Kunden die Ist-Werte außerhalb der definierten Soll-Werte liegen.
Wenn die Abfrage ausgeführt wird, werden alle Werte geprüft und nach "Qualität" absteigend sortiert. Von diesem Ergebnis werden die oberen 10% in eine Tabelle geschrieben, da nur diese geprüft werden müssen.
Hier nun das Problem: Die oberen 10 % werden aus dem GESAMT-Ergebnis gewählt. Eigentlich sollte die Abfrage aber so funktionieren, dass das erste Produkt (aus der Tabelle mit allen Produkten) als Kriterium eingesetzt, die Abfrage ausgeführt wird und von diesem Ergebnis dann die oberen 10 % in eine Tabelle geschrieben werden. Anschließend soll die Abfrage mit dem nächsten Produkt als Kriterium laufen, wieder die oberen 10 % wegschreiben werden und dann erneut mit dem nächsten Produkt aus der Tabelle als Kriterium laufen, bis das Ende der Produkte-Tabelle erreicht ist.
Ich habe mittlerweile einiges gelesen von Recordsets, MoveNext, EOF etc. aber bin nicht sicher, wie ich mein Vorhaben damit realisieren kann.
Ist das was ich vorhabe überhaupt möglich? Oder muss ich aus den 112 Produktion meiner Liste manuell eine schier unendlich lange UNION-Abfrage basteln?
Ich würde mich echt freuen wenn mir jemand bei diesem Problem helfen könnte, da ich echt nicht mehr weiter weiß.
Danke und Gruß,
Mike
Wenn ich das richtig verstehe, lässt sich die Aufgabe mit einer Abfrage (kein UNION) lösen. Einen konkreten Vorschlag auf den dargestellten Beschreibungsnebel traue ich mir aber nicht zu, bei klaren und nachvollziehbaren Tabellenstrukturen dann schon.
Okay, dann versuche ich mal mein Problem anhand einer Nachbildung genauer zu darzustellen:
Ich habe zum einen die Tabelle mit meinen Werten
DMDUnit = Produkt
Store = Kunde
FcstAcc = Prüfwert1
SMAPE = Prüfwert2
Die Werte-Tabelle sieht folgendermaßen aus:
(http://i.imgur.com/4woE121.png)
(Im Original sind es ca 136000 Datensätze)
Die Tabelle, welche meine Kriterien enthält, sieht so aus:
(http://i.imgur.com/Zzd6q3f.png)
(Im Original sind es ca. 70 Datensätze)
Meine Abfrage sieht so aus:
(http://i.imgur.com/RXS3sdM.png)
In den Eigenschaften ist festgelegt, dass die 25% Spitzenwerte ausgegeben werden.
Und hier komme ich zu meinem Problem.
Das Ergebnis der Abfrage nach jetzigem Stand ist folgendes:
(http://i.imgur.com/XGBQvEM.png)
Die Abfrage wird über alle Produkte in der "Kriterien"-Tabelle ausgeführt und anschließend werden vom Gesamtergebnis die oberen 25 % ausgegeben. Dies sind durch die absteigende Sortierung nach "SMAPE" natürlich die höchsten Werte.
Unsere Mitarbeiter bekämen somit die Info, dass die Prüfung lediglich für Produkt2 für die Kunden 1 und 4 erfolgen muss.
Hier liegt jedoch der Fehler.
Denn die Abfrage sollte eigentlich zunächst für Produkt 1 laufen, davon die oberen 25% in eine Tabelle wegschreiben und danach für das nächste Kriterium, Produkt2, erneut laufen und wieder die oberen 25% wegschreiben.
Das Ergebnis wäre dann richtigerweise folgendes:
(http://i.imgur.com/rTTLoys.png)
Dieses Ergebnis habe ich durch dur Verwendung die folgende UNION-Abfrage erhalten:
SELECT TOP 25 PERCENT Wertetabelle.DMDUNIT, Wertetabelle.Store, Wertetabelle.FcstAcc, Wertetabelle.SMAPE, IIf([Wertetabelle]![FcstAcc]>[Kriterien]![SollFcstAcc],"Prüfen") AS [Prüfung FcstAcc]
FROM Kriterien INNER JOIN Wertetabelle ON Kriterien.DMDUNIT = Wertetabelle.DMDUNIT
WHERE (((Wertetabelle.DMDUNIT)="DEM-Produkt1") AND ((IIf([Wertetabelle]![FcstAcc]>[Kriterien]![SollFcstAcc],"Prüfen"))="Prüfen"))
ORDER BY Wertetabelle.SMAPE DESC;
UNION ALL
SELECT TOP 25 PERCENT Wertetabelle.DMDUNIT, Wertetabelle.Store, Wertetabelle.FcstAcc, Wertetabelle.SMAPE, IIf([Wertetabelle]![FcstAcc]>[Kriterien]![SollFcstAcc],"Prüfen") AS [Prüfung FcstAcc]
FROM Kriterien INNER JOIN Wertetabelle ON Kriterien.DMDUNIT = Wertetabelle.DMDUNIT
WHERE (((Wertetabelle.DMDUNIT)="DEM-Produkt2") AND ((IIf([Wertetabelle]![FcstAcc]>[Kriterien]![SollFcstAcc],"Prüfen"))="Prüfen"))
ORDER BY Wertetabelle.SMAPE DESC;
Aufgrund der hohen Anzahl der Kriterien (ca. 70, aber es können auch Zeitweise bis zu 120 sein) in meiner Original-Datenbank kommt eine UNION-Abfrage aber nicht in Frage.
Daher muss ich irgendweine andere Vorgehensweise finden, die es ermöglicht, meine Abfrage mit einem Kriterium aus meiner "Kriterien"-Tabelle auszuführen und das Ergebnis jeweils in eine Tabelle wegzuschreiben, bevor die Abfrage dann erneut mit dem nächsten Kriterium läuft, so lange bis das Ende der Tabelle erreicht ist.
Das dies per SQL möglich ist glaube ich nicht. Sicher bin ich mir aber nicht, da ich nun etwas von Dynamic SQL gelesen habe, dass eventuell auch eine Lösung sein könnte!?
Ich hoffe, dass ich mein Anliegen so deutlicher machen konnte.
Danke vorab und Gruß,
Daniel
1) Die Wertetabelle sollte wie übrigens jede vernünftige Stammdatentabelle eine eindeutige und verwendungsfähige ID haben. Die Verwendbarkeit eines einfachen und sicheren Schlüssels vereinfacht oft vieles im Vergleich zu ggf. unsicheren Mehrfachschlüsseln.
2) Mein ungetesteter Vorschlag stellt sich dann so auf:
SELECT
W.DMDUNIT,
W.Store,
W.FcstAcc,
W.SMAPE,
"Prüfen" AS [Prüfung FcstAcc]
FROM
Wertetabelle AS W
WHERE
W.ID IN
(
SELECT TOP 25 PERCENT
X.ID
FROM
Kriterien AS K
INNER JOIN Wertetabelle AS X
ON K.DMDUNIT = X.DMDUNIT
WHERE
K.DMDUNIT = W.DMDUNIT
AND
X.FcstAcc > K.SollFcstAcc
ORDER BY
X.SMAPE DESC
)
3) Bei den genannten Datensatzzahlen, aber eigentlich schon generell sollte man hinsichtlich erzielbarer Performance auf eine Indizierung relevanter Felder achten: Grundlagen - SQL ist leicht ( 8 ) - Index (http://www.ms-office-forum.net/forum/showthread.php?t=317707)
Das scheint fast genau das gewünschte Ergebnis auszugeben.
Lediglich am Ende habe ich noch einmal eine Absteigende Sortierung nach SMAPE ausgeführt, aber dies ist nur kosmetisch.
Ich habe den Code auf meine Datenbank angepasst und ausgeführt, die Abfrage läuft aber eine Ewigkeit (wie erwartet). Ich habe die Tabellen und Felder indexiert, aber einen Effekt bemerke ich nicht.
Ich frage mich auch, was genau da nun passiert.
Nach meinem Verständnis fragt das innere SELECT-Statement doch auch nur einfach alle Produkte ab, für die der Soll-Wert überschritten wird, sortiert das Ergebnis anschließend absteigend nach SMAPE und gibt dann nur die oberen 25 % aus.
Eigentlich müsste das doch genau das gleiche Ergebnis sein, wie die normale Abfrage von mir oben, oder?
Wieso aber scheint die Abfrage nun Produkt für Produkt abzufragen und davon jeweils dann die oberen 25 % auszugeben.
Da stehe ich total auf dem Schlauch.
ZitatIch habe die Tabellen und Felder indexiert
Welche genau?
Tabellen lassen sich nicht indizieren.
WHERE
K.DMDUNIT = W.DMDUNIT Dies (Verknüpfung des Artikels der Unterabfrage mit dem Artikel der Hauptabfrage) erzeugt die Gruppen pro Artikel.
Danke erstmal für die Erklärung, warum das ganze im Test funktioniert :)
Ich hoffe ich habe das mit dem Index richtig gemacht.
Ich habe im Backend jeweils die betroffenen Tabellen im Entwurf geöffnet, oben auf Indizies geklickt, und dann einen Indexnamen vergeben, hinter dem ich dann die Feldnamen "zugeordnet" habe.
z.B. so:
(http://i.imgur.com/xn1jqEU.png)
und
(http://i.imgur.com/Ljj0KCI.png)
Völlig falsch?
Hallo,
Du hast hier zusammengesetzte Indizes angelegt. Das macht hier keinen Sinn, zumal die nicht auf eindeutig stehen.
Lege die Indizes einzel an, diese werden Im Tabellenentwurf selbst angelegt.
Siehe Bild (mit anderen Feldnamen). Die Spalte mit den Feldnamen darf für Einzelindizes nicht leer sein.
Eine kleine Diskussion zu Indizes:
a) Zusammengesetzte Indizes können durchaus Sinn machen. Allerdings helfen sie bei einzeln verwendeten Feldern nur beim ersten Feld in der Reihenfolge.
Beispielsweise ist in der gezeigten Abfrage für die TOP-X-Gestaltung die Sortierung funktionell wichtig, das Feld SMAPE hat aber keinen verwendbaren Index.
Mit jeweils einzelnen Indizes wärst Du erst einmal auf der sichereren Seite.
b) Für die zweite Tabelle hast Du auf ID doppelt einen Index vergeben, nur der Primary Key als der stärkere - weil sich hiernach sogar die physische Sortierung in der Tabelle (nach Komprimieren) richtet - genutzt wird.
Zum Nachlesen: Indexplanung (http://www.szweb.de/Datenbankperformance/Indexplanung-Seite-225.html?uKey=dfe73496d7876aa8abdd0fb64c1dc286), ist das Gleiche, was in dem verlinkten PDF steht.
Zumindest der Blick ins Indexfenster ist aber sehr lobenswert, weil nur durch Zusammenklicken im Tabellenentwurf auch einiger Unsinn entstehen kann, den man im Indexfenster wenigstens im Zusammenhang sehen kann.
Nachdem ich nun die felder einzeln indiziert habe, ist die Laufzeit noch immer sehr lang, jedoch kommt ein Ergebnis über ca. 1500 Lines zurück.
YAY!
Jetzt muss ich nur noch validieren, ob das Ergebnis das gewünschte ist ;)