Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1556to1560
1556to1560
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Letzte Änderung für jeden Kunden mittels SQL finde

Forumthread: Letzte Änderung für jeden Kunden mittels SQL finde

Letzte Änderung für jeden Kunden mittels SQL finde
22.05.2017 11:20:07
Marc
Hallo zusammen,
ich bräuchte wieder mal Eure geschätzte Hilfe: Ich greife aus Excel auf eine Access-Datenbank zu. Die Datenbank hat 2 Tabellen tblStammdaten und tblStatus. In tblStatus werden die sich verändernden Status inklusive Änderungsdatum der in tblStammdaten abgebildeten Kunden gespeichert. Somit hat man immer auch einen Überblick über die Veränderungen.
Jetzt habe ich 2 kleine Challenges (die 2 Fragen teile ich in 2 Beiträge auf, da sie inhaltlich unterschiedlich sind). Anbei mal das eine Problemchen:
Für einen gegebenen Zeitraum X bis y (durch Variablen angegeben) soll für jeden Kunden die letzte Statusänderung gesucht werden.
Die folgende Query funktioniert mal ganz gut (Ausgabe LieferantenID, Status, Datum der Änderung), liefert mir aber leider nicht nur den letzten Status, sondern alle Veränderungen innerhalb des gesuchten Zeitraums.
SELECT tblStatus.LieferantenID, tblStatus.StatusNeu, MAX(tblStatus.Datum_Statuswechsel)
FROM tblStatus
WHERE tblStatus.Datum_Statuswechsel >= #" & Format(StartDat, "yyyy-mm-dd") & "# AND tblStatus. _
Datum_Statuswechsel
Ich war der Meinung, ich könnte mit MAX(Datum_Statuswechsel) nur das letzte Datum auslesen, bin aber offensichtlich im Irrtum;-). Weiss einer von Euch wie ich das hinkriege? Notfalls würde es mir auch reichen, wenn ich einfach den zum Zeitpunkt y aktuell gültigen Status auslesen könnte.
Vielen Dank im Voraus für Euren Input.
Lg Marc
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Letzte Änderung für jeden Kunden mittels SQL finde
22.05.2017 11:27:12
ChrisL
Hi Marc
Aus dem hohlen Bauch...
SELECT ... FROM ...
WHERE
tblStatus.Datum_Statuswechsel >= #" & Format(StartDat, "yyyy-mm-dd") & "# AND tblStatus.Datum_Statuswechsel AND
tblStatus.Datum_Statuswechsel = MAX(tblStatus.Datum_Statuswechsel)
Also MAX in die WHERE-Bedingung integrieren.
cu
Chris
Anzeige
AW: Letzte Änderung für jeden Kunden mittels SQL finde
22.05.2017 11:43:59
Marc
Hallo Chris,
zunächst Danke für die schnelle Antwort! Dein Vorschlag hat mir spontan eingeleuchtet, und ich war sicher, dass damit das Problem behoben ist. Aber leider zu früh gefreut, es kommt die folgende Fehlermeldung:
Aggregatfunktion in WHERE-Klausel
(tblStatus.Datum_Statuswechsel = MAX(tblStatus.Datum_Statuswechsel)) nicht möglich.
Leider kann ich damit nichts anfangen. Hast Du da ev. eine gute Idee zu?
Lg Marc
Anzeige
AW: Letzte Änderung für jeden Kunden mittels SQL finde
22.05.2017 12:51:54
ChrisL
Hi Marc
Stimmt. Mein Bauchgefühl hat mich getäuscht.
Ich habe die Lösung im Moment auch nicht und muss gleich weg. Schaue später oder morgen wieder rein.
Meine Vermutung: Es brauch zwei separate Abfragen resp. eine Verschachtelung...
https://www.tutorials.de/threads/select-max-datum-und-group-by.302078/
cu
Chris
Anzeige
AW: Letzte Änderung für jeden Kunden mittels SQL finde
22.05.2017 13:53:43
Marc
Hallo Chris,
danke für den Link (hatte ich bei meiner intensiven Websuche auch schon gefunden) und dass Du es nochmals anschauen willst. Irgendwie scheine ich die MAX-Funktion hier nicht korrekt zu benützen. Wenn man nur schon
SELECT       modem_id, MAX(datum) AS datum
FROM         modem
GROUP BY     modem_id
anschaut, dann sollte man ja zu der Ansicht kommen, dass die WHERE-Einschränkung nach Datum gar nicht nötig ist. Aber dann erhält man trotzdem einfach eine Liste mit allen Status pro Kunde?
Und leider sind die SQL-Befehle für EXCEL VBA nicht so ausführlich dokumentiert im Netz (oder ich finde sie einfach nicht).
Anbei noch eine Variante, die sogar gleich beide meiner Fragen lösen könnte, scheint aber so für Excel nicht zu funktionieren (Quelle: http://stackoverflow.com/questions/22282944/access-vba-sql-find-max-date-and-retrieve-row-id): 
SELECT test.ID, test.Inv, test.TDate, test.Status
FROM test INNER JOIN
(SELECT test.Inv, Max(test.TDate) AS MaxOfTDate
FROM test
GROUP BY test.Inv)  AS q
ON (test.TDate = q.MaxOfTDate) AND (test.Inv = q.Inv)
GROUP BY test.ID, test.Inv, test.TDate, test.Status;
Ev. hast Du dazu eine gute Idee? Ich wüsste es zu schätzen!
Lg
Marc
Anzeige
SQL Zwischenabfrage
22.05.2017 19:45:45
ChrisL
Hi Marc
Ich denke dein Link ist genau das Richtige:
https://stackoverflow.com/questions/22282944/access-vba-sql-find-max-date-and-retrieve-row-id 

SELECT test.ID, test.Inv, test.TDate, test.Status
FROM test INNER JOIN
(SELECT test.Inv, Max(test.TDate) AS MaxOfTDate
FROM test
GROUP BY test.Inv)  AS q
ON (test.TDate = q.MaxOfTDate) AND (test.Inv = q.Inv)
GROUP BY test.ID, test.Inv, test.TDate, test.Status;
Das SQL-Statement habe ich in Access überprüft und funktioniert.
Dass die Max-Formel in deinem Beispiel nicht funktioniert, ist normal (ebenfalls in Access geprüft). Es geht scheinbar nur über eine Zwischenabfrage:
https://www.techonthenet.com/access/queries/max_query2_2007.php
Während der Recherche habe ich auch noch ein Excel-ADODB-Muster gesehen, wo eine Zwischenabfrage gemacht wurde. Ich denke das sollte prinzipiell funktionieren.
Evtl. besondere Feldnamen (Sonderzeichen, Unterbrechungen) noch in Klammern packen [eckigeKlammer] oder 'Apostroph' und natürlich auf exakte Schreibweise achten (ich teste es immer in Access vor).
cu
Chris
Anzeige
SQL-Zwischenabfrage
22.05.2017 22:09:05
Helfi
Hallo Chris,
ok, gebe mich geschlagen, machen wir nur in einem Thread weiter (obwohl ich es nur gut gemeint habe;-).
Danke für Deine Abklärung, das Testen in Access ist eine gute Idee. Kann man effektiv die Queries aus Access 1:1 übernehmen? Habe das auch schon probiert, aber hatte da Fehlermeldungen?
Ich bin immer noch verunsichert, ob die verschachtelte Abfrage (Select-Abfrage innerhalb einer Select-Abfrage) über Excel überhaupt geht? Man findet irgendwie nirgends sowas auf dem Netz, obwohl für Access und MySQL x Varianten existieren (alle Links bisher beziehen sich ja auf Access oder SQL generell). Ich habe auf jeden Fall bisher trotz unzähligen Versuchen keinen Erfolg gehabt. Andererseits habe ich die [eckigen Klammern] noch nicht versucht, ich probiere es nochmals.
Die gute Message generell ist aber, dass ich inzwischen einen Workaround gefunden habe: Die tblStatus dient ja nur als Historie, der aktuelle Status ist in der tblStammdaten nochmals gespeichert. Analog zu Deinem ersten Vorschlag habe ich nun eine zusätzliche WHERE-Bedingung eingefügt, indem ich den Status in den 2 Tabellen vergleiche. Damit (in Kombination mit der Sortierung nach Änderungsdatum absteigend) kann ich alle ausser den letzten, aktuellen Änderungen ausschliessen. Ist nicht so elegant und vor allem nicht für andere einfach analog umsetzbar, aber eine praktikable Lösung für mich.
Aber mit Deinen guten Inputs hast Du mich angefixt, ich gebe noch nicht auf und poste nächstens nochmals meine finalen Schlussfolgerungen.
Schönen Feierabend, lg Marc
Anzeige
AW: SQL-Zwischenabfrage
23.05.2017 08:43:32
ChrisL
hi Marc
Ich habe nun auch noch die Datenverbindung getestet, anhand von deiner ursprünglichen Aufgabenstellung:
Sub t()
Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Datenbank1.accdb"
strSql = "SELECT tblStatus.LieferantenID, tblStatus.StatusNeu, tblStatus.Datum_Statuswechsel" _
& " FROM tblStatus INNER JOIN (SELECT tblStatus.LieferantenID, Max(tblStatus. _
Datum_Statuswechsel) AS MaxOfDate" _
& " FROM tblStatus" _
& " GROUP BY tblStatus.LieferantenID)  AS q ON (tblStatus.LieferantenID = q.LieferantenID) AND ( _
tblStatus.Datum_Statuswechsel = q.MaxOfDate)" _
& " GROUP BY tblStatus.LieferantenID, tblStatus.StatusNeu, tblStatus.Datum_Statuswechsel;"
cn.Open strConnection
Set rs = cn.Execute(strSql)
Sheets("Tabelle1").Range("A1:C5").Value = Application.Transpose(rs.GetRows())
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
(Die String-Trennung ohne & ist durch das Forum und müsstest du auskorrigieren)
SQL-Codes aus Access lassen sich fast immer in andere SQL-Anwendungen übertragen. Je nach SQL gibt es kleine Unterschiede im Syntax aber wenn ein SQL-Statement bereits in Access nicht funktioniert, dann funktioniert es meist in anderen Anwendungen auch nicht ;)
cu
Chris
strSql = "SELECT tblStatus.LieferantenID, tblStatus.StatusNeu, tblStatus.Datum_Statuswechsel" _
& " FROM tblStatus INNER JOIN (SELECT tblStatus.LieferantenID, Max(tblStatus.Datum_Statuswechsel) AS MaxOfDate" _
& " FROM tblStatus" _
& " GROUP BY tblStatus.LieferantenID) AS q ON (tblStatus.LieferantenID = q.LieferantenID) AND (tblStatus.Datum_Statuswechsel = q.MaxOfDate)" _
& " GROUP BY tblStatus.LieferantenID, tblStatus.StatusNeu, tblStatus.Datum_Statuswechsel;"
Anzeige
AW: SQL-Zwischenabfrage
23.05.2017 11:49:30
Marc
Hallo Chris,
manchmal scheitert es an so wenig, habe es heute morgen noch selber auch versucht, habe aber offenbar irgendwo doch noch einen kleinen Syntax-Fehler drin gehabt. Dein String funktioniert effektiv und löst genau mein Problem, dazu noch einiges eleganter als mein bisheriger Workaround.
Danke Dir nochmals für Deine super Unterstützung! Ich war zwar im Nachhinein gesehen schon auf dem richtigen Weg, aber manchmal braucht es einfach eine gute Idee eines Aussenstehenden. Und der Tipp mit dem Testen in Access ist alleine Gold wert!
Ich hoffe, ich kann mich irgendwann mal revanchieren;-)
Lg
Marc
Anzeige
AW: SQL-Zwischenabfrage
23.05.2017 15:28:16
ChrisL
Hi Marc
Danke für die Rückmeldung. Freut mich, dass es doch noch funktioniert.
Und ja, SQL ist unverzeihlich ;)
cu
Chris
Anzeige
Anzeige

Infobox / Tutorial

Letzte Änderung für jeden Kunden mittels SQL abfragen

Schritt-für-Schritt-Anleitung

Um die letzte Statusänderung für jeden Kunden in einer Access-Datenbank abzufragen, kannst du folgende SQL-Abfrage verwenden. Diese Abfrage nutzt eine verschachtelte Abfrage, um den aktuellen Status zu ermitteln:

SELECT tblStatus.LieferantenID, tblStatus.StatusNeu, tblStatus.Datum_Statuswechsel
FROM tblStatus INNER JOIN
(SELECT tblStatus.LieferantenID, MAX(tblStatus.Datum_Statuswechsel) AS MaxOfDate
 FROM tblStatus
 GROUP BY tblStatus.LieferantenID) AS q
ON (tblStatus.LieferantenID = q.LieferantenID) AND (tblStatus.Datum_Statuswechsel = q.MaxOfDate);
  1. Starte Excel und gehe zu den Entwicklertools.
  2. Öffne den Visual Basic for Applications (VBA) Editor.
  3. Füge einen neuen Modus hinzu und kopiere den obigen SQL-Code in eine Subroutine.
  4. Ersetze die Datenquelle im strConnection String mit dem Pfad deiner Access-Datenbank.
  5. Führe das Skript aus und die Ergebnisse werden in das angegebene Excel-Blatt geladen.

