Karl Donaubauer, Wien
In der Access-FAQ von Karl Donaubauer (www.donkarl.com) finden Sie die meistgestellten Fragen und Anworten zum Thema Microsoft Access. In dieser Beitragsreihe stellt Karl Donaubauer die wichtigsten Einträge im Detail vor und zeigt Ihnen entsprechende Lösungen anhand praxisnaher Beispiele. Im fünften Teil lernen Sie die Lösungen zu den meistgenannten Problemen der Teilnehmer der deutschsprachigen Access-Newsgroups im Zusammenhang mit Abfragen kennen.
Eine recht häufige Aufgabe bei der Arbeit mit Datenbanken ist der Vergleich der Daten zweier Tabellen. Dabei gibt es zwei übliche Szenarien. Im ersten geht es um den Vergleich zweier „gleichrangiger“ Tabellen, die ähnlich aufgebaut sind – zum Beispiel wenn beim Import von externen Daten geprüft werden muss, ob und welche Daten in der bestehenden Tabelle bereits vorhanden beziehungsweise welche Datensätze in der Importtabelle neu sind. Das zweite Szenario ist die 1:n-Beziehung, bei der man feststellen möchte, welche Daten der Mastertabelle in der Detailtabelle bereits vorkommen oder welche noch nicht. Typische Anwendungsfälle sind etwa, aus einer Adresstabelle (Mastertabelle) jene Adressen herauszufinden, die nicht in der Bestelltabelle (Detailtabelle) vorkommen, oder Firmen (Mastertabelle), bei denen es noch keine Ansprechpartner (Detailtabelle) gibt.
Bild 1: Verknüpfungseigenschaften
In beiden Szenarien lässt sich die Aufgabe im Prinzip darauf reduzieren herauszufinden, welche Daten sich in Tabelle A befinden, aber nicht in Tabelle B.
Bild 1 zeigt eine Abfrage aus der Beispiel-Datenbank zu diesem Artikel mit der Firmen- und der Ansprechpartnertabelle.
Wenn die Beziehungen in der Datenbank richtig gesetzt wurden, dann zeigt Access in der Abfrage mit den beteiligten Tabellen automatisch für die Verknüpfungsfelder (hier FirmenId) eine Verknüpfung ohne Pfeil an.
Ohne Beziehungen auf Datenbankebene müssen Sie selber mit der Maus durch Ziehen und Fallenlassen eines Verknüpfungsfeldes über dem anderen eine solche pfeillose Verknüpfungslinie erzeugen. Mit dieser Verknüpfung (INNER JOIN in SQL) bekommt man im Ergebnis nur jene Firmen, für die es bereits Ansprechpartner gibt.
Hinweis
Auf der beiliegenden Heft-CD finden Sie Beispiel-Datenbanken für Access 97 und Access 2000 und höher mit den Quellcodes und Beispielen aus diesem Beitrag.
Durch Doppelklick auf die Beziehungslinie öffnet sich das Dialogfenster „Verknüpfungseigenschaften“. Option 1 ist der bereits angeführte INNER JOIN, Option 2 der LEFT JOIN, Option 3 der RIGHT JOIN. Im Beispiel ist ein LEFT JOIN gefragt, also Pfeil von links, denn es geht im ersten Schritt darum, alle Firmen anzuzeigen, egal, ob es bereits Ansprechpartner gibt oder nicht. Welche Felder aus der Firmentabelle zur Anzeige ausgewählt werden, ist an sich egal. Im Beispiel ist es nur der Firmenname. Der zweite wichtige Schritt – neben der richtigen Einstellung der Verknüpfung – ist die Auswahl des Verknüpfungsfeldes der n-Seite (im Beispiel FirmenId aus der Tabelle tbl_Ansprechpartner) mit dem Kriterium Ist Null.
Als Ergebnis erscheinen nur noch jene Firmennamen, für die es keine Ansprechpartner gibt. Der SQL-Text dieser Abfrage lautet:
SELECT tbl_Firmen.Firma FROM tbl_Firmen LEFT JOIN tbl_AnsprechPartner ON tbl_Firmen.FirmenId = tbl_AnsprechPartner.FirmenId WHERE tbl_AnsprechPartner.FirmenId Is Null;
oder schematisch:
SELECT A.* FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID Is Null
Es gibt verschiedene Methoden, um für die Ergebnis-Datensätze einer Abfrage eine laufende Nummer zu erzeugen. Zwei davon stelle ich hier vor. Eine, die reines SQL verwendet, und eine andere, die auf die Domänenaggregatfunktionen von Access zugreift. Welche der beiden Varianten Sie wählen, sollten Sie vor allem von einem Geschwindigkeitstest mit Ihren konkreten Daten abhängig machen.
In der Beispiel-Datenbank finden Sie eine Tabelle tblArtikel mit den Feldern ArtikelId, Bezeichnung, Stueck und Preis. Im ersten Beispiel soll die Nummerierung nach ArtikelId aufsteigend erfolgen. ArtikelId ist ein Autowertfeld mit den üblichen Lücken durch gelöschte Datensätze. Für die laufende Nummer ist in der Abfrage ein neues Feld notwendig, in dem sich eine Unterabfrage befindet. Der Text im Feld lautet:
RowNum: (SELECT COUNT (*) FROM [tblArtikel] AS Temp WHERE [Temp].[ArtikelId] < [tblArtikel].[ArtikelId])+1
Bild 2: Laufende Nummer über ArtikelId
Das Ergebnis sehen Sie in Bild 2. RowNum ist dabei ein beliebiger Name für das berechnete Feld, Temp ein beliebiger Name für die temporäre Tabelle in der Unterabfrage, die eine Kopie der echten Tabelle darstellt. Der SQL-Text zählt, wie viele Datensätze es in dieser temporären Tabelle gibt, deren ArtikelId kleiner ist als die ArtikelId der echten Tabelle im aktuellen Abfrage-Datensatz. Zu diesem Wert wird dann außerhalb der Unterabfrage noch 1 addiert.
Beispiele: Im ersten Datensatz ist die aktuelle ArtikelId 1. Es gibt 0 Datensätze in der Tabellenkopie mit kleineren Werten für ArtikelId. Der Ausdruck [tblArtikel].[ArtikelId])+1 ergibt also den Wert 1 für das Feld RowNum. Im zweiten Datensatz hat das Feld ArtikelId den Wert 2. Es gibt einen Datensatz mit einem kleineren Wert für das Feld ArtikelId. Der Ausdruck [tblArtikel].[ArtikelId])+1 ergibt also in diesem Fall den Wert 2. Im dritten Datensatz hat das Feld ArtikelId den Wert 5. Es gibt aber nur zwei Datensätze mit kleineren Werten für das Feld ArtikelId, der entsprechende Ausdruck ergibt also den Wert 3. Die Lücke im Feldwert spielt für den neuen Zähler also keine Rolle.
Eine andere Variante ist die Verwendung der DomAnzahl-Funktion (englisch: DCount). In der Beispiel-Datenbank finden Sie jede Abfrage einmal mit SQL- einmal mit DCount-Zähler. Das Prinzip ist hier das gleiche wie bei der SQL-Variante. Das Feld für den laufenden Zähler nach ArtikelId sieht dann jedoch so aus:
RowNum: DomAnzahl("ArtikelId";
"tblArtikel";"ArtikelId < " &
[ArtikelId])+1
Bei gleichen Werten in zwei Datensätzen wird der Rang zweimal vergeben und der nächste ausgelassen. Im folgenden Beispiel gibt es zweimal den Preis 300,00. Um einen Zähler aufgrund der Preise zu erzeugen, muss man die Unterabfrage nur leicht abändern:
RowNum: (SELECT COUNT (*) FROM [tblArtikel] AS Temp WHERE [Temp].[Preis] < [tblArtikel].[Preis] )+1
beziehungsweise:
RowNum: DomAnzahl("Preis";
"tblArtikel";"Preis < " & [Preis])+1
Bild 3 zeigt das Ergebnis mit dem doppelt auftretenden Wert 2 und dem ausgelassenen Wert 3 im Zähler.
Die fortlaufende Nummer entspricht also der Wertung bei Sportereignissen, die auch einer der praktischen Anwendungsfälle für diese Technik ist.
Wenn es eine andere Sortierung oder andere einschränkende Kriterien für die Datensätze gibt, so müssen diese auch in der Unterabfrage beziehungsweise im DomAnzahl-Ausdruck vorgenommen werden. Lautet zum Beispiel das Kriterium in der Preis-Spalte >300, dann muss das Feld für die laufende Nummer so aussehen:
RowNum: (SELECT COUNT (*) FROM [tblArtikel] AS Temp WHERE [Temp].[Preis] > 300 AND [Temp].[Preis] < [tblArtikel].[Preis] )+1
beziehungsweise mit DomAnzahl:
RowNum: DomAnzahl("Preis";"tblArtikel";
"Preis > 300 AND Preis < " & [Preis])+1
Bild 3: Fortlaufende Nummer mit doppelten Werten
Bild 4 zeigt das Ergebnis. Die weggefilterten Datensätze spielen für die laufende Nummer also keine Rolle.
Bild 4: Laufende Nummer mit Kriterien
Nach ähnlichem Muster funktioniert die Berechnung von laufenden Summen in Abfragen. Folgender Ausdruck in einem berechneten Feld summiert die Preise in der Artikeltabelle zeilenweise auf:
LaufSumme: nz([Preis])+
nz(DomSumme("Preis";"tblArtikel";
"ArtikelId < " & [ArtikelId]))
Dabei wird der Preis im aktuellen Datensatz addiert mit der Summe aller Preise, deren ArtikelId kleiner ist als die aktuelle. Die Nz-Funktion sorgt dabei dafür, dass auch in jenen Datensätzen ein Preis erscheint, bei denen einer der Operanden fehlt, also zum Beispiel im ersten Datensatz, in dem die DomSumme noch keinen Wert liefert. Bild 5 zeigt die entsprechende Abfrage aus der Beispiel-Datenbank.
Bild 5: Laufende Summe
Nach all der Ordnung beim vorherigen Thema folgt nun das Gegenteil, nämlich die zufällige Reihenfolge der Datensätze, die sich bei jedem Ausführen der Abfrage ändert. Praktische Anwendungsfälle sind zum Beispiel Testverfahren, Auslosungen oder auch Vokabeltrainer, bei denen sich die Reihenfolge der zu übersetzenden Vokabeln immer wieder ändern soll.
Für die Erzeugung von Zufallszahlen gibt es die VBA-Funktion Rnd() (für Randomize), die in Abfragen ZZG() heißt. Am einfachsten ist die Verwendung mit einem Autowert-Feld. Sie können einer Abfrage einfach ein neues Feld hinzufügen und nach diesem Feld sortieren lassen:
ZZG([NameDesAutowertFeldes])
Bei jedem öffnen der Abfrage wird sich die Reihenfolge ändern. Ein Beispiel dafür finden Sie ebenfalls in der Beispieldatenbank unter dem Namen qryZufaelligeReihenfolge.
Wie man in Access das Alter einer Person korrekt berechnet, habe ich bereits in einem früheren FAQ-Artikel beschrieben.
Eine verwandte, aber anders zu lösende Aufgabe sind Geburtstags- oder Jubiläumslisten. Die konkrete Aufgabenstellung lautet in der Praxis meist herauszufinden, wer in einem bestimmten Zeitraum ab jetzt Geburtstag hat, damit noch entsprechende Vorbereitungen möglich sind.
In der Beispiel-Datenbank gibt es die Abfrage qryGeburtstage, die aus den Geburtsdaten der Tabelle tblAnsprechpartner ermittelt, welcher Ansprechpartner in den nächsten 14 Tagen Geburtstag hat. In einem berechneten Feld GebHeute wird dazu das Geburtsdatum in das aktuelle Jahr transferiert. Der Ausdruck lautet:
GebHeute: DatAdd("jjjj";
nz(DatDiff("jjjj";[GeburtsDatum];
Datum()));[GeburtsDatum])
Die innere Funktion DatDiff() errechnet die Differenz in Jahren zwischen dem Geburtsdatum und dem aktuellen Datum. Die äußere Funktion DatAdd() addiert diese Jahre dann zum Geburtsdatum. Als Ergebnis erhält man Monat und Tag des Geburtsdatums, aber im aktuellen Jahr.
Bei Datumsberechnungen treten häufig Probleme mit Schaltjahren auf. Hier jedoch kümmert sich Access darum. Falls jemand an einem 29. Februar in einem Schaltjahr geboren ist und das aktuelle Jahr kein Schaltjahr ist, wird das Datum im aktuellen Jahr automatisch in den 28. Februar umgewandelt.
Das Kriterium für die Geburtstage in den nächsten 14 Tagen lautet:
Zwischen Datum() Und Datum()+14
Ein weiteres häufiges Problem neben den Schaltjahren ist der Jahreswechsel. Bei der gezeigten Methode werden die Geburtstage in das aktuelle Jahr transferiert. Was aber, wenn Sie am 31. Dezember wissen möchten, wer Anfang Januar Geburtstag hat In diesem Fall hilft ein zweites, sehr ähnliches Feld:
GebFolgeJahr: DatAdd("jjjj";
nz(DatDiff("jjjj";[GeburtsDatum];
Datum()))+1;[GeburtsDatum])
Der einzige Unterschied liegt im Teil „+1“. Damit wird ein Jahr mehr addiert und somit sind die Geburtstage für das nächste Jahr berechnet. Das Kriterium ist das gleiche wie beim Feld für das aktuelle Jahr, wird aber im Abfrage-Entwurf eine Zeile tiefer platziert, denn es ist ein Oder-Kriterium. Bild 6 zeigt diese Anordnung in der Beispiel-Abfrage. Mithilfe dieser Technik können Sie natürlich nicht nur Geburtstage ermitteln, sondern ebenso Mitarbeiter-Dienstjubiläen oder jährlich wiederkehrende Wartungstermine.
Bild 6: Geburtstage ermitteln
In einer Access-Abfrage können Sie bis auf wenige Ausnahmen alle VBA-Funktionen verwenden als seien sie SQL-Befehle. Ebenso können Sie selbst erstellte VBA-Funktionen einsetzen.
Als „übersetzer“ fungiert dabei der so genannte „Expression Service“ (ES), eine Komponente von Access, genauer seiner Datenbank-Engine JET.
Im Gegensatz dazu ist es nicht möglich, Variablen, die Sie irgendwo im VBA-Code deklariert haben, direkt in Abfragen einzusetzen, obwohl das bei vielen Aufgaben praktisch wäre, zum Beispiel in einem Kriterium. Der ES hat keinen Zugriff auf Variablen, er kann nur mit Funktionen umgehen. Darin liegt aber auch schon die Lösung für das Variablen-Problem.
Deklarieren Sie die Variable global, das heißt im Deklarationsbereich eines Standardmoduls. Dann reicht es – ebenfalls in einem Standardmodul -, eine Funktion zu schreiben, die nichts anderes macht, als den Wert der Variablen zurückzugeben. Ein Beispiel für eine Textvariable:
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo