Autor: Losch

DoCmd.OpenQuery „NameAbfrage“, acViewNormal

Frage: Ich möchte in einer Abfrage nur die letzten n (zum Beispiel die letzten 10) Datensätze angezeigt bekommen. Wie kann ich das erreichen?

Antwort: Das geht nur, wenn Sie die Datensätze nach irgendeinem Feld in umgekehrter Reihenfolge sortieren können. Beispiel Bestellungen: Die Abfrage wird „Absteigend“ nach dem Bestelldatum sortiert, sodass die ältesten Bestellungen zuerst aufgelistet werden.

Dann können Sie in den Abfrage-Eigenschaften (Rechtsklick in den freien, grauen Bereich des Abfrageentwurfs) „Spitzenwerte“ auf „10“ einstellen und erhalten so nur die zehn ältesten Bestellungen. Oder am Beispiel einer Lagerverwaltung: Abfrage aufsteigend nach dem Lagerbestand sortieren und „Spitzenwerte“ auf „25“ einstellen. Damit erhalten Sie eine Liste der 25 Artikel mit dem höchsten Lagerbestand.

Problem

Du hast ein Tabellenfeld in dem sich mehrteilige Inhalte befinden, z.B. Vor- und Nachname, Straße und Hausnummer etc. Zur weiteren Verarbeitung möchtest du diese Daten in jeweils eigene Felder auftrennen.

Lösung

Grundsätzlich solltest du darauf achten, in einem Tabellenfeld nur Werte zu speichern, die nicht weiter zerlegbar sind (–> 1. Normalform). Späteres Verketten ist stets einfacher als nächträgliches Trennen.

Wenn es brauchbare Trennungskriterien gibt z.B. trennende Leerzeichen oder Kommas, kannst du mit Aktualisierungsabfragen oder VBA-Funktionen einen Großteil der Arbeit automatisiert erledigen. Ein Beispiel:

Angenommen im Feld „Kunde“ befinden sich Vor- und Nachname getrennt durch 1 Leerzeichen (z.B. „Bill Gates“).
1. Lege in der Tabelle zwei neue Felder an und nenne sie „Vorname“ und „Nachname“.

2. Erzeuge auf Basis der Tabelle eine Aktualisierungsabfrage.

3. Schreibe im Abfrageentwurf in der Zeile „Aktualisieren“ beim Feld „Vorname“:
Left([Kunde];InStr([Kunde];“ „)-1)
beim Feld „Nachname“:
Mid([Kunde];InStr([Kunde];“ „)+1)

Eine andere häufige Variante ist Nachname vor dem Vornamen durch Komma+Leerzeichen gertrennt („Gates, Bill“). Hier lauten die Ausdrücke:
Left([Kunde];InStr([Kunde];“,“)-1)
Mid([Kunde];InStr([Kunde];“,“)+2)
Weitere Info gibt’s im VBA-Editor mit <F1> zu den verwendeten String-Funktionen.

4. Führe die Abfrage aus   und sei glücklich mit dem Ergebnis bzw…

Bei solchen Aktionen können Fehler passieren, weil z.B. jmd. mehrere/mehrteilige Vor- oder Nachnamen hat oder das Format nicht immer eingehalten wurde. Daher solltest du die Ergebnisse genau prüfen, z.B. die Ausdrücke zuerst in einer Auswahlabfrage als berechnete Felder testen und anpassen, oder die Daten händisch nachbearbeiten.

Für Angebote, Aufträge, Lieferscheine oder Rechnungen verwenden Sie in Tabellen häufig ein Feld, in das eine Ordnungsnummer wie beispielsweise „AB0156“ für ein Angebot, „RG0156“ für die dazugehörige Rechnung oder „KD1023“ für eine Kundenadresse erfasst wird. Dabei passiert es regelmäßig, dass die Zusätze wie „AB“, „LS“, „RG“ oder „KD“ vergessen und die zusammengehörenden Daten zum Beispiel über eine Abfrage nicht mehr korrekt verknüpft werden können. Es wäre etwas mühsam, alle Datensätze manuell durchzugehen und die fehlenden Zusätze zu ergänzen.

Wesentlich effektiver ist der Einsatz einer Aktualisierungsabfrage. Nehmen wir beispielsweise an, Sie setzen eine Tabelle „Kunden“ mit dem Feld „KundenNr“ ein und möchten sicherstellen, dass in jedem Datensatz das Kürzel „KD“, gefolgt von einer vierstelligen Kundennummer, gespeichert wird. Gehen Sie dazu folgendermaßen vor:

1. Erstellen Sie eine neue Abfrage.
2. Wählen Sie in der Tabellenauswahl die Tabelle „Kunden“ mit einem Doppelklick aus und klicken Sie dann auf SCHLIEßEN.
3. Wählen Sie das Menü ABFRAGEAKTUALISIERUNGSABFRAGE an.
4. Klicken Sie zweimal auf das Feld „KundenNr“, um es im Abfrageentwurf als Spalte hinzuzufügen.
   
   
5. Für die erste Spalte geben Sie unter „Kriterien“ die folgende Anweisung ein: Links$([KundenNr];2)<>“KD“
6. Für die zweite Spalte geben Sie unter „Aktualisieren“ folgende Anweisung ein: „KD“ &Format$([KundenNr];“0000″)
7. Wählen Sie das Menü ABFRAGEAUSFÜHREN an.

Diese Abfrage wählt alle Datensätze aus, die noch nicht über den Zusatz „KD“ verfügen und ergänzt ihn gegebenenfalls.

Möchten Sie Zusätze aus Ordnungsnummern entfernen, also nur noch die reinen Zahlen/Nummern verwenden, hilft eine ähnliche Aktualisierungsabfrage.

Als „Kriterium“ geben Sie ein:

Links$([KundenNr];2)

Unter „Aktualisieren“ steht dann die folgende Anweisung:

Wert(TeilStr$ ([KundenNr];3))

Eine solche Abfrage selektiert alle Datensätze, die den Zusatz „KD“ haben und setzt den Feldinhalt auf die ab Position „3“ ausgelesene Nummer.

Klicken Sie in einer Abfrage im Entwurfmodus mit der rechten Maustaste auf einer Spalte und wählen Sie Eigenschaften.

Datenbank

Wenn Mitarbeiter Urlaub machen, krank sind oder eine Schulung besuchen, erfassen Sie diese Fehltage in einer Tabelle. Die dazu benötigten Abfragen sind auf den ersten Blick schnell erstellt, aber bei genauerer Betrachtung wird es durch die Berücksichtigung von Wochenenden und Feiertagen etwas komplizierter. Im Folgenden zeigen wir effektive Lösungen für diese Problemstellung.

Damit Sie die folgenden Erläuterungen einfacher nachvollziehen können, haben wir eine Personalverwaltung in Form des Formulars „Personal“ vorbereitet, das die Erfassung von Basisdaten, persönlichen Daten und Fehltagen auf verschiedenen Registerkarten erlaubt.

Laden Sie zunächst die Beispieldatenbank herunter und installieren Sie sie in ein beliebiges Verzeichnis auf Ihrer Festplatte.

Anschließend können Sie die Datenbank öffnen und die Personalverwaltung nutzen. Für die einzelnen Mitarbeiter sind bereits einige Fehltage mit unterschiedlichen Gründen eingetragen, sodass wir uns gleich mit den Abfragen auseinandersetzen können. Technische Details zum Aufbau der Personalverwaltung finden Sie bei Bedarf im Dokument „Hinweise zur Personalverwaltung.doc“ im Installationsverzeichnis der Beispieldatenbank.

Zunächst soll eine Abfrage erstellt werden, die eine allgemeine Übersicht liefert:

1.  Erstellen Sie eine neue Auswahlabfrage auf Basis der Tabellen „Personal“ und „Fehltage“. Access richtet automatisch eine Verknüpfung über das Feld „Personal- Nr“ ein.
2. Schalten Sie die Abfrage per Klick auf das Symbol SUMMEN in eine Gruppierungsabfrage um.
3. Geben Sie in die Zeile „Feld:“ der ersten Spalte den folgenden Ausdruck ein:NV: [Nachname] & „, “ & [Vorname]

   Dieser Ausdruck fasst die Einzelfelder „Nachname“ und „Vorname“ in einem Feld „NV“ getrennt mit „,<Leerzeichen>“ zusammen und ergibt beispielsweise „Müller, Johannes“.

4. Stellen Sie in der Zeile „Funktion:“ den Eintrag „Gruppierung“ und die „Sortierung“ auf „Absteigend“ ein.
5. Fügen Sie das Feld „Grund“ per Doppelklick in die Feldliste zum Abfrageentwurf hinzu, stellen Sie „Funktion:“ auf „Gruppierung“ und „Sortierung“ auf „Aufsteigend“ ein. Bitte beachten Sie, dass die Sortierung auf Basis des Byte-Wertes „0“ bis „255“ des Feldes „Grund“ erfolgt und sich somit je nach Werteliste nicht unbedingt eine alphabetische Sortierung ergibt!
6. Geben Sie in die Zeile „Feld:“ der nächsten Spalte den folgenden Ausdruck ein und stellen Sie unter „Funktion:“ entsprechend „Ausdruck“ ein:Fehltage:Summe(AnzahlArbeitstage([FehltageVon];[FehltageBis]))

   Dieser Ausdruck ruft für jeden Datensatz die Funktion „AnzahlArbeitstage()“ mit dem aktuellen Zeitraum gemäß „FehltageVon“ und „Fehltage- Bis“ auf. Das Ergebnis ist die reine Anzahl an Arbeitstagen.

   

7. Speichern Sie die Abfrage und lassen Sie sie testweise ausführen.

Das Ergebnis ist eine Auflistung der Fehltage, gruppiert nach Mitarbeiter und Grund der Abwesenheit.

Wenn Sie eine nach dem Abwesenheitsgrund gegliederte Übersicht benötigen, schieben Sie einfach die Spalte „Grund“ vor die Spalte „NV“. Die Abfrage zeigt dann zunächst beispielsweise alle Fehltage „Urlaub“ der einzelnen Mitarbeiter an, anschließend alle Fehltage mit dem Grund „Krank“, „Schulung“ etc.

Möchten Sie eine alphabetische Sortierung des Grundes vornehmen, geben Sie in die Zeile „Feld:“ des Feldes „Grund“ den folgenden Ausdruck ein und schalten die gewünschte Sortierung auf „Auf-/Absteigend“:

G: Wahl([Grund];“Urlaub“;“Schulung“;“Krank“;“Freier Tag“;“Unentschuldigt“)

Eine weitere Anforderung bei der Auswertung von Fehltagen ist eine Übersicht der Fehltage für einen bestimmten Grund und einen bestimmten Zeitraum. Das Problem dabei ist der Auswertungszeitraum: Wird dieser z. B. mit „Von: 1.4.2011“ und „Bis: 30.6.2011“ auf das zweite Quartal festgelegt und ein Mitarbeiter war vom 22.3.2011 bis 9.4.2011 krank oder hatte vom 21.6.2011 bis 9.7.2011 Urlaub, dürfen nur die in den Auswertungszeitraum fallenden Tage berücksichtigt werden.

Um das sicherzustellen, nutzen Sie in der Abfrage als Kriterium die Funktion „FehltageInZeitraum()“:

Public Function FehltageInZeitraum(ZeitraumVon As Date, _ZeitraumBis As Date, _FehltageVon As Date, _FehltageBis As Date) As Integer

Dieser Funktion (siehe Modul „modWerk-Arbeitstage“ in der Beispieldatenbank) werden als Parameter Start und Ende des Auswertungszeitraumes sowie Start und Ende der Fehltage übergeben. Das Ergebnis ist die Anzahl der Arbeitstage, bereinigt um Wochenenden und Feiertage, die aus dem Zeitraum „Fehltage“ in den Zeit – raum „Auswertung“ fallen.

Um beispielsweise eine Abfrage zu erstellen, die alle Fehltage „Krank“ für einen per Parameter festzulegenden Zeitraum auflistet, gehen Sie folgendermaßen vor:

1.  Erstellen Sie eine neue Abfrage auf Basis der Tabellen „Personal“ und „Fehltage“ und schalten Sie sie auf eine Gruppierungsabfrage um.
2. Richten Sie zwei Parameter „Zeitraum von“ und „Zeitraum bis“ vom Datentyp „Datum/Zeit“ ein.
3. Fügen Sie das Feld „Grund“ per Doppelklick in die Feldliste zum Abfrageentwurf hinzu, stellen Sie „Funktion“ auf „Gruppierung“ ein und geben Sie in die Zeile „Kriterien“ den folgenden Ausdruck ein:=3

   Damit werden nur die Datensätze berücksichtigt, die im Feld „Grund“ den Inhalt „3/Krank“ haben. Für die Auswertung nach anderen Gründen geben Sie hier den entsprechenden Byte-Wert gemäß Werteliste an, also z. B. „1“ für „Urlaub“ oder „4“ für „Freier Tag“.

4. Geben Sie in der nächsten Spalte in die Zeile „Feld:“ den folgenden Ausdruck ein und stellen Sie „Funktion:“ auf „Gruppierung“ sowie die „Sortierung“ auf „Aufsteigend“ ein:NV: [Nachname] & „, “ & [Vorname]

   Damit fassen wir wieder Nach- und Vorname in einem Feld zusammen und lassen danach alphabetisch sortieren.

5. Geben Sie in der nächsten Spalte in die Zeile „Feld:“ den folgenden Ausdruck ein und stellen Sie „Funktion“ auf „Summe“ um:Fehltage:FehltageInZeitraum([Zeitraumvon];[Zeitraum bis];[FehltageVon];[FehltageBis])

   Weiterhin geben Sie für das Feld als Kriterium folgenden Ausdruck ein:

   >0

   Dieser Ausdruck ermittelt für jeden Datensatz die Anzahl der Fehltage, die möglicherweise in den per Parameter festgelegten Auswertungszeitraum fallen. Diese Anzahl von Tagen werden per „Summe“ pro Mitarbeiter addiert. Das Kriterium „>0“ stellt sicher, dass Mitarbeiter ohne Fehltage ausgeblendet werden.

6. Speichern Sie die Abfrage und lassen Sie sie testweise ausführen.

 

Sie erhalten nun eine Liste für den jeweiligen Grund, wie beispielsweise „Krank“, mit den Namen und Fehltagen der betreffenden Mitarbeiter.

In der Beispieldatenbank finden Sie die Abfragen „Allgemeine Übersicht Fehltage, Sortierung Name“, „Allgemeine Übersicht Fehltage, Sortierung Grund“ und „Übersicht Fehltage, Grund und Zeitraum“, die diese Technik demonstrieren.

 

So werden Wochenenden und Feiertage in Abfragen berücksichtigt

Um Fehltage der Art „Krank vom 2.8.2010 bis 20.8.2010“ korrekt auswerten zu können, müssen Wochenenden und Feiertage berücksichtigt werden. Es ist also nicht möglich, einfach per „DatDiff(„d“;“[Von]“;“[Bis])“ eine Anzahl von Tagen zu berechnen. Das Ergebnis „19“ wäre falsch, da noch Wochenenden sowie eventuell in den Zeitraum fallende Feiertage abgezogen werden müssen.

In der Beispieldatenbank zu diesem Beitrag haben wir ein paar Funktionen für Berechnungen von Feier- und Arbeitstagen zusammengefasst. Sie finden hier die Tabelle „Feiertage“ sowie die Module „modWerkArbeitstage“ und „modFeiertage“. Die für eine korrekte Auswertung von Fehltagen wichtigste Funktion ist „AnzahlArbeitstage()“, die aus einem Zeitraum alle Wochenenden und Feiertage herausrechnet, sodass nur die tatsächlich zu berücksichtigenden Arbeitstage als Ergebnis geliefert werden. Wenn Sie diese Funktion in den Abfragen Ihrer eigenen Datenbanken verwenden möchten, sind die oben genannten Objekte in Ihre Datenbank zu übertragen. Möchten Sie die Tabelle „Feiertage“ zum Jahreswechsel um Feiertage des neuen Jahres erweitern, wechseln Sie in die VBA-Entwicklungsumgebung und geben im Direktbereich die folgende Anweisung ein:

FeiertageInTabelle „Feiertage“, „FTBezeichnung“, „FTDatum“, <Jahr>, „FTFaktor“

„<Jahr>“ ersetzen Sie dabei durch das betreffende Jahr, also 2013, 2014, 2015 usw. Die Tabelle wird dann mit den Feiertagen gefüllt. Individuelle, bundesland- oder kantonsspezifische Feiertage sind manuell nachzutragen.

 

 

 

Hinweise zur Lösung „Personalverwaltung“

Basis ist die Haupttabelle „Personal“, die über die Personalnummer mit einer Detailtabelle „Fehltage“ verknüpft ist. In dieser Detailtabelle werden pro Mitarbeiter Zeiträume „Von/Bis“ mit einem Grund wie „Urlaub“, „Krank“, „Schulung“ usw. gespeichert. Das Feld „Grund“ speichert einen Bytewert von „0“ bis „255“. Über „Nachschlagen“ ist ein Kombinationsfeld mit einer Werteliste als Basis definiert, dass die Auswahl vereinfacht. Vorgegeben sind folgende Werte bzw. Gründe, die Sie ggf. an eigene Anforderungen anpassen können:

 1;Urlaub;2;Schulung;3;Krank;4;Freier Tag;5;Unentschuldigt

Der Standardwert für das Feld ist „0“, steht somit für „Nicht angegeben“ und wird per Gültigkeitsregel „<>0“ überprüft. Würde man hier als Standardwert beispielsweise „1“ für „Urlaub“ angeben, bestände die Gefahr, dass die Einstellung bei einer Neuerfassung unverändert übernommen und somit falsch gespeichert werden würde.

Wichtig: Wenn Sie die Werteliste des Feldes „Grund“ im Tabellenentwurf unter „Nachschlagen“ ändern, müssen Sie auch die Werteliste des Feldes „Grund“ im Unterformular „UF Fehltage“ anpassen! Access übernimmt die Änderungen aus dem Tabellenentwurf nicht automatisch in ggf. davon betroffene Steuerelemente in Formularen oder anderen Datenbankobjekten!

Für die Erfassung und Darstellung der Fehltage auf dem Register „Fehltage“ wird das Unterformular „UF Fehltage“ verwendet, das über das Feld „Personal-Nr“ mit dem Hauptformular „Personal“ verknüpft ist. Das Unterformular basiert auf der Abfrage „AF Fehltage“, die die Felder der Detailtabelle „Fehltage“ sortiert nach dem Feld „FehltageVon“ liefert. Dadurch ist sichergestellt, dass zum Beispiel ein nachträglich erfasster freier Tag oder ein in der Zukunft liegender Urlaub auch an der chronologisch korrekten Position im Unterformular angezeigt wird.

Rund die Hälfte der in durchschnittlich komplexen Datenbankanwendungen eingesetzten Abfragen verwenden Funktionen und Operatoren. Da sie meistens auch einen Geschwindigkeitsverlust bei der Ausführung der Abfragen bedeuten, sollten Sie genau prüfen, ob der Einsatz tatsächlich notwendig ist.

Denn wie in vielen anderen Bereichen gilt auch hier: Viele Wege führen nach Rom! Die einen sind etwas länger, aber bequem, andere eher kurz und dafür umständlich. Bei der Analyse von Abfragen wird das besonders deutlich. Oftmals entsteht der Eindruck, der Entwickler der Abfrage war nur daraufbedacht, sie in möglichst kurzer Zeit und mit möglichst wenig Aufwand zu erstellen, ohne nach effektiveren Alternativen zu suchen.

Wir stellen im Folgen den eine Reihe regelmäßig verwendeter Funktionen und Operatoren vor und zeigen Alternativen, die nicht selten schneller ausgeführt werden können.

Zwischen-Operator statt „>“, „<“ und „>=“, „<=“

Um Datensätze abzufragen, die in einen vorgegebenen Wertebereich fallen, verwenden Sie meistens die Operatoren „>“ und „<“ oder „>=“ und „<=“.

Um beispielsweise alle Adressen aus dem Postleitzahlbereich 51000 zu selektieren, wer den Konstruktionen wie die folgende bei einem Feld „PLZ“ eingesetzt:

>=“51000″ Und <= „51999“

Oder

>“50999″ Und < „52000“

Für die Prüfung solcher Wertebereiche mit zwei Grenzwerten nutzen Sie besser die weitaus effektivere Konstruktion mit „Zwischen“ und „Und“:

Zwischen „51000“ Und „51999“

„In()“-Funktion statt „Oder“-Operator

Wenn Sie Datensätze auswählen, die in einem bestimmten Feld einen von mehreren möglichen Werten enthalten, kommt meistens der „Oder“-Operator zum Einsatz.

Also zum Beispiel:

„Hamburg“ Oder „Köln“ Oder „Stuttgart

Eine wenig bekannte, aber sehr effektive Alternative ist die „In()“-Funktion. Hier übergeben Sie einfach die gewünschten Werte, getrennt durch ein Semikolon, als Argument:

In(„Hamburg“;“Köln“;“Stuttgart“)

Diese Variante ist einfacher einzugeben und auch übersichtlicher, vor allem bei einer großen Anzahl von Werten. Aber auch hier gibt es Grenzen. Wenn Sie 20 oder mehr Werte vorgeben möchten, sollten Sie diese besser in einer Tabelle verwalten, die dann mit der Abfrage verknüpft wird.

VBA-Funktion anstelle von „IIF()“

In Abfragen und SQL-Unterabfragen wird häufig die Funktion „If(Bedingung, True-Part, False-Part)“ bzw. „Wenn(Bedingung, True-Part, False-Part)“ verwendet, um abhängig von einem anderen Feld einen neuen Feldinhalt zusammenzusetzen:

„AnrBrief: IIF([AnrAdr]=“Herr“; „Sehr geehrter Herr“; „Sehr geehrte Frau“)

Hier wird abhängig vom Feldinhalt „AnrAdr“ die Anrede für den Brief mit „Sehr geehrter Herr“ oder „Sehr geehrte Frau“ generiert. Nachteil dieser Konstruktion ist, dass Access hier zunächst zwei Zeichenketten erzeugt: eine für den True- und eine für den False-Part. Erst dann wird eine der Zeichenketten abhängig von der Bedingung als Feldinhalt zugewiesen.

Schneller und flexibler ist der Einsatz einer benutzerdefinierten VBA-Funktion „AnrFuerBrief ()“:

Function AnrFuerBrief (strAnrAdr as String) As String
If strAnrAdr = „Herr“ Then
AnrFuerBrief = „Sehr geehrter Herr“
Else
if strAnrAdr = „Frau“ Then
AnrFuerBrief = „Sehr geehrte Frau“
Else
AnrFuerBrief = „Sehr geehrte Damen und Herren“
End If

In dieser Funktion wird zunächst die Bedingung geprüft und erst dann die benötigte Zeichenkette zusammengesetzt.

Außerdem erfolgt hier noch eine weitere Unterscheidung für den Fall, dass eine Anrede weder „Herr“ noch „Frau“ ist, und dementsprechend eine allgemeine Anrede wie „Sehr geehrte Damen und Herren“ für den Brief als Ergebnis geliefert werden soll.

„Links()“ und „Rechts()“ statt „Wie“

In Abfragekriterien oder SQL-Unterabfragen nutzen Sie regelmäßig die Funktion „Wie“ oder „Like“, um Datensätze zu selektieren, die in einem Feld gemeinsame Inhalte aufweisen.

Zum Beispiel:
Sie möchten alle Datensätze selektieren, bei denen das Feld „Ort“ Inhalte wie „Hamburg-Bahrenfeld“, „Hamburg-Blankenese“, „Hamburg-Winterhude“ usw. aufweist.

Dazu verwenden Sie zum Beispiel beim Feld „Ort“ das folgende Kriterium:

Wie „*Hamburg*“

Damit werden alle Datensätze selektiert, bei denen im Feld „Ort“ irgendwo die Zeichenkette „Hamburg“ vorkommt. Durch die Sternchen am Anfang und Ende teilen Sie Access mit, dass es keine Rolle spielt, welche Zeichen vor und nach dem betreffenden Begriff vorhanden sind. Prüfungen dieser Art benötigen viel Zeit, da Access jeden einzelne Feldinhalt Zeichen für Zeichen untersuchen muss.

Wenn, wie in diesem Beispiel, „Hamburg“ immer am Feldanfang steht, ist das folgende Kriterium wesentlich schneller zu prüfen:

Links([Ort];7)= „Hamburg“

Access muss hier nur die ersten sieben Zeichen aus dem Feld „Ort“ mit dem Begriff „Hamburg“ vergleichen. Da es dabei bereits abbricht und „False“ erkennt, wenn das erste Zeichen ungleich „H“ ist, können solche Prüfungen blitzschnell ausgeführt werden. Ähnliches gilt für Felder, deren gemeinsame Inhalte immer am Ende stehen, wie zum Beispiel in den Berufsbezeichnungen „Verkaufs-Assistent“, „Vertriebs-Trainee“, „Vorstands-Sekretärin“. Um eine Liste aller Assistenten zu erhalten, verwenden Sie nicht

Wie „*Assistent*“

sondern prüfen die entsprechende Anzahl von Zeichen rechts im Feldinhalt:

Rechts([Berufsbezeichnung];9) = „Assistent“

Feste Formatierung anstelle von „Format()“

Oftmals ist im Tabellenentwurf für ein Feld beispielsweise der Datentyp „Zahl“ mit Feldgröße „Double“ eingetragen, die Abfrage soll das Ergebnis aber im Format „Währung“ liefern.

Dazu wird häufig folgende Konstruktion eingesetzt:

BetragW: Format([Betrag];“Currency“) oder BetragW: Format([Betrag];“###,###,###.00″) & „Euro“

Access muss damit für jeden Datensatz eine Konvertierung per VBA-Funktion vornehmen.

Wesentlich schneller lässt sich das durch die interne Formatierung der Abfrage erledigen: Klicken Sie mit der rechten Maustaste in die Spalte des Feldes, dessen Format geändert werden soll, und wählen Sie EIGENSCHAFTEN im Kontextmenü an.

Im nachfolgenden Eigenschaftenfenster stellen Sie unter „Format“ den Eintrag „Währung“ oder „Euro“ ein.

Die Ergebnisse der betreffenden Spalte werden dann zukünftig im gewünschten Format ohne zeitraubende Aufrufe von VBA-Funktionen ausgegeben.

In einer Abfrage kann man ein Datumsfeld so einstellen, dass nur der Tag, der Monat, oder das Jahr angezeigt wird.

Wenn es ein Datumsfeld mit dem Namen Geburtsdatum gibt und nur der Monat angezeigt werden soll, lautet der Befehl:

Ausdruck: Monat([Geburtsdatum])

---

Für den Tag lautet der Befehl:

Ausdruck: Tag([Geburtsdatum])

---

Für das Jahr lautet der Befehl:

Ausdruck: Jahr([Geburtsdatum])

Wenn Sie Datenbanken über einen längeren Zeitraum einsetzen, befinden sich in den Tabellen viele Datensätze, die für die tägliche Arbeit eigentlich nicht mehr benötigt werden. Diese „Datensatzleichen“ bremsen Ihre Datenbanken spürbar aus: Es dauert ewig, bis Formulare oder Berichte geöffnet, Abfragen ausgeführt oder eine Komprimierung abgeschlossen ist. Das alles kostet viel Zeit, die Sie sparen können, wenn Sie die nicht mehr direkt benötigten Datensätze archivieren und in externe Datenbanken auslagern. Wir zeigen Ihnen, wie Sie eine vollautomatische Archivierungslösung Schritt für Schritt individuell für Ihre Datenbanken entwickeln.

Wenn Sie nicht mehr benötigte Datensätze auslagern möchten, können Sie entweder zusätzliche Tabellen in der Datenbank nutzen, mit der Sie täglich arbeiten, oder Sie speichern die Datensätze in einer externen Datenbank. Beide Methoden haben den Vorteil, dass Formulare und Berichte wieder schneller geöffnet und Abfragen schneller ausgeführt werden. Bei der Speicherung in internen Tabellen befinden sich Bestandsdaten und Archiv in einer einzigen Datei, was die Handhabung und Datensicherung erleichtert. Der Nachteil ist jedoch, dass die Datenbank-Datei dadurch zusätzlich belastet wird und sich zum Beispiel Komprimierungszeiten nicht verringern, sondern eher erhöhen. Da archivierte Daten in der Regel nur selten eingesehen werden, empfiehlt es sich daher, die Archivierung in Tabellen in einer externen Datenbank abzulegen. Diese Tabellen können Sie dann in die Ursprungs- Datenbank einbinden, sodass sie bei Bedarf jederzeit zur Verfügung stehen.

Wenn Sie sich entschlossen haben, Ihre Datenbanken von unnötigem Ballast zu befreien, beginnen Sie zunächst mit dem Anlegen der Archiv-Datenbank:

1. Wählen Sie das Menü DATEI-NEU an und legen Sie eine neue, leere Datenbank „Archiv Datenbankname“ an. „Datenbankname“ ersetzen Sie dabei durch den Namen der Datenbank, aus der Daten archiviert werden sollen. Anhand eines Dateinamens ist dann später sofort erkennbar, welchen Inhalt die Datenbank hat.

2. Starten Sie eine zweite Instanz von Access und öffnen Sie dort die Datenbank, aus der Daten archiviert werden sollen.

3. Ordnen Sie beide Fenster nebeneinander an, indem Sie die Symbole in der Windows-Taskleiste mit gedrückter Strg-Taste markieren, mit der rechten Maustaste auf ein markiertes Symbol klicken und NEBENEINANDER im Kontextmenü anwählen.

4. Markieren Sie nun im Datenbankfenster der Ursprungs-Datenbank die Tabelle, aus der Datensätze archiviert werden sollen.

5. Klicken Sie auf das Symbol KOPIEREN, drücken Sie Strg+C oder wählen Sie das Menü BEARBEITEN-KOPIEREN an.

6. Wechseln Sie in die Archiv-Datenbank.

7. Klicken Sie auf das Symbol EINFÜGEN, drücken Sie Strg+V oder wählen Sie das Menü BEARBEITEN-EINFÜGEN an.

8. Access zeigt nun den Dialog „Tabelle einfügen als“ an. Geben Sie hier im Feld TABELLENNAME den Namen der jeweiligen Tabelle mit dem Zusatz Archiv ein, wählen Sie die Option NUR STRUKTUR und klicken Sie auf OK. Damit haben Sie eine Tabelle erstellt, die mit der Ursprungstabelle absolut identisch ist, aber noch keine Datensätze enthält.

9. Sollte in der Tabelle ein Feld vom Typ „AutoWert“ verwendet werden, öffnen Sie die Tabelle im Entwurfsmodus und ändern den Feldtyp von „AutoWert“ auf „Zahl/Long Integer“. Dieser Schritt ist notwendig, um einerseits die bereits vorhandenen Werte der „AutoWert“- Felder zu übernehmen. Außerdem sollen natürlich Fehlermeldungen bei der späteren Übertragung von Datensätzen vermieden werden.

10. Wiederholen Sie die Schritte 4. bis 9. für weitere Tabellen, aus denen gegebenenfalls Datensätze archiviert werden sollen. Am Ende verfügen Sie über eine Archiv- Datenbank, die leere Tabellen mit der gleichen Struktur enthält, wie Ihre Ursprungs- Datenbank.

Um auf diese Tabellen aus Ihrer Ursprungs- Datenbank zugreifen zu können, gehen Sie wie folgt vor:

1. Wechseln Sie ins Datenbankfenster der Ursprungs-Datenbank.

2. Wählen Sie das Menü DATEI-EXTERNE DATEN-TABELLEN VERKNÜPFEN an.

3. Lokalisieren Sie im nachfolgenden Dialog die soeben erstellte Archiv- Datenbank und wählen Sie diese per Doppelklick aus.

4. Im folgenden Dialog „Tabellen verknüpfen“ klicken Sie auf ALLE AUSWÄHLEN und bestätigen dann mit OK.

In Ihrer Ursprungs-Datenbank finden Sie daraufhin im Datenbankfenster eine Reihe neuer Symbole vor, die Sie an einem schwarzen Pfeil vor dem Symbol als verknüpfte Tabellen und am Zusatz Archiv als Tabellen für die Archivierung erkennen können.

Damit sind nun alle Voraussetzungen geschaffen, um Datensätze aus der Ursprungs- Datenbank in die Archiv-Datenbank sichern zu können. Für die eigentliche Übertragung der Datensätze stehen Ihnen verschiedene Möglichkeiten zurVerfügung::

– Kombinationen aus Anfügeund Löschabfragen
– SQL-Anweisungen
– VBA-Routinen

---

Archivieren mit Anfüge- und Löschabfragen

Am einfachsten und übersichtlichsten sind Kombinationen aus Anfüge- und Löschabfragen, sodass wir uns mit dieser Möglichkeit im Folgenden anhand einiger praktischer Beispiele etwas detaillierter beschäftigen. Im ersten Schritt erstellen Sie zunächst eine Anfügeabfrage:

1. Wechseln Sie in den Bereich „Abfragen“, klicken Sie auf die Schaltfläche NEU und doppelklicken Sie auf ENTWURFSANSICHT.

2. Doppelklicken Sie im folgenden Dialog auf die Tabelle, aus der Datensätze archiviert werden sollen und bestätigen Sie mit SCHLIEßEN.

3. Doppelklicken Sie im Abfrageentwurf in der Feldliste auf den Eintrag mit dem Sternchen, um alle Felder aufzunehmen.

4. Wählen Sie das Menü ABFRAGE-ANFÜGEABFRAGE an, markieren Sie in der Liste TABELLENNAME die dazugehörige Archiv-Tabelle und klicken Sie auf OK.

Die Abfrage ist nun so weit vorbereitet, dass Sie Datensätze aus der Ursprungs- Datenbank in die Archiv-Datenbank übertragen können. Allerdings sollten Sie die Abfrage jetzt nicht ausführen, denn sie würde alle Datensätze in die Archiv-Tabelle übertragen. In der Regel soll die Archivierung nur für bestimmte Datensätze erfolgen. Sie müssen also über ein Kriterium festlegen, welche Datensätze übertragen werden sollen. Die Bedingungen hierfür hängen von der jeweiligen Datenbank und Ihren jeweiligen Anforderungen ab. Als Beispiel wollen wir einmal die Artikel-Stammdaten aus der bei Access mitgelieferten Datenbank „Nordwind“ verwenden. Hier gibt es ein Feld „Auslaufartikel“ (Ja/Nein) und ein Feld „Lagerbestand“ (Zahl). Wenn der Artikel als „Auslaufartikel“ markiert und der Lagerbestand „0“ ist, kann der Artikel archiviert werden.

Ein solches Kriterium würden Sie in der Anfügeabfrage wie folgt angeben:

1. Doppelklicken Sie im Abfrageentwurf auf die Felder, für die Kriterien gesetzt werden sollen. In unserem Beispiel wären das „Auslaufartikel“ und „Lagerbestand“.

2. Löschen Sie für diese Felder die Einträge in der Zeile „Anfügen an“.

3. In der Zeile „Kriterien“ geben Sie die gewünschten Bedingungen ein. In unserem Beispiel wäre das für das Feld „Auslaufartikel“ das Kriterium „= Ja“ und für das Feld „Lagerbestand“ das Kriterium „=0“.

Wenn Sie nun das Menü ANSICHT-DATENBLATTANSICHT anwählen, werden Sie sehen, dass die Abfrage nur die Datensätze selektiert und später übertragen würde, die den Kriterien entsprechen.

Mit dem Übertragen der Datensätze ist aber erst die halbe Aufgabe erfüllt. Schließlich sollen die Daten ausgelagert werden. Sie müssen also nach der Übertragung noch gelöscht werden. Hierzu setzen Sie, wie weiter oben bereits angedeutet, eine Löschabfrage ein. Zunächst sichern Sie jedoch erst einmal die eben erstellte Anfügeabfrage unter einem Namen der Form „Archivierung/ Tabellenname – Übertragen“, sodass später im Datenbankfenster sofort ersichtlich ist, welche Aufgabe die Abfrage hat. Um eine zu dieser Anfügeabfrage passende Löschabfrage zu erstellen, gehen Sie wie folgt vor:

1. Wechseln Sie in den Bereich „Abfragen“, klicken Sie auf die Schaltfläche NEU und doppelklicken Sie auf ENTWURFSANSICHT.

2. Doppelklicken Sie im dann folgenden Dialog auf die Tabelle, aus der Datensätze archiviert werden sollen und klicken Sie auf SCHLIEßEN.

3. Doppelklicken Sie im Abfrageentwurf in der Feldliste auf den ersten Eintrag mit dem Sternchen, um alle Felder aufzunehmen.

4. Wählen Sie das Menü ABFRAGE-LÖSCHABFRAGE an.

5. Da die Abfrage nur die Datensätze löschen soll, die zuvor mit der Anfügeabfrage übertragen wurden, sind auch hier entsprechende Kriterien zu setzen. Doppelklicken Sie also auf die Felder, für die Kriterien gesetzt werden sollen (in unserem Beispiel wären das „Auslaufartikel“ und „Lagerbestand“).

6. In der Zeile „Kriterien“ geben Sie die entsprechenden Bedingungen ein, somit für das Feld „Auslaufartikel“ das Kriterium „= Ja“ und für das Feld „Lagerbestand“ das Kriterium „=0“.

Wenn Sie jetzt das Menü ANSICHT-DATENBLATTANSICHT anwählen, werden auch hier nur die Datensätze selektiert und später gelöscht, die den Kriterien entsprechen. Sichern Sie diese Löschabfrage unter einem Namen der Form „Archivierung/Tabellenname – Löschen“.

Um Anfüge- und Löschabfrage hintereinander auszuführen, können Sie die beiden Abfragen natürlich bei Bedarf hintereinander per Doppelklick ausführen. Das wäre aber ein fehlerträchtiges Unterfangen, wenn Sie zum Beispiel nur die Löschabfrage ausführen, ohne die Datensätze zuvor übertragen zu haben, oder wenn Sie vergessen, die Löschabfrage nach der Übertragung auszuführen. Es empfiehlt sich, den Aufruf beider Abfragen in einem Makro zusammenzufassen:

1. Wechseln Sie in den Bereich „Makros“, klicken Sie auf die Schaltfläche NEU und ziehen Sie das Fenster des Makro- Entwurfs nach rechts unten im Datenbankfenster.

2. Ziehen Sie die beiden eben erstellten Abfragen „Archivierung/Tabellenname – Übertragen“ und „Archivierung/Tabellenname – Löschen“ in das Makro- Entwurfsfenster. Access fügt daraufhin automatisch zwei Aktionen „Öffnen- Abfrage“ mit den passenden Parametern zum Makro hinzu.

3. Prüfen Sie vorsichtshalber noch einmal die Reihenfolge: Erst übertragen, dann löschen, danach speichern Sie das Makro unter einem Namen der Form „Archivierung/Tabellenname“ und verlassen Sie den Makro-Entwurf.

Wenn Sie dieses Makro per Doppelklick im Datenbankfenster starten, werden zunächst die Datensätze gemäß dem gewünschten Kriterium in die Archiv- Datenbank übertragen und anschließend in der Ursprungs-Datenbank gelöscht. Eventuell zeigt Access dabei Sicherheitsabfragen an, die Sie stören können. Fügen Sie dann im Makro als erste Anweisung die Aktion „Warnmeldungen“ ein und setzen Sie den Parameter „Warnmeldungen AN“ auf „Nein“.

Wie oben erläutert können Sie nun weitere Archivierungs-Abfragen mit entsprechenden Kriterien erstellen und alle in einem Makro zusammenfassen. Den Namen des Makros sollten Sie dann auf z.B. „Archivierung Artikel, Kunden, Bestellungen“ ändern, damit ersichtlich ist, welche Anfüge-/ Löschabfragen-Kombinationen über das Makro ausgeführt werden. Das eben erstellte Makro können Sie nun einmal die Woche oder einmal im Monat per Doppelklick starten und so Ihre nicht mehr benötigten Datensätze ohne weitere Aktionen fast automatisch archivieren lassen.

Um die Archivierung vollautomatisch laufen zu lassen, gehen Sie wie folgt vor:

1. Legen Sie ein neues Makro „Auto- Exec“ an oder fügen Sie in einem eventuell vorhandenen „AutoExec“- Makro die folgende Aktion hinzu:

Aktion: AusführenMakro
Makroname: Archivierung/Name

Die beiden restlichen Parameterfelder bleiben leer. Für „Archivierung/ Name“ geben Sie den Namen des Makros an, das Ihre Archivierungs- Abfragen zusammengefasst ausführt. Alternativ können Sie auch mehrere Aktionen „AusführenMakro“ verwenden, die jeweils einzelne Makros „Archivierung/Tabellenname“ aufrufen.

2. Speichern Sie die Änderungen und verlassen Sie den Makro-Entwurf.

In Zukunft wird die Archivierung bei jedem Start von Access automatisch durchgeführt, ohne dass Sie irgendwelche Makros manuell starten müssen.

Zu den wichtigsten statistischen Aufgaben von Abfragen zählt die Summenbildung bestimmter Werte. Dabei gruppieren Sie einzelne Felder und addieren die Inhalte von berechneten Feldern oder von anderen Tabellenfeldern. Häufig verwendete Gruppierungskriterien sind Datumswerte, etwa um die Umsätze einzelner Monate oder Jahre zu ermitteln.

Um Gruppierungen und Rechenfunktionen für Abfragespalten festlegen zu können, schalten Sie nach dem Öffnen eines Abfrageentwurfs die Option FUNKTIONEN im Menü ANSICHT ein oder Sie aktivieren die Schaltfläche FUNKTIONEN (Summensymbol) in der Abfragesymbolleiste. Daraufhin ergänzt Access den unteren Bereich des Entwurfsfensters um eine Zeile mit der Bezeichnung „Funktion“, in der Sie die Optionen per Dropdown-Feld auswählen können. Bei monatlichen Gruppierungseinheiten stehen Sie vor dem Problem, das Abfrageergebnis in eine sinnvolle Sortierfolge bringen zu müssen.

Sehen Sie sich einmal die obige Abfrage „Monatsumsatz_ Problem“ an.

Wenn Sie sie öffnen, erscheinen zwar die gewünschten Monatsgruppierungen. Dabei berücksichtigt Access allerdings nicht die Jahresangaben. Grund dafür ist die Art und Weise, wie die Monate aus dem Bestelldatum gebildet werden.

Wenn Sie in die Entwurfsansicht der Abfrage „Monatsumsatz_Problem“ umschalten, erkennen Sie, dass dafür eine Formatfunktion zum Einsatz kommt.

Formatfunktionen liefern aber reine Zeichenfolgeausdrücke, was Access daran hindert, eine chronologische Ordnung in die Werte zu bringen: Nach „01/2005“ folgt z. B. sofort „01/2006“, da die Monatsausdrücke Ziffer für Ziffer verglichen und sortiert werden. Bevor Sie die Formatfunktion so umstellen, dass erst das Jahr und dann der Monat zurückgegeben wird, nutzen Sie lieber die elegantere Lösung aus der Abfrage „Monatsumsatz“.

Die Monatsumsätze erscheinen hier in der korrekten zeitlichen Abfolge. Die Erklärung finden Sie, wenn Sie einen Blick auf den Abfrageentwurf von „Monatsumsatz“ werfen.

Das berechnete Feld „Zeitraum“ basiert nicht auf einer Formatfunktion, sondern auf der Datumsfunktion „DatSeriell“. So ist gewährleistet, dass Access die Inhalte weiterhin als Zeitangaben interpretieren und dementsprechend sortieren kann. Um die Gruppierung nach Monaten zu erreichen, wenden Sie einen kleinen Trick an, indem Sie alle Bestelldaten auf den Ersten des jeweiligen Monats setzen. Die Funktion „Dat- Seriell“ liefert den jeweiligen fortlaufenden Zeitwert – beispielsweise 37257 für den 1.1.2002. Damit die Monatsangaben genauso dargestellt werden wie in der anfangs vorgestellten Abfrage, müssen Sie lediglich das Ausgabeformat in den Feldeigenschaften anpassen. Dazu klicken Sie im unteren Bereich des Abfrageentwurfs mit der rechten Maustaste auf die Felddefinition. Auf diese Weise öffnen Sie ein Kontextmenü, in dem Sie den Befehl EIGENSCHAFTEN anwählen. Im Fenster mit den Feldeigenschaften tragen Sie das gewünschte Datumsformat – in diesem Fall „mm/jjjj“ – in das Feld FORMAT ein.