Häufige Fehler und Lösungen

  • Fehlermeldung: Aggregatfunktion in WHERE-Klausel nicht möglich.
    Um diese Fehlermeldung zu vermeiden, stelle sicher, dass die MAX()-Funktion in einer separaten Abfrage (wie oben gezeigt) verwendet wird. Aggregatfunktionen können nicht in der WHERE-Klausel eingesetzt werden.

  • Syntaxfehler in der SQL-Abfrage.
    Überprüfe, ob du die eckigen Klammern um Feldnamen verwendest, die Sonderzeichen oder Leerzeichen enthalten. Zum Beispiel [tblStatus.LieferantenID].

Alternative Methoden

Wenn du nicht nur SQL verwenden möchtest, kannst du auch VBA-Methoden in Excel verwenden, um auf die Daten zuzugreifen und sie zu analysieren. Eine alternative Methode könnte die Verwendung der ADODB.Connection-Klasse sein, um SQL-Abfragen direkt in VBA auszuführen.

Sub FetchLastStatus()
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Pfad\zu\deiner\Datenbank.accdb"
    strSql = "Hier kommt deine SQL-Abfrage rein"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    ' Hier kannst du die Daten in ein Excel-Blatt übertragen
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Praktische Beispiele

Ein praktisches Beispiel wäre, die letzte Statusänderung für Lieferanten innerhalb eines bestimmten Zeitraums abzufragen. Dazu kannst du die WHERE-Klausel in deiner SQL-Abfrage wie folgt anpassen:

WHERE tblStatus.Datum_Statuswechsel BETWEEN #StartDatum# AND #EndDatum#

Ersetze #StartDatum# und #EndDatum# durch die entsprechenden Variablen oder Daten, die du abfragen möchtest.

Tipps für Profis

  • Teste deine SQL-Abfragen zuerst in Access. Das hilft, Syntaxfehler frühzeitig zu erkennen.
  • Scheue dich nicht vor verschachtelten Abfragen. Diese können oft die benötigten Ergebnisse liefern, auch wenn sie komplizierter erscheinen.
  • Nutze die MS SQL-Funktionen. Viele der in Access verwendeten SQL-Befehle funktionieren auch in MS SQL, achte aber auf Unterschiede im Syntax.

FAQ: Häufige Fragen

1. Wie kann ich die letzte Statusänderung für mehrere Kunden abfragen?
Du kannst die oben angegebene SQL-Abfrage verwenden und sicherstellen, dass du die GROUP BY-Klausel korrekt anwendest, um die Ergebnisse pro Kunde zu gruppieren.

2. Funktionieren diese SQL-Abfragen auch in anderen SQL-Datenbanken?
Ja, viele SQL-Abfragen sind übergreifend, jedoch kann es zu Syntaxunterschieden kommen. Teste die Abfragen in der spezifischen Umgebung, in der du arbeitest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige