Hallo zusammen,
ich habe folgendes (soweit funktionierendes) SQL-Statement, welches ich gerne erweitert hätte:
SELECT tblTrack.trkID ,
tblTrack.trkAuftragsnummer ,
tblUser.usrVorname & ' ' & tblUser.usrNachname AS Name,
tblTrack.trkZeit ,
tblAbteilungen.abtName
FROM tblUser
INNER JOIN (tblAbteilungen
INNER JOIN tblTrack
ON tblAbteilungen.abtID=tblTrack.trkZiel)
ON tblUser.usr422Name =tblTrack.trkAbsender
WHERE trkZiel NOT LIKE 'SAP'
ORDER BY tbltrack.trkzeit DESC;
Jetzt möchte ich gerne jede tblTrack.trkAuftragsnummer nur einmal erhalten, mämlich den Datensatz mit dem letzten Zeitstempel (tbltrack.trkzeit) für diese Auftragsnummer.
Hat jemand einen Lösung?
Gerne praktiziert, aber schnell unhandlich ist die von Dir verwendete Methode, erst alle (verfügbaren) Tabellen zu verknüpfen und dann zu versuchen, etwas daraus zu machen.
Da solche Verknüpfungen meist zu Datenvermehrung führen und zur Verarbeitung vieler Daten mehr Zeit benötigt wird als bei wenigen Daten, sollte man bestrebt sein, seine eigentlichen Berechnungen nur mit den genau dafür notwendigen Daten (Felder, Tabellen) auszuführen und dann erst die anderen anzuzeigenden Informationen anzuknüpfen.
Technisch solltest Du also aus der Tabelle tblTrack die gewünschte Filterung erstellen und dann im zweiten Schritt genau diese Abfrage statt der gesamten Tabelle mit den beiden anderen Tabellen verknüpfen.
SELECT T.*
FROM tblTrack AS T
INNER JOIN
(SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE trkZiel > 'SAP'
OR trkZiel < 'SAP'
GROUP BY trkAuftragsnummer
)
AS U
ON T.trkAuftragsnummer = U.trkAuftragsnummer
AND T.trkZeit = U.MaxZeit
MfGA
ebs
Zitat von: ebs17 am September 13, 2012, 13:11:03
...Technisch solltest Du also aus der Tabelle tblTrack die gewünschte Filterung erstellen und dann im zweiten Schritt genau diese Abfrage statt der gesamten Tabelle mit den beiden anderen Tabellen verknüpfen...
Ich danke Dir für die Ausführung, wenngleich mir immer noch schleierhaft ist, wie man fehlerlos die andere Tabellen mittels JOIN (tblAbteilungen und tblUser) daran knüpft.
Blöderweise ist die Fehlerangabe im Access sehr vage, was ein SQL-Statement angeht, so dass Trial and Error (meine meist genutzte Lernmethode) fast unmöglich ist. Im Gegensatz zum eigentlichen VBA-code gestaltet sich die Fehlersuche im SQL doch sehr sehr zäh.
Zitatdie andere Tabellen mittels JOIN (tblAbteilungen und tblUser) daran knüpft
Du kannst doch die gezeigte SQL-Anweisung als gespeicherte Abfrage anlegen und verwendest dann diese wie gewohnt statt der Tabelle.
Der andere Weg ist, statt des Tabellennamens die SQL-Anweisung als Unterabfrage einzufügen. Wenn man den ursprünglichen Tabellennamen als Tabellenalias beibehält (was sachlich aber falsch und somit iritierend ist), ist das ein einfaches Copy&Paste.
...
FROM tblUser
INNER JOIN (tblAbteilungen INNER JOIN
(SELECT ...) AS tblTrack
ON tblAbteilungen.abtID=tblTrack.trkZiel)
ON tblUser.usr422Name =tblTrack.trkAbsender
...Die Verwendung eines kurzen Alias (hier U) ist im Schreibaufwand kürzer und dann sachlich besser:
...
FROM tblUser
INNER JOIN (tblAbteilungen INNER JOIN
(SELECT ...) AS U
ON tblAbteilungen.abtID=U.trkZiel)
ON tblUser.usr422Name =U.trkAbsender
...Die zweite Methode bevorzuge ich, da dann Abfragen entstehen, die nur Tabellen und deren Felder als Basis haben und ich so keine Abhängigkeiten von anderen Abfragen beachten muss, die sonst bei Mehrfachnutzung von Basisabfragen entstehen (Pflegeaufwand).
Außerdem hat man in der zusammengefassten Abfrage (sofern man etwas SQL lesen kann) den Überblick und kann in der Unterabfrage besser Überflüssiges wie z.B. ORDER-Anweisungen erkennen.
MfGA
ebs
Tut mir Leid, ich kriegs einfach nicht zusammen...
(ich hätte Dir gerne was anderes gesagt, bei der Mühe die Du Dir gegeben hast).
Das, was ich verstanden habe sieht so aus (läuft aber nicht)
ZitatstrSQL = "SELECT * " & _
"(FROM tblUser " & _
"(INNER JOIN (tblAbteilungen " & _
"(INNER Join " & _
"(SELECT T.* " & _
"FROM tblTrack AS T " & _
"INNER Join " & _
"(SELECT trkAuftragsnummer, " & _
"MAX(trkZeit) As MaxZeit " & _
"FROM tblTrack " & _
"WHERE (trkZiel > 'SAP' " & _
"OR trkZiel < 'SAP') " & _
"AND (trkZiel > 'JUN' " & _
"OR trkZiel < 'JUN') " & _
"AND trkZeit < DATE()-33 " & _
"GROUP BY trkAuftragsnummer " & _
") " & _
"AS U " & _
"ON T.trkAuftragsnummer = U.trkAuftragsnummer " & _
"AND T.trkZeit = U.MaxZeit " & _
"ON tblAbteilungen.abtID=U.trkZiel) " & _
"ON tblUser.usr422Name =U.trkAbsender " & _
"ORDER BY T.trkAuftragsnummer ASC;"
Die Darstellung als String in VBA ist nun sehr unübersichtlich. Damit würde man stets erst beginnen, wenn der SQL-String an sich in Ordnung ist, weil sich gerade bei den Zeilentrennungen und ggf. Variablen leicht Fehler einschleichen.
Zitatläuft aber nicht
... heißt was genau?
(In der Programmierung muss man treffen, nicht nur in die Richtung schießen: Präzision!)
Mir fehlt nach spontanem Blick nach "U.MaxZeit" eine schließende Klammer.
MfGA
ebs
Ich hab die Fehlermeldung mal angehängt (mit Klammer hinter U.MaxZeit). Ohne Klammer sieht der Fehler genau so aus.
Ich kann wie gesagt mit diesen Meldungen nichts anfangen, da sie m.E. nicht aussagekräftiger sind als "läuft nicht". Das ist ja gerade das Problem, welches ich grundsätzlich mit SQL (gegenüber VB, C#, C++, etc...) habe ???
Hier mal die ansehnlichere Version ;)
ZitatSELECT * (FROM tblUser (
INNER JOIN (tblAbteilungen (
INNER JOIN
(SELECT T.*
FROM tblTrack AS T
INNER JOIN
(SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (
trkZiel > 'SAP'
OR trkZiel < 'SAP'
)
AND
(
trkZiel > 'JUN'
OR trkZiel < 'JUN'
)
AND trkZeit < DATE()-33
GROUP BY trkAuftragsnummer
)
AS U
ON T.trkAuftragsnummer = U.trkAuftragsnummer
AND T.trkZeit = U.MaxZeit
)
ON tblAbteilungen.abtID=U.trkZiel) ON tblUser.usr422Name =U.trkAbsender ORDER BY U.trkAuftragsnummer ASC;
[Anhang gelöscht durch Administrator]
O.K. da sind jetzt viele Fehler drin, ein wesentlicher wurde auch von mir verursacht:
Da ich U als Tabellenalias in der Unterabfrage verwende, muss diese gesamte Unterabfrage nach außen einen anderen Namen (Alias) haben.
Deine jetzige Abfrage als Teillösung:
SELECT T.*
FROM tblTrack AS T
INNER JOIN
(SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (
trkZiel > 'SAP'
OR trkZiel < 'SAP'
)
AND
(
trkZiel > 'JUN'
OR trkZiel < 'JUN'
)
AND trkZeit < DATE()-33
GROUP BY trkAuftragsnummer
)
AS U
ON T.trkAuftragsnummer = U.trkAuftragsnummer
AND T.trkZeit = U.MaxZeit
Diese Teilabfrage bekommt den Alias X und wird in die ursprüngliche Gesamtabfrage einkopiert.
Als Zwischenschritt Rahmenabfrage mit neuem Alias:
SELECT *
FROM tblUser
INNER JOIN (tblAbteilungen
INNER JOIN (...) AS X
ON tblAbteilungen.abtID=X.trkZiel)
ON tblUser.usr422Name =X.trkAbsender
ORDER BY X.trkzeit DESC;
Und nun werden die Platzhalterpunkte durch die Teilabfrage ersetzt:
SELECT *
FROM tblUser
INNER JOIN (tblAbteilungen
INNER JOIN (SELECT T.*
FROM tblTrack AS T
INNER JOIN
(SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (
trkZiel > 'SAP'
OR trkZiel < 'SAP'
)
AND
(
trkZiel > 'JUN'
OR trkZiel < 'JUN'
)
AND trkZeit < DATE()-33
GROUP BY trkAuftragsnummer
)
AS U
ON T.trkAuftragsnummer = U.trkAuftragsnummer
AND T.trkZeit = U.MaxZeit) AS X
ON tblAbteilungen.abtID=X.trkZiel)
ON tblUser.usr422Name =X.trkAbsender
ORDER BY X.trkzeit DESC;
Nun noch evtl. nachformatieren und den String nach VBA übertragen, je nach weiterer Verwendung.
Die Fehlermeldungen, die man im Abfrageeditor bekommt, sind die besten, die man kriegen kann. Um dann aber den Punkt genau zu treffen, muss man wissen, wie die Syntax in SQL und zusätzlich in Jet-SQL (z.B. Klammerung bei JOIN's) aussehen muss.
MfGA
ebs
Hmmm... auch auf die Gefahr hin, dass es nervt:
Teil 1 funktioniert bestens, der untere SQL-Code verursacht immer noch ein "fehlender Operator in tblUser.usr422Name ...".
Kannst Du mir nebenher ein Buch empfehlen um das ganze mal nach zu studieren? Also nicht unbedingt eines, dass die einzelnen Begriffe kapitelweise erklärt sonden genau diese Verschachtelungen und Optimierungsmöglichkeiten, was die Performance angeht, etc...?
edit:
HALT: Kommando zurück, ich glaub ich habs fast!
ZitatVerschachtelungen und Optimierungsmöglichkeiten, was die Performance angeht
Teilabfragen und Verschachtelungen ergeben sich aus funktionalen Forderungen. Einfach ist natürlich immer besser.
- Ein Prinzip wäre, kleine Teiltabellen zu verknüpfen wie bereits oben beschrieben (erst filtern, dann verknüpfen), weil die Verarbeitung von wenigen Daten schneller geht als die von vielen Daten.
- Ein wesentliches Prinzip ist Indexnutzung, vergleiche Performance total mies (http://www.office-loesung.de/ftopic413215_0_0_asc.php&highlight=alter+schwede)
Das im Link genannte Script von Michael Zimmermann ist mein Standardwerk (noch kenne ich es nicht auswendig).
- Ein weiterer Punkt wäre der Netzwerktraffic und dessen Reduzierung. Transport von Daten kostet Zeit.
- Ein weiterer Punkt wäre, ein aktives DBMS statt eines File-Backends zu verwenden und aktiv zu nutzen.
- Die Verwendung von Abfragen ist an sich schon ein Beitrag für die Performance, weil das bei passender Datenstruktur deutlich schneller ist als eine prozedurale Einzelverarbeitung von Daten per VBA, Java, C# usw., weil SQL hochqualifiziert ist für Massendatenverarbeitung (Bagger statt Sandschaufel).
MfGA
ebs
Hallo Carlo,
Bei der Gestaltung von verschachtelten Abfragen kann man schon mal Probleme bekommen ;D...
Mein Tipp nach ähnlichen Problemen:
1: Die Abfragen einzeln mit QBE (Query By Example) gestalten und dann verknüpfen (also Abfrage2 greift dann auf Abfrage1 zu), dass ist nicht so elegant u. wie Ekkehart schon bemerkte auch mit Nachteile verbunden. (aber man hat erst mal eine Lösung)
2: Wenn 1. klappt, kann/sollte man versuchen, beide Abfragen zu verschachteln (schrittweise, bis es klappt).
3. Wenn 2. klappt, kann man aus der Abfrage den SQL-String kopieren, in VBA einsetzen u. dort an VBA-Syntax anpassen.
So kannst du schrittweise überprüfen ob die Datenstruktur passt u. ob die elementaren SQL-Anweisungen stimmen (1. Schritt) ob eine Verschachtelung klapp (2. Schritt) und ob eine funktionierende SQL-Anweisung richtig in VBA eingebettet ist (3. Schritt)
Gruß Oma
Hallo Oma,
Zitatu. wie Ekkehart
Du meinst sicher Eberhard :)
Bei SQL überlasse ich ihm auch auf jeden Fall
das Feld; - da kann ich ihm wohl nie das Wasser
reichen, und bin eher stiller Mitleser/-lernender ;)
gruss ekkehard
Hallo,
@ekkehard: sorry, tatsächlich habe ich euch verwechselt (weil du auch, wie ebs, ziemlich gut im Forum unterwegs bist)
@carlo: hatte noch vergessen, auf zahlreiche Links hinzuweisen, wie z.B. http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Unterabfragen (http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Unterabfragen)
Gruß Oma
Hallo Oma,
Entschuldigen musst Du dich bei Eberhard,
Du hast mich ja mit seinen Federn geschmückt ;)
gruss ekkehard
Nebenbei:
@C4RL0: Generell finde ich es von Dir hervorragend, SQL-Anweisungen formatiert zu zeigen und sicher auch für Dich persönlich so zu verwenden. Zum Lesen und besseren Verstehen bei etwas komplexeren Abfragen hilft das allemal.
Meine Frage: Welchen Formatierer verwendest Du? (Das Spurenbild kenne ich noch nicht.)
Hintergrund der Frage: Die SQL-Ansicht des Abfrageeditors bietet leider nicht die Möglichkeit des Formatierens (hier wird eher deformatiert neben einigen weiteren Unzulänglichkeiten).
Ich suche eine Lösung, die ich möglichst direkt an den originalen Abfrageeditor von Access ankoppeln kann (für pure Access-Verwendung ohne andere Entwicklungsumgebungen), die dann auch wegen der Nachnutzbarkeit möglichst keine Lizenzgebühren verursacht.
@ekkehard: Unverdientes Lob muss man nicht zwingend zurückweisen. Man kann es auch als Ansporn verwenden, es sich nachträglich zu verdienen.
MfGA
ebs
Hallo Eberhard,
Ich bemühe mich ja.
gruss ekkehard
Zitat von: ebs17 am September 15, 2012, 12:10:51
Meine Frage: Welchen Formatierer verwendest Du? (Das Spurenbild kenne ich noch nicht.)
Hintergrund der Frage: Die SQL-Ansicht des Abfrageeditors bietet leider nicht die Möglichkeit des Formatierens (hier wird eher deformatiert neben einigen weiteren Unzulänglichkeiten)....
I.d.R. verwende ich entweder den:
http://poorsql.com/
oder den:
http://www.sqlinform.com/free_online_sw.html
Welchen ich in den o.g. Beispielen verwendet habe, weiß ich nicht mehr. Leider versagen manche ihren Dienst, sobald ein Syntax Error eingebaut ist.
An dieser Stelle noch mal Danke an alle Beteiligten!
Eine Änderung hab ich noch, und zwar in folgendem Teil:
SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (trkZiel > 'SAP' OR trkZiel < 'SAP' )
AND (trkZiel > 'JUN' OR trkZiel < 'JUN')
AND trkZeit < DATE()-33
GROUP BY trkAuftragsnummer;
Dieser Code liefert Datensätze, wo das Datum trkZeit älter als 33 Tage ist.
Ich möchte aber nur Datensätze, wo das letzte Datum trkZeit älter als 33 Tage ist
Wenn das letzte Datum dieses Auftrags trkZeit jünger / gleich 33 Tage ist möchte ich von der trkAuftragsnummer KEINEN Datensatz bekommen.
Folgendes funktioniert leider nicht:
SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (trkZiel > 'SAP' OR trkZiel < 'SAP' )
AND (trkZiel > 'JUN' OR trkZiel < 'JUN')
AND MAX(trkZeit) < DATE()-33
GROUP BY trkAuftragsnummer;
Wie löse ich das Problem?
Hallo,
probiere mal:
SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (trkZiel <> 'SAP' ) ' besser noch eine Zeile u. Not In ('SAP'; 'Jun')
AND (trkZiel <> 'JUN' )
GROUP BY trkAuftragsnummer;
HAVING (((DMax("trkZeit","tblTrack","trkAuftragsnummer=" & [trkAuftragsnummer]))<Date()-33));
Gruß Oma
@oma: Zum Vorschlag sowie zum "besser noch" solltest Du eine Beschreibung der Risiken und Nebenwirkungen beilegen oder aber erklären, woher Du weißt, dass C4RL0 nur eine geringfügige Tabelle hat: Die mögliche Indexnutzung, die ich beiläufig eingeführt hatte und die C4RL0 übernommen wenn nicht gar verstanden hat, möchtest Du wieder killen und sogar als Vorteil verkaufen?
Vorschlag:
SELECT trkAuftragsnummer,
MAX(trkZeit) AS MaxZeit
FROM tblTrack
WHERE (
trkZiel > 'SAP'
OR trkZiel < 'SAP'
)
AND
(
trkZiel > 'JUN'
OR trkZiel < 'JUN'
)
GROUP BY trkAuftragsnummer
HAVING MAX(trkZeit) < DATE() - 33
Hinweis dazu: WHERE filtert vor der Gruppierung (auch im Abfrageablauf => Verkleinerung der Datenmenge vor der aufwändigeren Aktion Gruppieren/Aggregieren), HAVING filtert Ergebnisse der Aggregierung. Vom Abfrageeditor erzeugte SQL-Strings in diesem Zusammenhang sind recht oft fehlerhaft.
Außerdem: Danke für die Links. Den ersten kannte ich noch nicht.
MfGA
ebs
Zitat von: ebs17 am September 17, 2012, 20:46:12...Hinweis dazu: WHERE filtert vor der Gruppierung (auch im Abfrageablauf => Verkleinerung der Datenmenge vor der aufwändigeren Aktion Gruppieren/Aggregieren), HAVING filtert Ergebnisse der Aggregierung...
Das ist wohl mal wieder das klassische Beispiel für "Ich hab das mal gelesen aber bisher noch nie gebraucht". Dank dieses praktischen Beispiels ist es jäh wieder zum Vorschein gekommen.
Vielen Dank für die Erläuterungen und den Code, der hervorragend läuft!