infobase: EDV - MS-Access

SQL

AKTUALISIERUNGS-ABFRAGEN:

Zwei Beispiele für Aktualisierungsabfragen, die Daten aus mehreren Feldern verarbeiten, um einen neuen Feldwert zu bilden:

UPDATE ventile INNER JOIN integr1 ON ventile.matnr = integr1.matnr
SET ventile.angebotszeichnung = integr1.angebotszeichnung,
ventile.freigabenr = integr1.freigabenr,
ventile.entwicklungsnr = integr1.entwicklungsnr,
ventile.serienangabe = integr1.serienangabe;

*

Im folgenden Fall enthalten die zwei Felder bemerkungen_1 und bemerkungen_2 Inhalte, die auf geschmackvolle Weise zu einem neuen, gemeinsamen Wert zusammengefasst werden sollen.
Zur Sicherheit wurde ein neues Feld bemerkungen angelegt, damit keine vorhandenen Daten verändert werden, falls etwas schiefgehen sollte.

UPDATE ventile_dbv SET bemerkungen =
IIF(Not IsNull(bemerkungen_1) AND Not IsNull(bemerkungen_2), bemerkungen_1 & "." & chr$(13) & chr$(10) & bemerkungen_2 & ".",
IIF(Not IsNull(bemerkungen_1) AND IsNull(bemerkungen_2), bemerkungen_1 & ".",
IIF(IsNull(bemerkungen_1) AND Not IsNull(bemerkungen_2), bemerkungen_2 & ".",
Null
)
)
)
;

Die eigenartige Schreibweise soll einer besseren Lesbarkeit Rechnung tragen.
Jede auf die erste IIF-Zeile folgende IIF-Zeile entspricht einem DANN-Statement.
Null ist das letzte DANN-Statement, falls keiner der WENN-Fälle eingetreten ist.

Nach erfolgreicher Aktion können die unnötigen Felder gelöscht werden.

* * * *

Selbst Abhängigkeiten vom Vorkommen von Daten in anderen Tabellen stellen für Anfügeabfragen keine Hürde dar:

UPDATE Adressen INNER JOIN ordo_Versicherungen ON Adressen.Suchname = ordo_Versicherungen.Suchname SET Adressen.Art = "Versicherung";

SQL-ABFRAGEN, oder wie setze ich neue Maßstäbe in der Programmierung:

Bei komplexen Datenbeständen, in der viele, teilweise zusammengehörende Daten in verschiedenen Tabellen stehen, ist der Zugriff für einen normalen Menschen fast unmöglich. Das Geklicke und Gedrecke mit Hilfe von Abfrage-Assistenten ist ja ganz schön (für den Anwender, der sich einbildet, ein fortgeschrittener zu sein), aber edel wäre halt nur ein Werkzeug, daß dem Anwender erlaubt, in mehreren Ebenen (4 reichen) aus einer Liste vorhandener Tabellen auszuwählen, anschließend die Liste der darin enthaltenen Felder angezeigt zu bekommen, einen zulässigen Operator zu benennen, und bei Bedarf sogar einen Überblick über die enthalten Werte zu haben. Die genannten Ebenen können per UND / ODER - Verknüpfung und setzbaren Klammern nach Belieben verschachtelt werden. So sind feinfühligste Abfragen möglich, ohne sich um Syntax, SQL-Problematik oder Assistenten-Eigenarten kümmern zu müssen.

Selbstverständlich kann und soll ein solches Werkzeug nicht die vergleichenden Möglichkeiten der SQL-Sprache zu übertreffen versuchen, sondern vielmehr ein äußerst potentes Mittel zum Auffinden relational getrennt gespeicherter Datensätze geben.

Die nach meiner unmaßgeblichen Meinung bisher beste Lösung dieser Art wurde im Hause DMT entwickelt -> siehe Motordatenbank KORO. Eine einfachere und in Ihrer Effektivität kaum zu übertreffende kann in strobel.mdb bewundert werden.

Die ist vor allem deswegen so toll, weil alles im Klartext in den Feldern einer einzigen Tabelle steht. Die oben mit lockerer Geste gelobten feinfühligen Abfragen können einen bei verknüpften Tabellen schnell an den Rand des Wahnsinns bringen.

* * * *

Überhaupt sind Excel-ähnliche Wenn-Dann(iif)-Konstrukte zuweilen hilfreicher, als man glauben mag. In SQL-Ausdrücken können z.B. trickreiche INSTR und ähnliche Analyse-Funktionen mit AS virtuellen Feldern zugewiesen werden, die dann selbst als Variable in anderen Ausdrücken des SQL-Strings eingesetzt werden können. So konnten im Hause Kelkel ganze Datenformat- und -Import-Prozeduren über SQL-Strings abgewickelt werden, ohne Basic-Routinen entwickeln zu müssen.
Siehe auch logische Ausdrücke

* * * *

Das leidige ';':

In vollständigen SQL-Anweisungen kann / soll / darf es stehen, in einer Where-Bedingung a'la Form_Open jedoch nicht !

* * * *

Formulare und Berichte mit Bedingungen in Form von Unterabfragen aufrufen:

Function CleanFromFirstWhere (sSQL As String) As String

    On Error GoTo err_CleanFromFirstWhere

    Dim sLeft As String, sRight As String, iPos As Integer

    Const LEFT_LENGTH = 7

    ' **** Entferne das erste WHERE eines SQL-Statements ****

    ' beim Aufruf von z.B. Formularen kann als Datensatz-einschränkende Bedingung
    ' ein Teil eines SQL-Ausdruckes ohne WHERE übergeben werden.
    ' Würde man das bei einem vollständigen SQL-Statement durch ein ReplaceInString()
    ' schicken, würden weitere WHEREs, die in Unterabfragen enthalten sind, ebenfalls
    ' enfernt werden und wir erhalten syntakisch ungültige Bedingungen.

    sLeft = Left$(sSQL, LEFT_LENGTH)

    iPos = InStr(sLeft, "WHERE")

    If iPos = 0 Then
       CleanFromFirstWhere = sSQL
       Exit Function
    Else
       sLeft = ReplaceInString(sLeft, "WHERE", "")
    End If

    sRight = Mid$(sSQL, iPos + Len("WHERE") + 1)

    CleanFromFirstWhere = Trim$(sLeft & sRight)

    Exit Function


err_CleanFromFirstWhere:

    Fehler "CleanFromFirstWhere"
    Exit Function

End Function

* * * *

SUCHE IN DATENBANK-FELDERN NACH TEXTWERTEN (STRING-ZEICHENKETTEN):

Selbst im Jahre 2006 stoße ich noch auf neue Erkenntnisse im Bereich des guten, alten Access 2.0:

Beim automatischen Bilden von SQL-Abfragen im Stile von "suche in allen Feldern" würde auch in numerischen Feldern unbeabsichtigt nach Text gesucht werden a'la ident LIKE "*xy*"

Ich hätte geschworen, daß es hier Fehlermeldungen der SQL-Engine hagelt, aber nein, das wird völlig klaglos abgearbeitet. Auch beim manuellen Testen in der Abfrage-Entwurfsansicht geht das ohne Probleme (klar, daß das keine Ergebniss-Treffer bringt). Selbst ein wenig sinnvolles SELECT * FROM adressen WHERE ident LIKE "*20*" ergibt doch glatt sinnvolle Treffer, nämlich alle mit den ident-Werten 20, 120, 200, 220 usw.

Da war mal jemand gnädig. Selbst bei moderneren oder erwachseneren Datenbanken wie MySql geht das alles auch, ohne das Fehler gemeldet werden.

ANFÜGEABFRAGEN:

Einfach und wirksam, da das manuelle Anfügen über die Zwischenablage allzu oft mit 'Daten zu lang für Feld' u.ä. quittiert wird.

INSERT INTO LITSTG SELECT * FROM ARTSTG;

Toll ist es, wenn man Tabellen-Manipulationen mittels SQL-Abfragen ausführen kann, ohne Basic-Code entwickeln zu müssen. Das folgende Beispiel

INSERT INTO Termine
SELECT DISTINCTROW Format$(TERM2ASC.Datum,"dd.mm.yyyy") AS Datum,
Format$(TERM2ASC.Datum,"hh:nn") AS von, TERM2ASC.Text AS Notiz
FROM TERM2ASC;

trennt das vollständige, serielle Datumsformat des Feldes TERM2ASC.Datum in Datum und Zeit auf und weist dies zwei Ausdrücken zu, die den gleichnamigen Feldern 'Datum' und 'von' der Tabelle Termine entsprechen. Das Feld TERM2ASC.Text wird in das Memo-Feld Termine.Notiz projiziert.

****

Übernahme aller integr1-Datensätze in die Tabelle ventile, die keine matnr-Entsprechung haben (im Beispiel gelöst per Unterabfrage):

INSERT INTO ventile SELECT * FROM integr1 WHERE integr1.matnr NOT IN (SELECT matnr FROM ventile)

Die Variante des Vergleiches per Unterabfrage kann bei z.B. 10.000 Datensätze schon mal locker 15 min in Anspruch nehmen, wenn die zeitraubende "NOT IN"-Variante bemüht wird.

****

Manchmal sehen die Quelldaten, die in eine Zieltabelle eingefügt, nicht so aus wie die der Zieltabelle.
Teilweise sollen nur einige Felder übernommen werden und dann haben die auch noch andere Bezeichnungen.
Auch damit wird der SQL-Insert-Befehl locker fertig.
Das folgende Beispiel zeigt, wie sog. zirkuläre Abhängigkeiten aufgelöst werden, wenn ein Feldname in beiden Tabellen gleich lautet (quelltabelle.feldname AS feldname) und wie einfache Namenszuweisungen aussehen (Quellfeldname = Zielfeldname):

INSERT INTO how2
SELECT
matnr_daten_mit_entwicklungsnr_neu_in_how2.entwicklungsnr AS entwicklungsnr,
bestellnr AS matnr,
aznr AS angebotszeichnung,
matnr_daten_mit_entwicklungsnr_neu_in_how2.freigabenr AS freigabenr,
"05.2006" AS merkmal
FROM matnr_daten_mit_entwicklungsnr_neu_in_how2;

ANZAHL von Datensätzen in ABFRAGEN:

SELECT TOP 1000 Adressen.Suchname FROM Adressen, Adressen AS Adressen_1;

verknüpft einfach die Adresstabelle bedingungslos mit sich selbst und liefert bei 800 Datensätzen in der Tabelle glatt 800^2 = 640.000 Datensätze zurück, wenn man dies nicht mit einem SELECT TOP xyz einschränkt. So können auch zuverlässig größere Datenmengen zur Steuerung von z.B. Datensatzgruppen-abhängigen Access-Berichten abgebildet werden.

SQL-AUSDRUCK ERZEUGEN:

In trickreicheren Anwendungen kommt es zuweilen vor, daß abhängig von diversen Angaben dynamische SQL-Ausdrücke gebildet werden müssen. Daß das per Basic elegant und schnell erledigt werden kann, ist bekannt.

Diese Vorgehensweise steht ebenfalls bei der dynamischen Bildung von 'Brut Force'-Abfragen an. Die folgende Funktion liefert für die übergebenen Parameter Tabellenname und Ausdruck einen SQL-Where-Abschnitt zurück, in dem alle Felder der Tabelle per LIKE mit dem String '*Ausdruck*' verglichen werden.

Diese Funktion kann von einer vorgehenden Routine sogar mehrfach aufgerufen werden, um SQL-Ausdrücke zu erzeugen, die eine 'Brut Force'-Suche über mehrere Tabellen ermöglichen !

Bleibt abzuwarten, wie schnell man an Grenzen der Größe oder der Anzahl abgefragter Felder des gebildeten SQL-Strings stößt.

Function Multiple_Or_Krit (Tabelle As String, Wert As Variant) As String

    ' Gibt einen SQL-Where-Abschnitt zurück, in dem alle Felder auf
    ' Ähnlichkeit mit 'Wert' überprüft und per OR verknüpft werden.

    On Error GoTo err_Multiple_Or_Krit

    Dim DB As Database, TD As TableDef, sWhere As String, i As Integer

    Set DB = DBEngine.Workspaces(0).Databases(0)
    Set TD = DB.TableDefs(Tabelle)

    For i = 0 To TD.Fields.Count - 1
        sWhere = sWhere & "[" & TD.Fields(i).Name & "] LIKE '*" & Wert & "*' OR"
    Next i

    sWhere = Left$(sWhere, Len(sWhere) - 4)

    Multiple_Or_Krit = sWhere

    Exit Function


err_Multiple_Or_Krit:

    Fehler "Tools / Multiple_Or_Krit"
    Exit Function

End Function

SQL-Zusatz-Routinen:

Auslesen der Datenquelle (Name einer Tabelle oder Abfrage) aus einem übergebenem SQL-Statement:

Function Get_SQL_RecordSource (v As Variant)

    On Error GoTo err_Get_SQL_RecordSource

    Dim p1 As Integer, p2 As Integer

    ' **** Rückgabe der Datenquelle falls der String ****
    ' **** ein SQL-Statemment mit 'SELECT' ist.      ****

    If InStr(v, "SELECT") Then
       p1 = InStr(InStr(v, "FROM"), v, " ")
       p2 = InStr(p1 + 1, v, " ")
       v = Mid$(v, p1 + 1, p2 - p1 - 1)
    End If

    Get_SQL_RecordSource = v

    Exit Function


err_Get_SQL_RecordSource:

    Fehler "Get_SQL_RecordSource"
    Exit Function

End Function

DUPLIKATABFRAGEN

können das mehrfache Auftreten bestimmter Werte feststellen, um z.Bsp. das nachträgliche Erstellen einer ACCESS-Beziehung, die eine Meldung a'la 'referentielle Integrität läßt sich nicht durchsetzen' bringt, zu ermöglichen.

SELECT DISTINCTROW Exakt, Count(Exakt) AS Anzahl FROM Termine_DMT GROUP BY Exakt HAVING (Count(Exakt)>1);

Zeigt diejenigen Datensätze mit dem Tabellenfeld 'Exakt' und dem virtuellen Feld 'Anzahl', dem jeweils der Ausdruck 'Count(Exakt)' zugeordnet wird, für deren Werte von 'Exakt' ein mehrfaches Vorkommen besteht. Zentrale Elemente sind GROUP BY mit der folgenden HAVING-Klausel.

Hier sieht man pro betroffenen Schlüsselwert eben denselben sowie die Anzahl seines Vorkommen (so er denn mehrfach vorkommt).

In komplexeren Tabellen können sich die meist ungewollten Duplikate-Datensätze im Detail voneinander unterscheiden, so daß man diese Duplikate einzeln mit allen Tabellenfeldern sehen möchte, um sie entsprechend einzeln miteinander vergleichen zu können.

Hierzu kann eine weitere Abfrage erstellt werden, die auf eine vorhandene Duplikate-Abfrage aufbaut (könnte "Duplikate_Abfrage_einzeln" genannt werden):

SELECT *
FROM tabelle
INNER JOIN Duplikate_Abfrage
ON tabelle.feld = Duplikate_Abfrage.feld
ORDER BY tabelle.feld;

Klar, daß die Duplikate_Abfrage die Duplikate des Feldes "feld" in der Tabelle "tabelle" liefert.
Der Gag besteht darin, daß die zweite Abfrage "Duplikate_Abfrage_einzeln" eine Verknüpfung zwischen der eigentlichen Tabelle und der Duplikate_Abfrage herstellt, so daß eben nur solche Datensätze angezeigt werden, deren betroffenes Feld auch durch die Duplikatabfrage eben als Duplikat erkannt wurde. Diese Duplikat-Datensätze werden dann auch ganz brav nacheinander aufgelistet. Und das mit allen gewünschten Feldern, so daß man die Kandidaten in Ruhe miteinander vergleichen kann.

****

Heftig wird es aber immer dann, wenn Duplikate in Bezug auf mehrere Felder vorkommen, wenn z.B. ein Stichwort bei Normalisierungs-Routinen versehentlich mehr als einmal einem ID zugewiesen wurde.

SELECT Ident, Kuerzel, Count(Ident) AS Anzahl
FROM Kuerzel_Zuordnungen
GROUP BY Ident, Kuerzel
HAVING Count(Ident)>1 AND Count(Kuerzel)>1;

Allerdings ist es sehr tricky, mit diesen Daten was zu machen, denn schließlich möchte man ja vielleicht einen Teil der Dubletten-Einträge löschen. Mit solchen Abfragen gefundene Daten werden aber als schreibgeschützt behandelt. Allerdings besteht die Möglichkeit, in einer anderen Abfrage, die 'normale' Datensätze liefert, in einer Unterabfrage auf die (Mehrfach)-Duplikatabfrage Bezug zu nehmen:

SELECT *
FROM Kuerzel_Zuordnungen
WHERE Ident IN (SELECT Ident FROM [Suche Duplikate für Kuerzel_Zuordnungen])
ORDER BY IDENT;

Hier kann man dann auch löschen. Leider werden hier aber auch alle Zuordnungen des Duplikat-Kandidaten angezeigt, was das Auge des Betrachters sehr verwirrt.

Aber immerhin !

Klar, daß es bei einem Microsoft-Produkt, daß Datenbank-Aufgaben übernehmen soll, auch Probleme gibt:

SQL-Anweisungen mit benutzerdefinierten Funktionen per Abfrage oder Code ausführen

Wenn in Abfragen variable Werte eingefügt werden sollen, muß das je nach Implementierung mit Bezug auf Anführungszeichen auf verschiedene Art geschehen.

Das in den SourceCode eingebettete SQL-Statement

SELECT Min(LastCheckDate + revisit_days) AS faellig FROM webmanagement WHERE Besitzer = '" & Get_BesitzerAktuell() & "' AND DontCheck = false

muß im Abfrage-Entwurfsmodus so geschrieben werden:

SELECT Min(LastCheckDate + revisit_days) AS faellig FROM webmanagement WHERE Besitzer = Get_BesitzerAktuell() AND DontCheck = false

FUNKTIONEN innerhalb von ABFRAGEN:

Selbst mit Access 2.0 geradezu traumhaft:
Denn man kann ohne weiteres auch selbst geschriebene Funktionen in SQL-Statements einbinden.

Um relational verbundene Daten 2-dimensional in einer einfachen Textdatei abzubilden, sah ich den einzigen Weg darin, die n-fach vorhandenen Subdaten (in diesem Falle Kommunikations-Datensätze) für jeden einzelnen Adressen-Datensatz durch eine selbstgeschriebene Funktion abzuholen, um diese zu einer Zeichenkette zusammenzufügen.

Das SQL-Statement sieht so aus:

SELECT Adressen.*, Get_KommunikationsElemente(Suchname) AS Kommunikation
FROM Adressen
ORDER BY Suchname;

Die Routine so:

Function Get_KommunikationsElemente (vSuchname As Variant) As Variant

    On Error GoTo err_Get_KommunikationsElemente

    Dim DB As Database, RS As Recordset, s As String

    Set DB = DBEngine.Workspaces(0).Databases(0)
    Set RS = DB.OpenRecordset("SELECT Art, Wert FROM Adressen_Kommunikation WHERE Suchname = '" & vSuchname & "' ORDER BY Art", DB_OPEN_SNAPSHOT, DB_FORWARDONLY)

    Do While Not RS.EOF
       s = s & RS("Art") & ": " & RS("Wert") & ", "
       RS.MoveNext
    Loop

    If s <> "" Then
       s = Left$(s, Len(s) - 2)
    End If

    Get_KommunikationsElemente = s


exit_Get_KommunikationsElemente:

    Set RS = Nothing
    Set DB = Nothing
    Exit Function


err_Get_KommunikationsElemente:

    Fehler "Get_KommunikationsElemente"
    Exit Function

End Function

Selbst bei mehreren hundert Datensätzen wird das komplette Prozedere in ungefähr 1 s abgewickelt, GENIAL !

In der grafischen Datenblatt-Ansicht der Abfrage kann das "virtuelle" Feld dann sogar noch frei verschoben werden.

ALLGEMEINES ZU ABFRAGEN:

- Vorkommen von Mutterdaten in zwei Tochtertabellen (additive Abfragen, additive Daten):

Wenn man so etwas 'additiv' betrachten möchte, wird man sich mit irgendwelchen INNER, LEFT oder RIGHT JOIN's äußerst schwer tun.

Mit Unterabfragen, die gar nicht so kompliziert sein müssen, können sogar sehr leicht logische Verknüpfungen zwischen den Ergebnistabellen hergestellt werden.

SELECT DISTINCT Institutionen.*
FROM Institutionen WHERE Suchname IN
(SELECT Suchname FROM Projekte_Institutionen)
OR Suchname IN
(SELECT Suchname FROM Projekte_Auftraggeber);

zeigt alle Institutionen, die in eine der beiden Untertabellen Projekte_Institutionen bzw. Projekte_Auftraggeber stehen.

Als Abfrage gespeichert kann das Ding dann bequem 'domänen'-like benutzt und mit Filtern etc. versehen werden.

Schwieriger wird es, wenn man die Daten der oben beschriebenen Abfrage sehen möchte, für die das Vorkommen eines bestimmten Suchnamens einer Institution in den beiden Untertabellen zutrifft. Dann muß das Ganze umformuliert und mit einzelnen Where's versehen werden:

SELECT Projekte.*
FROM Projekte
WHERE ((Projekte.Ident In
(SELECT Ident FROM Projekte_Institutionen WHERE Suchname='Institut für Film und
Bild in Wissenschaft und Unterricht (FWU)')
Or Projekte.Ident In
(SELECT Ident FROM Projekte_Auftraggeber WHERE Suchname='Institut für Film und
Bild in Wissenschaft und Unterricht (FWU)')))
OR Projekte.Leitung='Institut für Film und Bild in Wissenschaft und Unterricht
(FWU)';

Das sieht schon ein bißchen häßlich aus, findet aber alle Projekt-Datensätze, bei denen die benannte Institution als Leitung oder in Untertabellen als beteiligte Institution oder als Auftraggeber vorkommt.

Früher hätte ich für sowas 2 Wochen gebraucht, heute geht das u.U. in zwei Stunden. Allerdings muß diese Lösung komplett in den Quelltext eingebaut werden, da bisher noch keine Lösung bekannt ist, einen Abfrage-Parameter so zu hinterlegen, daß er beim Aufruf mit auf den Weg gegeben werden kann, ohne innerhalb der Abfrage als Benutzerdialog zu stören.

* * * *

- SQL-String direkt zuweisen und so schnell zu gültigen Objekten kommen (temporäre Abfragen):

Bisher kam es bereits mehrmals vor, daß manipulierend auf Abfragen eingewirkt werden muß, da einige der Access-Funktionen nicht auf ehemals 'view' genannte Datensatzgruppen, sondern 'nur' auf Domänen angewendet werden können. Das jeweilige Erzeugen und vorige Löschen temporärer Abfragen war immer umständlich. Erst bei einem ganz verschissenem Problem zum Fall strobel.mdb kam ich in der Verzweiflung auf folgende Idee:

s = "SELECT * FROM Zeiten WHERE Kostenstelle='Krank' OR Kostenstelle='Urlaub';"

DB.QueryDefs("Monatsbericht_UF_Q").SQL = s

Die Eigenschaft SQL kann in der direkt benannten Abfrage auch direkt gesetzt werden und bleibt (vorerst) dauerhaft hinterlegt. In dem Beispiel ist die Objektvariable DB der einzig gesondert zu vereinbarende Code-Teil.

So kann doch wohl sehr einfach mit einer anfangs leeren 'Temp_Q' gearbeitet werden.

* * * *

- Abfragen können auch als FILTER-ABFRAGEN gespeichert werden:

Das ist zwar im Prinzip dokumentiert, doch worauf es dabei wirklich ankommt, sagt einem natürlich wieder mal keiner.

Das mit dem Formular-Filter bearbeiten-Dialog klappt gut, das mit dem 'Menü Datei | als Abfrage speichern' auch, nur 'Menü Datei | aus Abfrage laden' dann überhaupt nicht. It's tricky und eigentlich auch gar nicht dumm, in diesem Dialog nur die Abfragen zu zeigen, die in ihrem Namen auch die Zeichenkette 'abfrage' enthalten, da dann dem Anwender die vom Programmierer erstellten Abfragen verborgen bleiben und dieser nur seine eigenen sieht. Aber wer in aller Welt soll darauf kommen, wenn das NIRGENDS dokumentiert ist ? Ein glückliches Händchen hat in diesem Falle der, der mit naiver Begeisterung alle Defaults (hier 'Abfrage1') übernimmt und danach auch mit dem Auftauchen dieser Abfrage in dem 'Aus Abfrage laden'-Dialog belohnt wird. Naja ...

Das Naja ist berechtigter als man gemeinhin glaubt. Der Versuch, Abfragen mit dem Namensinhalt 'abfrage' in dem Glauben zu speichern, daß diese dann im Dialog [Filter - Menü Datei - aus Abfrage laden] auch wieder angeboten werden, ist bereits mehr als einmal fehlgeschlagen. Die im vorhergehenden Abschnitt beschriebenen Zusammenhänge müssen u.U. wieder revidiert werden; evtl. unterscheidet MS-Access zwischen Filter-Abfragen und echten Abfragen. Filterabfragen können nämlich auch als 'sau' gespeichert werden und tauchen dann manchmal auch im entsprechenden Dialog auf.

Vielleicht gibt es ja auch einen geheimen Flag, der aber auch bei engagiertem Suchen in den Systemtabellen nicht gefunden werden wollte. Der Dreck kann mich mal ...

* * * *

Kriterien-bezogenes Ausführen von Abfragen 2.ter Ordnung (gemeint sind ABFRAGEN, die aufeinander BEZUG nehmen):

Manchmal kommt es vor, daß Abfragen von z.B. sehr "schlanken" Daten künstliche Felder mit bereinigtem Inhalt o.ä. enthalten.

Nach solchen künstlichen Feldern kann direkt nicht sortiert werden.

Wird eine wie vorhin beschriebene Abfrage gespeichert, kann aber eine zweite Abfrage auf die künstlichen Felder der ersten sowohl mit ORDER BY, DISTINCT sowie auch GROUP BY und evtl. auch anderen SQL-Aggregat-Funktionen zugreifen.

Eine solche zweite Abfrage kann auch mit einer WHERE-Klausel auf Daten künstlicher Felder Bezug nehmen, aber leider nicht auf die anderen Felder, die in der ersten Abfrage enthalten sind.

Die Kombination solcher aufeinander Bezug nehmender Abfragen zeigt auch erst den Sinn gespeicherter Abfragen (stored procedures sollen ein Pendant zu professionellen Datenbanken a'la Oracle sein). Solche Abfragen können später wie Tabellen als echte Datenquellen behandelt werden.

Wenn bei einem solchen 2-Abfragen-System die Daten der ersten Abfrage Kriterien-bezogen geliefert werden sollen, um danach durch eine zweite Abfrage aufbereitet zu werden, wird die Sache schwierig.

Eine Lösung kann per Code erfolgen:

Das SQL-Statement der Abfragen, die nachträglich mit Kriterien abgefragt werden sollen, kann im Quelltext hinterlegt werden, um dort dynamischen Änderungen unterworfen zu werden. Das so geänderte Statement kann per

CurrentDb.QueryDefs(ABFRAGENAME).SQL = SqlStatement

zugewiesen werden.

INKONSISTENZABFRAGEN / VERWAISTE DATEN:

Inkonsistenzabfragen sind wesentlicher einfacher, als ich es bei Concert Merchandising gedacht habe:

SELECT DISTINCTROW [Kunden].[Kunden-Code] FROM Kunden
LEFT JOIN Bestellungen ON [Kunden].[Kunden-Code] = [Bestellungen].[Kunden-Code]
WHERE ([Bestellungen].[Kunden-Code] Is Null );

Gefunden werden alle Kunden, zu denen keine Bestellungen vorliegen.

Die Klausel WHERE 'Kunden-Code Is Null' erledigt das Problem.

Bei der Durchsetzung der Regeln der referentiellen Integrität kann es aber auch vorkommen, daß in einer untergeordneten Zuordnungstabelle Geisterdaten übrigbleiben, wenn z.B. in der Entwicklungsphase ein Masterdatensatz gelöscht wurde, bevor die Regeln der referentiellen Integrität durchgesetzt werden konnten.

Diese Abfrage zeigt die verwaisten Datensätze der Zuordnungstabelle. Nachdem diese gelöscht sind, kann die Beziehung hergestellt werden.

SELECT DISTINCTROW [Kuerzel_Zuordnungen].[Ident]
FROM Kuerzel_Zuordnungen LEFT JOIN LITSTG ON [Kuerzel_Zuordnungen].[Ident] =
[LITSTG].[Ident]
WHERE ([LITSTG].[Ident] Is Null );

Beiden Inkonsistenz-Abfragen gemein ist die LEFT JOIN - Verknüpfung, die alle Datensätze der linken Seite liefert, auch wenn keine Entsprechungen auf der rechten Seite bestehen.

*

Die zweite Abfrage hätte auch ohne 'Rumdrehen' der Elemente mit einem RIGHT JOIN realisiert werden können.

Ebenso könnte man Unterabfragen "... IN (SELECT ..." für so was gebrauchen, z.B.

SELECT *
FROM Termine
WHERE ID NOT IN (SELECT ID FROM Leistungsrapport) AND Suchname = "Strobel";

KREUZTABELLENABFRAGEN

oder wie entledige ich mich einer Tabellenkalkulation:

Eine normale Auswahlabfrage könnte z.Bsp. Artikel monatsweise mit der Summe der Verkaufserlöse auflisten:

ARTIKEL  M    SUMME
------------------------
Zange    jan  4424
Zange    feb  5675
Zange    mär  7654
...
Hammer   jan   765
Hammer   feb   876
Hammer   mär   654

Eine Kreuztabelle könnte so etwas im Excel-Stil darstellen:

ARTIKEL  JAN  FEB   MÄR
------------------------
Zange    865  6757  765
Hammer   877  764   654

Beispiel:

PARAMETERS [Verkaufszahlen für welches Jahr?] LONG;

TRANSFORM Sum(Bestelldetails.Anzahl * (Bestelldetails.Einzelpreis - (Bestelldetails.Rabatt / 100) * Bestelldetails.Einzelpreis)) AS Verkäufe

SELECT Artikelname FROM Bestellungen INNER JOIN(Artikel INNER JOIN Bestelldetails ON Artikel.[Artikel-Nr] = Bestelldetails.[Artikel-Nr]) ON Bestellungen.[Bestell-Nr] = Bestelldetails.[Bestell-Nr]

WHERE DatePart("yyyy", Bestelldatum, 2, 2) = [Verkaufszahlen für welches Jahr?]

GROUP BY Artikelname ORDER BY Artikelname PIVOT DatePart("m", Bestelldatum, 2, 2);

Erstellt eine Kreuztabellenabfrage, die Artikelverkaufszahlen nach Monaten für ein vom Benutzer angegebenes Jahr anzeigt. Die Monate werden von links nach rechts (gedreht) als Spalten angezeigt, und die Artikelnamen werden von oben nach unten als Zeilen angezeigt.

Die Abfrage zeigt in NWIND.MDB für den Parameter 1993 folgende Daten:

ARTIKELNAME  1    2    3    4
------------------------------
Artikel1   867  687  765  334
Artikel2   876  463  897  585
Artikel3   889  486  985  549

ARTIKELNAME fungiert in dieser Tabelle als Zeilenüberschrift. Nach diesem Feld wird gruppiert und sortiert.

Der PIVOT-Ausdruck DatePart("m", Bestelldatum, 2, 2) stellt die Spaltenüberschriften dar. Nach diesem Ausdruck wird ebenfalls gruppiert.

Der Ausdruck VERKÄUFE ... wird als jeweiliger Tabelleneintrag benutzt.

LOGISCHE AUSDRÜCKE IN ABFRAGEN:

Da geht zuweilen mehr, als man sich träumt.

Häufig tritt das Problem auf, das z.B. in einer Zeittabelle sparsamerweise nur die Felder von, bis und Abzug vereinbart werden, weil ja die Dauer dann innerhalb eines berechneten Steuerelementes zur Laufzeit ermittelt werden kann.

Das allein ist aber gar nicht so einfach, wie bereits das Mitternachtsproblem bei AOS gezeigt hatte. Die alternative Berechnungsmethodik für Zeitenbereiche über 00:00 hinaus läßt sich witzigerweise nicht nur im regulären Quelltext formulieren, sondern auch innerhalb des SELECT-Teiles einer SQL-Anweisung:

IIF(Leistungsrapport.bis>=Leistungsrapport.von,Format((Leistungsrapport.bis-Leistungsrapport.von-Abzug),"hh:nn"), Format(-24+Leistungsrapport.von-Leistungsrapport.bis+Abzug,"hh:nn")) AS Dauer

Das läßt eine größere SQL-Anweisung zwar sehr schnell unübersichtlich aussehen, wird aber zügig und scheinbar zuverlässig ausgeführt.

Ein anderes Beispiel für die Potenz logischer Auswertungen im SELECT-Bereich ist:

SELECT IIF([Anschluß 1]="e-mail",[Nummer 1],
IIF([Anschluß 2]="e-mail",[Nummer 2],
IIF([Anschluß 3]="e-mail",[Nummer 3],
IIF([Anschluß 4]="e-mail",[Nummer 4],"keine e-mail")))) AS email, *
FROM Adressen
WHERE ((Adressen.[Anschluß 1]="e-mail"))
OR ((Adressen.[Anschluß 2]="e-mail"))
OR ((Adressen.[Anschluß 3]="e-mail"))
OR ((Adressen.[Anschluß 4]="e-mail"))
ORDER BY Adressen.Suchname;

Nicht erschrecken; die Abfrage liefert erst mal alle Adressen , die in den Feldern

Die IIF- oder zu deutsch 'WENN DANN'-Anweisung ist ja bereits aus der OA3-Report-Programmierung bekannt und eines der wenigen Dinge, die uns aus der 'guten, alten Zeit' der EDV erhalten geblieben sind.

Auch schön ist die SQL-basierende Lösung zu einem Problem, das öfter bei Berichten vorkommt. Mehrere Felder sollen in einem Ausdruck als ein zusammengesetzter Ausdruck erscheinen (z.B. Vor-und Nachname). Entweder man versucht, mit standardisierten Feldgrößen möglichst kleine Abstände einzuhalten (klappt nie), oder man versucht, mittels heftigster Basic-Routinen (und das während des Druckvorganges) Umwandlungen vorzunehmen, oder man geht den professionellen Weg über logische Ausdrücke in SQL-Strings:

SELECT Suchname, IIF(Vorname<>'',Vorname & ' ' & Nachname,Nachname) AS Name FROM Adressen;

baut aus den Feldern Vor- und Nachname einen Ausdruck namens Name zusammen und kann sogar prüfen, ob ein Vorname vorliegt, damit der Ausdruck nicht ein führendes Leerzeichen vor dem Nachnamen enthält. Yes !

Ein Test mit 6.800 Datensätzen verlief quasi lichtschnell und das allerbeste: Diese in der SQL-Anweisung zusammengebauten Ausdrücke stehen in Formularen und Berichten zur Verfügung !

LÖSCHABFRAGEN in SQL-Statements:

Ein Ausdruck a'la

DELETE * FROM tabelle WHERE kriterien

Als ich aber das erste Mal einen solchen Ausdruck an eine MySql-Datenbank absetzte, wurde ich doch glatt mit einer Fehlermeldung konfrontiert. Gestört hatte das "*"; in einer "professionellen" SQL-Umgebung möchte man eher sowas wie ein

DELETE FROM tabelle WHERE kriterien

DELETE FROM table_name[.*]

DELETE vorname FROM tabelle

Access 2.0 erlaubt per freistehender DROP-Anweisung das Löschen von Tabellen und Indizes, in einer ALTER TABLE - Anweisung per DROP COLUMN auch das Entfernen eines Feldes.

MySql möchte/kann auch das etwas anders haben:
Auch da gibt es freistehende DROP TABLE - Anweisungen und ein erlaubtes

ALTER TABLE DROP feldname

ALTER TABLE DROP COLUMN feldname

Alles in allem merkt man, daß die Entwickler solcher Sprach-Aussagensysteme zu ihrer Zeit manch anderes "im Kopf" gehabt haben.

ABFRAGEN, die das Vorkommen eines Wertes in VERSCHIEDENEN TABELLEN überpruefen (MULTITABELLEN-ABFRAGE):

* * * *

Das Vorkommen eines Wertes in einer Vielzahl verschiedener Tabellen (z.B. B-Nr in verschiedenen Bosch-Artikel-Tabellen) kann sehr schnell mit folgendem Abfragekonstrukt ermittelt werden.

Im Beispiel wird eine Tabelle abgefragt und mit den Ergebnissen von 6 anderen Tabellen in Form von 7 Unterabfragen verglichen. Im Vergleich zum bei Unterabfragen zeitraubenden NOT IN geht das hier sehr fix über die Bühne

Das finale FROM test1 steht eigentlich nur der SQL-Syntax zuliebe da und TOP 1 reduziert lediglich die Ergebnismenge (bringt vor allem bei großen Datenmengen Tempo).

Die mehrfach verschachtelten IIFs enthalten autonome Unterabfragen, deren WHERE-bedingte Ergebnisse direkt mit dem gesuchten Wert verglichen werden, um dann jeweils den Namen der entsprechenden Tabelle auszugeben oder zum naechsten IIF-Unterabfragen-Konstrukt zu verzweigen.

Der Clou besteht letztendlich darin, ein einziges Feld in einer einzigen Ergebniszeile auszugeben, dessen Inhalt den Namen der gesuchten Tabelle enthaelt.

SELECT TOP 1
IIF((SELECT Wert FROM test1 WHERE test1.Wert="7000")="7000", "test1",
IIF((SELECT Wert FROM test2 WHERE test2.Wert="7000")="7000", "test2",
IIF((SELECT Wert FROM test3 WHERE test3.Wert="7000")="7000", "test3",
IIF((SELECT Wert FROM test4 WHERE test4.Wert="7000")="7000", "test4",
IIF((SELECT Wert FROM test5 WHERE test5.Wert="7000")="7000", "test5",
IIF((SELECT Wert FROM test6 WHERE test6.Wert="7000")="7000", "test6",
IIF((SELECT Wert FROM test7 WHERE test7.Wert="7000")="7000", "test7",
""))))))) AS Ergebnis
FROM test1;

Selbst auf einem Pentium I wird eine solche Abfrage, auch wenn der gesuchte Wert erst im letzten IIF-Unterabfragen-Konstrukt gefunden wird, OHNE erkennbare Zeitdauer ausgefuehrt.

* * * *

Die klassischere Methode besteht eigentlich im Verknüpfen von Tabellen mit JOIN-Konstrukten, was bei 2 Tabellen auch eine übersichtliche Sache ist.

Schwieriger wird es, wenn mehr als 2 Tabellen verknüpft werden sollen, da die Verkettung der JOIN-Anweisungen einer eigenartigen Syntax folgt:

SELECT Suchname, Grund, Termine.von, Termine.bis, Termine.Notiz
FROM Termin_Gründe INNER JOIN
(Termine LEFT JOIN Leistungsrapport ON Termine.ID = Leistungsrapport.ID) ON Termin_Gründe.Bezeichnung = Termine.Grund
WHERE Leistungsrapport.ID Is Null AND Termin_Gründe.[geschäftlich]=True AND Suchname="Strobel";

In dem Beispiel sollten ursprünglich nur Daten der Tabelle Termine abgefragt werden; das vorliegende Beispiel wurde vom Abfrage-Assistenten geliefert. Die Joins müssen in der Reihenfolge stehen, ein "SELECT *" liefert alle Felder aus allen verknüpften Tabellen.

Im Beispiel werden ein paar Felder der Termine-Tabelle ausgegeben.
Gefunden werden solche Datensätze, für die in der Tabelle Leistungsrapport kein Eintrag existiert und für die für den jeweiligen Termine-Grund in der Tabelle Termin_Gründe das Feld geschäftlich auf ja gesetzt ist.

Ein anderes Beispiel für das Abfragen mehrerer Tabellen ist:

SELECT *
FROM AUFTRAG
INNER JOIN (Leistungsrapport
INNER JOIN Leistungen
ON Leistungsrapport.Art = Leistungen.Art)
ON Auftrag.Nr = Leistungsrapport.Auftrag;

Diese Abfrage zeigt Daten aller existierenden Aufträge mit den dazugehörigen Leistungsrapporten. Ergänzt wird eine solche Datenzeile um die Felder der Tabelle Leistungen.

Da ich immer wieder über die Syntax der Verschachtelung stolpere, möchte ich die Vorgehensweise so beschreiben:
- ausgehend von der 'obersten' Tabelle gehen die INNER JOINs zu den Tabellen, die jeweils an der zuletzt genannten dran hängen.
- Bei einem zusätzlichen, verschachtelten INNER JOIN einfach den letzten Tabellennamen in Klammern setzen und darin ein INNER JOIN ... ON - Statement ausführen.

Ich wage gar nicht daran zu denken, wie so etwas aussieht, wenn an obere und mittlere Tabellen weitere INNER JOINs mit weiteren Verschachtelungen gehängt werden. So ein SQL-Statement ist ohne eigene Dokumentation gar nicht mehr nachvollziehbar.

Klar, daß genau der Fall auch prompt gleich darauf eintrat.

Da das Einsatzdatum nur in der Tabelle Termine steht, die nicht "geradlinig" in der Hierarchiekette Auftrag-Leistungsrapport-Leistungen liegt, muß eine dementsprechende SQL-Anweisung anders aussehen.

Peinlicherweise habe ich mir vom Access-Abfrage-Assistenten helfen lassen, nachdem meine Nerven durch überlange Computer-Support-Telefonate überstrapaziert waren:

SELECT *
FROM
(Auftrag
INNER JOIN (Leistungsrapport
INNER JOIN Termine
ON Leistungsrapport.ID = Termine.ID)
ON Auftrag.Nr = Leistungsrapport.Auftrag)
INNER JOIN Leistungen
ON Leistungsrapport.Art = Leistungen.Art
ORDER BY Auftrag.Nr, Leistungsrapport.Id;

* * * *

Eine Alternative zu JOIN-Verknüpfungen sind teilweise einfacher zu lesende Unterabfragen:

SELECT * FROM integr1 WHERE matnr IN (SELECT matnr FROM ventile);

ABFRAGEN können auch Daten in getrennten Tabellen zusammengefasst darstellen:

Die folgende Abfrage sieht alle Strassennamen aus den Datensätzen der Tabellen Adressen und xTabelle, die jeweils das Kriterium Ort='Stuttgart' erfüllen. Das virtuelle Feld AllStr erhält seinen Wert dann im iif-Ausdruck entweder aus einem
passenden Adressen- oder xTabellen-Datensatz. Diese Werteliste kann sogar dem DISTINCT-Verfahren unterzogen werden. Geiler gehts nimmer.

SELECT DISTINCT iif(Adressen.Ort='Stuttgart',Adressen.Strasse,xTabelle.Strasse)
AS AllStr FROM Adressen, xTabelle
WHERE Adressen.Ort='Stuttgart' OR xTabelle.Ort='Stuttgart'

Was aber so nicht geht, sind rein zusammengefasste Abfragen, die z.B. 180 Datensätze auflisten, von denen 100 aus einer und 80 aus einer anderen Tabelle stammen (zusammengesetzte Daten).

Dieses Problem lösen, wenn auch mit gewissen Einschränkungen, UNION-Abfragen:

SELECT Feld1, Feld2, Feld3, Feldx
FROM tabelle1
UNION SELECT Feld1, Feld2, Feld3, Feldy FROM tabelle2
UNION SELECT Feld1, Feld2, Feld3, Feldz FROM tabelle3;

Liefert lediglich die Anzahl aller Datensätze aller bezogenen Tabellen, die Darstellung wird allerdings in die Form der Feldnamen und -Anzahl gepresst, die durch die Tabelle1 vorgegeben ist.

Wenn die Tabellen von der Anzahl der Felder her gleich sind, kann die Anweisung auch verkürzt werden:

SELECT *
FROM tabelle1
UNION SELECT * FROM tabelle2
UNION SELECT * FROM tabelle3;

PARAMETER-ABFRAGEN:

Eine witzige Sache; im SQL-String können Parameter-Merkmale wie Aufforderungstext und Datentyp angegeben werden, die dann beim Ausführen der Abfrage per InputBox entgegen-genommen und ausgewertet werden.

Laut Access-Dokumentation sieht das ganze so aus (tut nicht !):

PARAMETERS [Einen Nachnamen eingeben:] Text;
SELECT * FROM Personal
WHERE [Bitte Nachnamen eingeben:] = Nachname;

Fordert den Benutzer zur Eingabe des Nachnamens eines Angestellten auf und verwendet dann diese Eingabe als Kriterium für die Abfrage.

Was passiert, wenn der arglose Anwender das ausprobiert ?

Es erscheinen zwei Eingabefenster (weil die Inhalte der beiden Zeichenketten innerhalb der eckigen Klammern verschieden sind), von denen nur das zweite im WHERE-Teil ausgewertet wird, obwohl hierfür keine Datentyp-Deklaration stattfand !

Eine abgespeckte DMT-Version sieht so aus:

SELECT *
FROM Termine
WHERE Format$(von,"yyyy")=[Sau];

und tut genau so mit einer einzigen Parameter-Abfrage.

Das Geheimnis besteht darin, daß der Variablenname im Deklarationsteil mit dem im WHERE-Teil übereinstimmen muß, sonst evaluiert Access-SQL eine weitere Variable varianter Art. Man kann sogar die eckigen Klammern weglassen, die aber erforderlich sind, wenn etwas ausformuliertere Aufforderungen erscheinen sollen.

Die wahre Form der Parameter-Abfrage sieht also so aus:

PARAMETERS [Geben sie das auszuwertende Jahr ein:] SHORT;
SELECT *
FROM Termine
WHERE Format$(von,"yyyy")=[Geben sie das auszuwertende Jahr ein:]
ORDER BY von;

Es werden alle Termine eines anzugebenden Jahres nach Zeitpunkt sortiert ausgegeben.

Ein Beispiel aus der komplexen VB6-Anwendung "PMFRes" der Fa. vector, in der eine Aggregat-Funktion mit einer "Distinct"-Darstellung kombiniert wird:

Die Situation:
Eine Tabelle enthält Projektdaten: id und Startdatum.
Eine zweite Tabelle enthält mehrere quality-gate-Daten mit "Zu erledigen"-Datum.

Der Wunsch:
Eine möglichst zusammengefasste Abfrage, die Daten aus beiden Tabellen mit dem jeweils höchstem due_date in einer einzigen Zeile anzeigt.

Die Umstände:
Diese Daten sollten mit ungeordneten Projekt-ids einer dictionary-Liste verglichen werden. Damit die Daten nicht für jeden Eintrag der dictionary-Liste einzeln abgefragt werden müssen, habe ich das Navigieren innerhalb einer einmal geöffneten Abfrage vorgezogen.

Das Ergebnis:
Funktioniert ganz wunderbar in weniger als 1s.

Der Weg:

Zuerst die "DISTINCT"-Darstellung innerhalb der Subdaten-Tabelle (due_date):
SELECT project, Max(due_date) AS due_date
FROM tblProjectPhase
GROUP BY project
ORDER BY project;
liefert anstelle von 900 nur noch 195 Datensätze.
Es werden in der Tat für jeden Projekt-Datensatz nur eine Zeile angezeigt, die im Feld due_date den jeweils höchsten Wert enthält.

Um diese Daten mit denen einer anderen Tabelle zu verbinden, wird das Statement wie folgt erweitert:
SELECT project, tblProjectData.start_date, Max(due_date) AS end_date
FROM tblProjectPhase INNER JOIN tblProjectData ON
tblProjectData.project_id = tblProjectPhase.project
GROUP BY project, tblProjectData.start_date ORDER BY project;
Hier mußte noch das zusätzliche erwünschte Feld der zweiten Tabelle in den Group-Bereich aufgenommen werden.

Das Durchlaufen der dictionary-Liste geht so (VB6):

  For i = 0 To dictProjectList.count - 1
    rs.MoveFirst
    rs.Find "project=" & dictProjectList(i)
    Debug.Print rs!project & " " & rs!start_date & " " & rs!end_date
  Next

* * * *

Im Hause Bosch ergab es sich, daß einer Tabelle, die einen eigenen Auszug von dat013-Ventildatensätzen (aber ohne id-Feld) enthielt, die in der dat103-Muttertabelle zugewiesenen id-Werte den entsprechenden Auszugs-Datensätzen zugeordnet werden sollte.

Dummerweise gab es zu diesem Zeitpunkt kein eindeutiges Kriterium mehr (vormals BNr). Die relevanten Felder Bnr und OrderNr sind obendrein nicht einmal zwingend ausgefüllt.

Die Abfrage verarztet fast alle Kandidaten, vereinzelte Reste können handverlesen korrigiert werden.

UPDATE Ventile
LEFT JOIN Ventile_hp_vhp ON Ventile_hp_vhp.size = Ventile.size
SET Ventile.id = Ventile_hp_vhp.id
WHERE Ventile_hp_vhp.BNr=Ventile.BNr AND Ventile_hp_vhp.OrderNr=Ventile.OrderNr
OR (Ventile.BNr=Null AND Ventile_hp_vhp.OrderNr=Ventile.OrderNr)
OR (Ventile.OrderNr=Null AND Ventile_hp_vhp.BNr=Ventile.BNr)
;

SUMMEN-AUSWERTUNG per Abfragen

Aus dem Stegreif immer wieder ein Problem.

SELECT Kostenstelle,SUM(Dauer) AS Stunden,SUM(Kosten_Kst) AS Kosten
FROM Zeiten
GROUP BY Kostenstelle;

listet bereits alphabetisch sortiert alle vorkommenden Kostenstellen einzeln a'la DISTINCT auf. In der SELECT-Anweisung dürfen allerdings keine weitere Felder, sondern nur Ausdrücke enthalten sein, ansonsten müssen selektierte Felder auch in der GROUP-BY-Klausel aufgeführt werden.

UNTERABFRAGEN,

ein äußerst potentes Mittel, um Daten einer Tabelle in Abhängigkeit vom Vorkommen bestimmter Daten in anderen Tabellen abzufragen.

Die folgende SQL-Anweisung ermittelt alle Datensätze der Tabelle Stichworte, für die in einer anderen Tabelle W_S_Zuweisungen Stichwort_ID-Einträge bestehen, deren Wissen_ID mit dem ID eines Master-Formulares übereinstimmen:

Select * From Stichworte Where ID IN (Select ID_Stichwort From Wissen_Stichworte_Zuweisungen Where ID_Wissen=" & Forms!Wissen!ID);

Die Abfrage gibt genau die Daten zurück, die im oberen Beispiel durch die Anweisungen vor dem Wort IN selektiert werden. Mit diesen Daten kann dann unbekümmert gearbeitet werden, ohne Angst haben zu müssen, unwissentlich Daten anderer Tabellen zu verändern, wie dies bei ab und zu bei Abfragen der Fall ist, bei denen mehrere Tabellen miteinander verknüpft wurden.

Eine erweiterte Form einer mehrfach per "WHERE ID IN (SELECT ...)" verschachtelten Unterabfrage kann das diffizile Problem lösen, wenn in einer Zuweisungstabelle ein Datensatz gefunden werden soll, der in genau dieser Tabelle mehrere Einträge mit eben verschiedenen Zuweisungen besitzt:

SELECT *
FROM Zuweisungen_Hardware_Preisfindung
WHERE ID_Preisfindung = "1.1"
AND ID_Hardware IN (SELECT ID_HARDWARE FROM Zuweisungen_Hardware_Preisfindung WHERE ID_Preisfindung = "2.2")
AND ID_Hardware IN (SELECT ID_HARDWARE FROM Zuweisungen_Hardware_Preisfindung WHERE ID_Preisfindung = "3.4");

Da hier lediglich innerhalb einer Tabelle und mit kleinen Teilmengen gearbeitet wird, geht das Ganze rasant von statten.

Bei großen Tabellen kommt es allerdings zu langen Laufzeiten !

Hier wurden in Phase1.mdb SEL Klumpp aus 34.000 Schlagwort-Zuordnungs-Datensätzen die ausgespuckt, die mit 100 'echten' Kürzeln aus der Tabelle Lookup übereinstimmen. Ca. 20s Dauer.
Beim Quervergleich mit 300 Unterabfragen-Datensätzen dauert das ca. 5min !

SELECT *
FROM Schlagwort_Zuordnungen
WHERE Schlagwort IN
(SELECT LookupWert FROM Lookup1 WHERE
LookupWert=Schlagwort_Zuordnungen.Schlagwort
AND LookupTyp='Schlagwort')
ORDER BY Schlagwort;

Aber richtig schwierig wird es, wenn eine Abfrage mehrere UND-Kriterien für ein Feld einer 1:n-Tabelle abfragen soll a'la 'zeige alle Dokumente, für die in der Personen-Tabelle Einträge für Klumpp und Bonnet bestehen. Aller Versuche mit JOINS schlugen fehl, da ja kein Eintrag existieren kann, der alleine die Bedingung Name="Klumpp" AND Name="Bonnet" erfüllen kann.

Für solche Fälle müssen verschachtelte Unterabfragen her:

SELECT LITSTG.* FROM LITSTG WHERE LITSTG.Ident IN
(SELECT Personen_Zuordnungen.Ident FROM Personen_Zuordnungen
WHERE Personen_Zuordnungen.Personen ="Klumpp, Dieter"
AND Personen_Zuordnungen.Ident IN
(SELECT Personen_Zuordnungen.Ident FROM Personen_Zuordnungen
WHERE Personen_Zuordnungen.Personen = "Bonnet, Petra"));

Das scheint dann auch richtige Ergebnisse zu geben.
Wenn zu Klumpp 132 und zu Bonnet 5 Datensätze existieren, wovon beide in 1 gemeinsam vorkommen, dann liefert ein OR anstelle des AND korrekte 136 Dokument-Datensätze.

Bei OR kann man die beiden Bedingungen auch einfach in den einen Unterabfragen-Ausdruck eingeben:

(SELECT Personen_Zuordnungen.Ident FROM Personen_Zuordnungen
WHERE Personen_Zuordnungen.Personen ="Klumpp, Dieter" AND
Personen_Zuordnungen.Personen="Bonnet, Petra")

Allerdings ist eine klassische, verknüpfte Abfrage mit einem ODER für zwei Bedingungen doppelt so schnell:

SELECT LITSTG.*
FROM LITSTG LEFT JOIN Personen_Zuordnungen ON LITSTG.IDENT =
Personen_Zuordnungen.Ident
WHERE ((Personen_Zuordnungen.Personen="Klumpp, Dieter") OR
Personen_Zuordnungen.Personen="Bonnet, Petra");

Aber Vorsicht ist geboten, wenn negative Formulierungen (NOT IN) erwünscht sind. Das führt zu teilweise unzumutbar lange andauernden Abfragezeiten.

SELECT LITSTG.* FROM LITSTG WHERE LITSTG.Ident IN
(SELECT DISTINCT Personen_Zuordnungen.Ident FROM Personen_Zuordnungen
WHERE Personen_Zuordnungen.Personen ="Bonnet, Petra"
AND Personen_Zuordnungen.Ident NOT IN
(SELECT Personen_Zuordnungen.Ident FROM Personen_Zuordnungen
WHERE Personen_Zuordnungen.Personen = "Klumpp, Dieter"));

Ein NOT in der zweiten Teilmenge läßt sich noch erleben und liefert exakt die 4 Dokumente von Bonnet, an denen Klumpp nicht beteiligt ist.

Abschließend muß gesagt werden, daß UND-verknüpfte Kriterien auf 1:n-Tabellen angewandt mit Teilmengen bildenden, verschachtelten Unterabfragen formuliert werden müssen. Dies ist auch der Fall, wenn UND und ODER gemischt auftreten.
Bei rein Oder-verknüpften Kriterien ist ein JOIN-verknüpfter Ausdruck vorzuziehen.
Hier ist es wohl in den meisten Fällen ratsam, eine LEFT JOIN-Verknüpfung mit DISTINCTROW-Anweisung zu erstellen, um auch die Masterdatensätze sehen zu können, für die keine Einträge in der 1:n-Tabelle bestehen und dabei aber keine Mehrfachnennungen für die Masterdatensätze zu erhalten, für die mehrere Einträge in der 1:n-Tabelle existieren.

Weiterhin bleiben Unklarheiten bei verknüpften Abfragen

SELECT DISTINCTROW LITSTG.*
FROM LITSTG LEFT JOIN Personen_Zuordnungen ON LITSTG.IDENT =
Personen_Zuordnungen.Ident
WHERE NOT Personen_Zuordnungen.Personen="Klumpp, Dieter"

In wirklich schwierigen Fällen können komplexe Strukturen und ausgefallen Wünsche auch über ZUSAMMENGESETZTE ABFRAGEN gelöst werden. So kann eine Abfrage per übelster IIF-Strukturen virtuelle Felder erzeugen, nach denen in einer zweiten, einfacheren Abfrage sogar sortiert werden kann.

Das folgende Beispiel stützt sich auf eine umfangreiche Abfrage, in der bereits für das virtuelle Feld 'Adresse' massivste Klimmzüge gemacht wurden. In diesem Feld befinden sich leider viele, bis zu 3-zeilige Einträge.

Es werden fast schon quelltextgleich der Carriage-Return-String sowie die Positionen der max. 2 Zeilenumbrüche definiert. Jede weitere IIF-Konstruktion deckt alle Fälle (1-, 2- oder 3-zeilig) ab und liefert jeweils einen gültigen String (Zeichenkette oder '') an eine weitere, virtuelle Feldvariable.

Diese korrekt ausgewerteten Felder Adresse1-3 können dann anstandslos in eine Feld-mehrzeilige Adress-Tabelle übernommen werden.


SELECT Funktion, Name, Adresse, chr$(13) & chr$(10) AS CR,
IIF(Instr(Adresse,CR)>0,Instr(Adresse,CR),0) AS CR1,
IIF(CR1>0 AND Instr(CR1+1,Adresse,CR)>0,Instr(CR1+1,Adresse,CR),0) AS CR2,
IIF(CR1=0 AND CR2=0,Adresse,Left$(Adresse,CR1-1)) AS Adresse1,
IIF(CR1=0 AND CR2=0,"",IIF(CR1>0 AND
CR2=0,Mid$(Adresse,CR1+2),Mid$(Adresse,CR1+2,CR2-CR1-2))) AS Adresse2,
IIF(CR1=0 AND CR2=0,"",IIF(CR1>0 AND CR2=0,"",Mid$(Adresse,CR2+2))) AS Adresse3,
Strasse1 AS Strasse, PLZ1 AS Plz, Ort1 AS Ort, Notiz, Telefon, Telefax, e_mail,
www, Ident
FROM [#NB21_1]
ORDER BY Adresse;


****

Der Sache sind natürlich wieder mal Grenzen gesetzt.

Im vorliegenden Beispiel sollen aus 4 nur zum Teil ausgefüllten Feldern Werte sinnvoll zusammengesetzt werden, um später in ein Bemerkungsfeld übernommen zu werden.

Das ist im Detail schon ein bißchen fieselig, da für alle Kombinationen geprüft werden muß, ob wir es mit leeren oder Werte enthaltenden Paaren zu tun haben, um keine unsinnigen ", , , "-Zeichenketten zu bilden.

Das läßt sich durchaus Excel-like mit IIF-Ausdrücken realisieren.
In diesem Beispiel meide ich das allzu arge Verschachteln von IIF-Ausdrücken innerhalb anderer IIF-Ausdrücke und weise das Ergebnis einer noch halbwegs überschaubaren Zeile einem "virtuellen" Feld zu, auf das ich dann in der nächsten Zeile Bezug nehme.


SELECT
IIF(din_a IS NOT NULL, "Din A " & din_a, NULL) AS Feld1,
IIF(Feld1 IS NOT NULL AND datum IS NOT NULL, Feld1 & ", " & datum, IIF(Feld1 IS NULL, IIF(datum IS NOT NULL, datum, NULL), Feld1)) AS Feld2,
IIF(Feld2 IS NOT NULL AND name IS NOT NULL, Feld2 & ", " & name, IIF(Feld2 IS NULL, IIF(name IS NOT NULL, name, NULL), Feld2)) AS Feld3,
IIF(Feld3 IS NOT NULL AND herkunft IS NOT NULL, Feld3 & ", " & herkunft, IIF(Feld3 IS NULL, IIF(herkunft IS NOT NULL, herkunft, NULL), Feld3)) AS Feld4,
IIF(bemerkung IS NOT NULL AND Feld4 IS NOT NULL, bemerkung & ", " & Feld4, IIF(bemerkung IS NULL, IIF(Feld4 IS NOT NULL, Feld4, NULL), bemerkung)) AS bemerkung_neu
FROM an;


Das Ganze lief der Reihe nach wunderbar, bis ich die fünfte Zeile einfügte und Microsoft Access (2.0) mich mit einem "Abfrage zu komplex" demütigte. Immerhin verwies mich die Hilfe-Dokumentation auf die Möglichkeit, das Problem mittels einer "benutzerdefinierten" Funktion zu lösen ... und genau so sollte es denn auch sein.

Das SQL-Statement vereinfacht sich ganz wunderbar:

SELECT din_a, datum, name, herkunft, bemerkung, Get_AN_Bemerkung (din_a, datum, name, herkunft, bemerkung) as bemerkung_neu FROM an;


und die korrespondierende Visual-Basic-Funktion sieht brav Schulbuben-mäßig so aus:

Function Get_AN_Bemerkung (din_a As Variant, datum As Variant, an_name As Variant, herkunft As Variant, bemerkung As Variant) As Variant

    Dim v1 As Variant, v2 As Variant, v3 As Variant, v4 As Variant, v5 As Variant

    ' Um mit den lediglich deklarierten, aber noch nicht initialisierten Variant-Variablen
    ' in den Vergleichen sauber arbeiten zu können, müssen diese doch allen Ernstes alle
    ' einzeln auf NULL gesetzt werden.

    v1 = Null
    v2 = Null
    v3 = Null
    v4 = Null
    v5 = Null

    If Not IsNull(din_a) Then v1 = "Din A " & din_a Else v1 = Null

    If Not IsNull(v1) And Not IsNull(datum) Then
       v2 = v1 & ", " & datum
    Else
       If IsNull(v1) And Not IsNull(datum) Then v2 = datum
       If Not IsNull(v1) And IsNull(datum) Then v2 = v1
    End If

    If Not IsNull(v2) And Not IsNull(an_name) Then
       v3 = v2 & ", " & an_name
    Else
       If IsNull(v2) And Not IsNull(an_name) Then v3 = an_name
       If Not IsNull(v2) And IsNull(an_name) Then v3 = v2
    End If

    If Not IsNull(v3) And Not IsNull(herkunft) Then
       v4 = v3 & ", " & herkunft
    Else
       If IsNull(v3) And Not IsNull(herkunft) Then v4 = herkunft
       If Not IsNull(v3) And IsNull(herkunft) Then v4 = v3
    End If

    If Not IsNull(v4) And Not IsNull(bemerkung) Then
       v5 = bemerkung & ", " & v4
    Else
       If IsNull(v4) And Not IsNull(bemerkung) Then v5 = bemerkung
       If Not IsNull(v4) And IsNull(bemerkung) Then v5 = v4
    End If

    Get_AN_Bemerkung = v5

End Function

Die Performance einer solchen Kombination von SQL und VB-Code ist geradezu affenartig.
Auf meinem bis Mai 2006 immer noch aktuellen NT4-Server (Intel PII 350 MHz) bauen sich die Datensätze in "NullKommaNichts" auf, der letzte von mehr als 10.000 Datensätzen wird im Zehntelsekunden-Bereich abgefertigt.
Dennoch gab es hier wieder mal einen Rückschlag, der zum Einfügen dieses seltsamen Code-Abschnitts führte, in dem die frisch deklarierten Variant-Variablen erst mal alle brav auf "Null" gesetzt werden.
Dazu mehr unter Variablen, Vergleich Variant.