Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 6/2005.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Domänenfunktionen

Roland Grothe, Hanau; André Minhorst, Duisburg

Zusammenfassung

Erstellen Sie ein Add-In zum Messen und Vergleichen der Performance von Funktionen.

Techniken

Domänenfunktionen

Voraussetzungen

Access 97 und höher

Beispieldateien

Domaenenfunktionen97.mdb (Access 97)
Domaenenfunktionen00.mdb (Access 2000
und höher)

Dieser Beitrag stellt Ihnen eine Gruppe von Funktionen vor, die einen recht einfachen Zugriff auf Daten in beliebigen Tabellen oder Abfragen einer Access-Datenbank ermöglichen. Mit diesen so genannten Domänenfunktionen ist es möglich, Tabellendaten in Form von Funktionsergebnissen zum Beispiel als Feldinhalte oder als Kriterien in Abfragen zur Verfügung zu stellen. Anhand einiger Praxisbeispiele erfahren Sie, in welchen Fällen der Einsatz von Domänenfunktionen sinnvoll ist und wann es besser ist, auf andere Alternativen zur Datenermittlung zurückzugreifen.

Einführung

Domänenfunktionen lassen sich am einfachsten als "in eine Funktion gepackte SQL-Abfragen" definieren. Mit Domänenfunktionen lassen sich fast alle Abfragen ersetzen, die allerdings eine Bedingung zwingend erfüllen müssen: Sie dürfen lediglich einen einzigen Wert zurückliefern. Die Bezeichnung dieses Funktionstyps erklärt sich aus dem Begriff Domäne (engl.: Domain), eine in Datenbank-Kreisen übliche Bezeichnung für eine Datensatzgruppe. Die insgesamt zwölf von Access zur Verfügung gestellten Domänenfunktionen ermöglichen den schnellen und unkomplizierten Zugriff auf Informationen aus einer Tabelle oder einer gespeicherten Abfrage.

Sie lassen sich sehr flexibel einsetzen und stellen oft die einzige Möglichkeit dar, das Ergebnis einer Funktionsabfrage in einem Formular- oder Berichtsfeld darzustellen, ohne aufwändigen VBA-Code schreiben zu müssen. Sie sind auch immer dann hilfreich, wenn ein solches Ergebnis zu weiteren Berechnungen benötigt wird, da Sie direkt in dem jeweiligen Ausdruck anstelle eines konkreten Wertes verwendet werden.

Tab. 1 zeigt alle Domänenfunktionen in Deutsch und Englisch inklusive Beschreibung. Sie können in der deutschen Version von Access fast überall sowohl die deutschen als auch die englischen Funktionsbezeichnungen verwenden. Access wandelt diese automatisch in die deutsche Bezeichnung um. Dies gilt allerdings nicht für VBA-Code. Dort verwendete Funktionen müssen mit der englischen Bezeichnung eingegeben werden. In VBA werden die Funktionsparameter statt mit dem Semikolon mit einem Komma voneinander getrennt.

Funktion (engl. Bez.)

Beschreibung

DomAnzahl (DCount)

Anzahl der Datensätze aus der Tabellen- bzw. Abfrage-Domäne, bei denen der Inhalt des in Ausdruck definierten Feldes bzw. das Ergebnis dieses Ausdrucks nicht Null ist. Berücksichtigt werden nur Datensätze, die dem in Kriterium festgelegten Kriterium entsprechen (die Syntaxbeschreibung gilt für alle Domänenfunktionen).

DomSumme (DSum)

Summe der Feldinhalte

DomErsterWert (DFirst)

Feldinhalt des ersten Datensatzes

DomLetzterWert (DLast)

Feldinhalt des letzten Datensatzes

DomMittelwert (DAvg)

Durchschnittswert des Feldinhaltes. Nullwerte werden ignoriert

DomVarianz (DVar)

Varianz eines Feldes, bezogen auf eine Stichprobe

DomVarianzG (DVarP)

Varianz eines Feldes, bezogen auf die Gesamtmenge

DomStdAbw (DStDev)

Standardabweichung eines Feldes, bezogen auf eine Stichprobe

DomStdAbwG (DStDevP)

Standardabweichung eines Feldes, bezogen auf die Gesamtmenge

DomMin (DMin)

minimaler Wert eines Feldes

DomMax (DMax)

maximaler Wert eines Feldes

DomWert (DLookup)

Feldinhalt des ersten Datensatzes

Tab. 1: Domänenfunktionen von Access

Syntaxbeschreibung anhand der Funktionen DomWert() und DomAnzahl()

Der Aufruf einer Domänenfunktion erfolgt immer in dieser Form:

Funktion(Ausdruck;Domäne[;Kriterium])

Alle drei Parameter werden in Form einer Zeichenkette übergeben, wobei die ersten beiden zwingend notwendig sind. Der Parameter Kriterium ist optional und kann weggelassen werden, wenn er nicht benötigt wird. Im Prinzip finden Sie in den Parametern die Hauptkomponenten einer SQL-Abfrage wieder, wobei lediglich die entsprechenden Schlüsselwörter weggelassen werden. Fügen Sie diese (in Gedanken) wieder hinzu, erhalten Sie den SQL-Befehl:

SELECT Ausdruck FROM Domäne 
WHERE Kriterium

Tatsächlich baut Access aus den Parametern einen der jeweiligen Domänenfunktion angepassten SQL-Befehl zusammen. Daher müssen Sie bei der Verwendung von Domänenfunktionen alle Bedingungen und Einschränkungen beachten, die auch für "normale" SQL-Befehle gelten.

Dies gilt vor allem für die vom SQL-Interpreter vorgeschriebenen amerikanischen Formatierungen, auf die der Beitrag später noch eingehen wird. Für die folgenden Beschreibungen der einzelnen Parameter verwenden Sie als Beispiel die Domänenfunktion DomWert(), die einen einzelnen Wert aus einer Tabelle oder Abfrage ermittelt und die Funktion DomAnzahl(), welche die Anzahl der Datensätze zurückgibt, die einen Eintrag in einem bestimmten Feld enthalten.

Der Parameter "Ausdruck"

Bei diesem Parameter handelt es sich normalerweise um den Namen des Feldes, das die benötigte Information enthält. Der Feldname wird als Zeichenkette übergeben, daher müssen Sie ihn in Anführungszeichen setzen. Um etwa das Bestelldatum des ersten Datensatzes der Tabelle Bestellungen zu ermitteln, verwenden Sie folgenden Ausdruck:

DomWert("[Bestelldatum]";
"[Bestellungen]")

Unter VBA würde dieser Ausdruck so aussehen (in einer Zeile):

Dlookup("[Bestelldatum]"
,"[Bestellungen]")

Sie können das Ergebnis dieses Ausdrucks im Direktfenster durch Voranstellen der Anweisung Debug.Print oder dessen Abkürzung, dem Fragezeichen (?), ausgeben lassen (s. Abb. 1).

Abb. 1: Testen einer Domänenfunktion im Direktfenster

Statt eines einzelnen Feldnamens kann aber auch ein komplexerer Ausdruck übergeben werden, der Informationen aus mehreren Feldern zusammenfasst oder Berechnungen durchführt. In diesem Fall setzen Sie den gesamten Ausdruck in doppelte Anführungszeichen. Der folgende Ausdruck ermittelt beispielsweise die Zeit zwischen der Bestellung und der Lieferung des ersten Datensatzes der Tabelle Bestellungen:

DomWert("[Lieferdatum] - [Bestelldatum]"; "[Bestellungen]")

Die VBA-Variante lautet:

Dlookup("[Lieferdatum] - [Bestelldatum]", "[Bestellungen]")

Befinden sich innerhalb des Ausdrucks doppelte Anführungszeichen, ersetzen Sie diese durch einfache Anführungszeichen, die vom SQL-Interpreter als Begrenzungszeichen anerkannt werden.

Der folgende Ausdruck ermittelt die Firmenbezeichnung aus dem ersten Datensatz der Tabelle Kunden und stellt den Ansprechpartner anschließend in Klammern dar:

DomWert("[Firma] & ' (' 
& [Kontaktperson] & ')'";"[Kunden]")

beziehungsweise

Dlookup("[Firma] & ' (' 
& [Kontaktperson] & ')'","[Kunden]")

Anstelle eines einfachen Anführungszeichens können Sie auch zwei doppelte Anführungszeichen verwenden, allerdings wird ein solcher Ausdruck sehr schnell unübersichtlich:

DomWert("[Firma] & "" ("" 
& [Kontaktperson] & "")""";"[Kunden]")

Die Zeichenkette kann der Domänenfunktion auch als Bezeichner, als Ergebnis einer Funktion oder (in VBA) als Variable übergeben werden.

In diesem Fall lassen Sie die Anführungszeichen weg. Es muss jedoch sichergestellt sein, dass die übergebene Zeichenkette die bereits angesprochenen formalen Bedingungen erfüllt, ansonsten erhalten Sie den Ausdruck #Fehler als Ergebnis.

Der Parameter "Domäne"

Wie auch der Parameter Ausdruck ist der Parameter Domäne nicht optional und daher unbedingt einzugeben. Als gültige Werte wird der Name jeder Tabelle oder gespeicherten Abfrage der aktuellen Datenbank in Form einer Zeichenkette akzeptiert. Auch für diesen Parameter gelten die gleichen Regeln bezüglich der Anführungszeichen und eckigen Klammern wie beim Parameter Ausdruck. Wird als Domäne der Name einer Abfrage verwendet, kann es sich hierbei nur um eine gespeicherte Abfrage handeln. Es ist nicht möglich, diesen Parameter wie im Ausdruck

DomWert("[Bestelldatum]";"SELECT * FROM [Bestellungen]")

direkt in Form eines SQL-Befehls zu übergeben.

Aus Gründen der Ausführungsgeschwindigkeit sollten Sie versuchen, die benötigte Information möglichst direkt aus der Tabelle zu beziehen. Im optimalen Fall verfügt die Tabelle über einen Index auf das gesuchte Feld.

Der Parameter "Kriterium"

Alle bisherigen Beispiele lieferten die gesuchte Information entweder aus einem Feld des ersten Datensatzes (DomWert()) oder berücksichtigten bei der Berechnung alle Datensätze (DomAnzahl()) der im Parameter Domäne genannten Tabelle oder Abfrage.

Dies liegt daran, dass Sie den Parameter Kriterium bei diesen Beispielen einfach weggelassen haben. Mit diesem Parameter ist es möglich, auf einen anderen als den ersten Datensatz zuzugreifen oder lediglich eine begrenzte Auswahl von Datensätzen zu berücksichtigen.

Die Syntax des Parameters Kriterium entspricht exakt der WHERE-Klausel einer SQL-Abfrage, bei der das Schlüsselwort WHERE fehlt. Wie die anderen Parameter wird auch dieser als Zeichenkette übergeben und ist in doppelte Anführungszeichen zu setzen. Um beispielsweise das Bestelldatum der Bestellung Nr. 10268 zu ermitteln, verwenden Sie den Ausdruck

DomWert("[Bestelldatum]";
"[Bestellungen]";"[Bestell-Nr]=10268")

Da es sich beim Kriterium Bestell-Nr um einen ganzzahligen Wert handelt, sind bei diesem Beispiel keine Besonderheiten zu beachten. Komplizierter wird es, wenn als Kriterien Zeichenketten, Datumsinformationen oder Fließkommazahlen herangezogen werden, da diese erst einmal in ein für den SQL-Interpreter verständliches amerikanisches Format umgesetzt werden müssen. Handelt es sich beim verwendeten Kriterium um eine Zeichenkette, muss diese durch einfache Anführungszeichen (Hochkommas) begrenzt werden. Alternativ ist auch hier die Verwendung von zwei doppelten anstelle eines einfachen Anführungszeichens möglich, allerdings werden besonders umfangreiche Kriterienklauseln damit praktisch unlesbar.

Der folgende Ausdruck ermittelt die Anzahl der Bestellungen von Alfreds Futterkiste:

DomAnzahl("[Bestell-Nr]";"[Bestellungen]"
;"[Kunden-Code]='ALFKI'")

In der Tabelle Bestellungen gibt es zwei Datensätze mit Versandkosten von 1,63 EUR. Um diese Aussage zu überprüfen, bietet sich der folgende Ausdruck an:

Debug.Print Dcount("[Bestell-Nr]", "Bestellungen", "Frachtkosten = 1,63")

Dieser funktioniert allerdings so nicht, sondern liefert eine Fehlermeldung (s. Abb. 2).

Abb. 2: Fehler bei Verwendung der falschen Syntax

Auch hier liegt der Fehler in der Formatierung des Kriterienwertes, die nicht den amerikanischen Gepflogenheiten entspricht, weil als Dezimalzeichen das bei uns übliche Komma anstelle des Punktes verwendet wird. Ändern Sie den Ausdruck wie folgt, funktioniert dieser einwandfrei:

? Dcount("[Bestell-Nr]", "Bestellungen", "Frachtkosten = 1.63")

Noch komplizierter wird es bei Datumswerten, da diese sowohl in Nummernzeichen eingeschlossen als auch im amerikanischen Format MM/DD/YYYY eingegeben werden müssen. Der Ausdruck zur Ermittlung der Anzahl an Bestellungen am 14.11.2005 sieht demnach so aus:

DomAnzahl("[Bestell-Nr]";"[Bestellungen]"
;"[Bestelldatum]=#11/14/2005#")

Da Sie wohl in den seltensten Fällen das Kriterium fest in die Funktion einbauen werden, müssen Sie es aus statischem Text und Feldinhalten mittels Textoperatoren und Formatfunktionen so zusammenbauen, dass es den geforderten Konventionen entspricht. Um das Kriterium des letzten Beispiels beispielsweise aus einem Formularfeld txtDatum zu holen, müsste der Ausdruck so aussehen:

DomAnzahl("[Bestell-Nr]";
"[Bestellungen]";"[Bestelldatum]= " 
& Format([txtDatum];"\#mm/tt/jjjj\#"))

Die WHERE-Klausel, die an den SQL-Interpreter übergeben wird, ist in beiden Fällen völlig identisch. Der Unterschied liegt lediglich darin, dass die Klausel im ersten Beispiel als statischer Text direkt in der Funktion steht und im zweiten Beispiel vor der Bearbeitung durch die Funktion erst einmal aus statischem Text und einem Feldinhalt des Formulars zusammengebaut wird.

Eine Alternative zur umständlichen Formatumwandlung ist der direkte Zugriff des SQL-Interpreters auf den Feldinhalt eines Formulars. Im Ausdruck

DomAnzahl("[Bestell-Nr]";
"[Bestellungen]";"[Bestelldatum]=
Forms!frmBeispiel!txtDatum")

besteht das gesamte Kriterium aus statischem Text, der direkt an den SQL-Interpreter übergeben wird. Dieser holt sich die benötigte Information direkt aus dem Feld txtDatum und wandelt dessen Inhalt in das von ihm benötigte Format um. Beachten Sie jedoch, dass Sie hierbei immer den kompletten Bezeichnerpfad mit Formularnamen verwenden müssen.

Die Funktionen DomWert(), DomErsterWert(), DomLetzterWert(), DomMax() und DomMin()

Obwohl alle Domänenfunktionen die gleichen Parameter verwenden, weicht die Interpretation des Kriteriums bei diesen fünf Funktionen etwas ab. Sie ermitteln ihr Ergebnis nämlich immer nur aus den Informationen eines einzigen Datensatzes, während die anderen Domänenfunktionen ihr Ergebnis aus allen Datensätzen berechnen, die das Kriterium erfüllen.

DomWert und DomErsterWert

  • Kein Datensatz entspricht den Kriterien. In diesem Fall liefern alle Funktionen den Wert Null zurück.
  • Ein einziger Datensatz entspricht den Kriterien. Dies ist meist dann der Fall, wenn als Kriterium das Primärschlüsselfeld verwendet wird. In diesem Fall liefern alle fünf Funktionen die gewünschte Information aus diesem Datensatz zurück.
  • Mehrere Datensätze entsprechen den Kriterien. In diesem Fall liefern die Funktionen DomWert() und DomErsterWert() die gewünschte Information aus dem ersten gefundenen Datensatz, die Funktion DomLetzterWert() aus dem letzten gefundenen Datensatz. Die Funktionen DomMax() und DomMin() liefern den Wert des Datensatzes, in dem die gewünschte Information den größten beziehungsweise kleinsten Wert hat.
  • Quellcode 1: Das Speichern des Primärschlüsselwertes muss unbedingt in der Ereignisprozedur BeimEntladen() erfolgen, da beim Schließen des Formulars der Datensatzzeiger bereits auf den Anfang des Recordsets zurückgesetzt wurde.

    Private Sub Form_Unload(Cancel As Integer)

        If Not IsNull(Me![Kunden-Code]) Then

            CurrentDb.Execute "UPDATE tblEinstellungen SET Wert = '" & Me![Kunden-Code] _
                & "' WHERE Einstellung = 'frmKunden_AktuellerKunde'"

        End If

    End Sub

    Quellcode 2: Beim Laden des Formulars wird der Datensatzzeiger auf den zuletzt bearbeiteten Datensatz positioniert.

    Private Sub Form_Load()

        Me.RecordsetClone.FindFirst "[Kunden-Code] = '" _
            & DLookup("Wert", "tblEinstellungen", "Einstellung = 'frmKunden_AktuellerKunde'") & "'"

        Me.Bookmark = Me.RecordsetClone.Bookmark

    End Sub

    Quellcode 3: Variation der Routine aus Quellcode 2 für Access 2000 und höher

    Private Sub Form_Load()

        Me.Recordset.FindFirst "[Kunden-Code] = '" & DLookup("Wert", "tblEinstellungen", _
            "Einstellung = 'frmKunden_AktuellerKunde'") & "'"

    End Sub

    Praxisbeispiel:
    Einstellungen speichern

    In jeder Anwendung gibt es Parameter und Einstellungswerte, die dauerhaft gespeichert werden müssen. Dafür eignet sich eine Tabelle tblEinstellungen, die Paare aus Name und Wert der Einstellung enthält (s. Abb. 3).

    Abb. 3: Tabelle zum Speichern von Einstellungen

    Mit der Domänenfunktion

    DomWert("[Wert]";"[tblEinstellungen]";
    "[Einstellung] = '<Einstellungsname>'")

    greifen Sie jederzeit schnell auf den gerade benötigten Einstellungswert zu.

    Das Formular Kunden verwendet diese Technik, um sich beim Entladen des Formulars den aktuellen Datensatz zu merken und den Datensatzzeiger beim nächsten Aufruf des Formulars wieder auf diesen zu positionieren.

    Die Ereignisprozedur BeimEntladen() aktualisiert das Tabellenfeld Wert mit Hilfe der Execute-Methode des aktuellen Datenbankobjekts für den Datensatz mit dem Wert frmKunden_AktuellerKunde im Feld Einstellung (s. Quellcode 1).

    Abb. 4: Beim Laden des Formulars frmKunden wird automatisch der zuletzt bearbeitete Datensatz geöffnet.

    Beim Laden des Formulars liest die Ereignisprozedur BeimLaden() dieses Feld mit der Funktion DomWert() (engl.: DLookup()) wieder aus und positioniert den Datensatzzeiger auf den Datensatz mit dem in den Einstellungen gefundenen Primärschlüsselwert.

    In Quellcode 2 und Quellcode 3 finden Sie zwei Varianten dieser Routine. Letztere ist etwas kürzer, funktioniert erst ab Access 2000.

    Abb. 5: Bei der Berechnung des Anteils am Gesamtumsatz kommt die Domänenfunktion DomSumme() zum Einsatz.

    Achten Sie dabei auf die Verwendung der Anführungszeichen, um den alphanumerisch gespeicherten Kundencode an die SQL-Befehle zu übergeben.

    Die Funktion DomAnzahl()

    Auch diese Funktion unterscheidet sich ein wenig von den übrigen Domänenfunktionen, da nur DomAnzahl() das Zeichen * als Wert für den Parameter Ausdruck akzeptiert. In diesem Fall liefert DomAnzahl() die Anzahl aller Datensätze zurück, die dem Kriterium entsprechen beziehungsweise bei fehlendem Kriterium die Anzahl aller Datensätze in der Domäne.

    Übergeben Sie als Ausdruck einen Feldnamen, ermittelt DomAnzahl() die Anzahl aller Datensätze, die das Kriterium erfüllen und bei denen das genannte Feld keinen Nullwert enthält.

    Die Funktionen DomSumme(), DomMittelwert(), DomStdAbw(), DomVarianz(), DomStdAbwG() und DomVarianzG()

    Alle diese Funktionen ermitteln die gesuchten Informationen aus allen Datensätzen, die das im Kriterienausdruck übergebene Kriterium erfüllen bzw. aus allen Datensätzen der Domäne, wenn kein Kriterium angegeben wurde.

    Datensätze, bei denen der gesuchte Ausdruck einen Nullwert zurückliefert, werden ignoriert. Dies ist unter anderem bei der Ermittlung des Mittelwertes wichtig, um Verfälschungen durch eine hohe Zahl von nicht eingegebenen Datensatzfeldern zu verhindern.

    Wie alle Domänenfunktionen (außer DomAnzahl()) liefern auch diese Funktionen einen Nullwert zurück, wenn kein Datensatz das Kriterium erfüllt beziehungsweise die Domäne keinen Datensatz enthält. Da es sich bei den Ergebnissen dieser Domänenfunktionen um mathematische Berechnungen handelt, muss der als Ausdruck übergebene Parameter einen numerischen Wert ergeben. Andernfalls liefern diese Funktionen den Wert #Fehler als Ergebnis zurück.

    Eine weitere Besonderheit ist bei den statistischen Domänenfunktionen DomVarianz() und DomStdAbw() zu beachten. Diese ermitteln ihre Ergebnisse aus einer Stichprobe und nicht wie ihre Pendants mit dem G am Ende aus der Gesamtheit der Datensätze und benötigen daher mindestens zwei Datensätze in der Ergebnismenge, um ein anderes Resultat als einen Nullwert zu liefern.

    Praxisbeispiel:
    Prozent vom Gesamtumsatz

    Für Unternehmen ist es wichtig zu wissen, welchen Anteil der Umsatz mit einem bestimmten Kunden am Gesamtumsatz ausmacht. In der Abfrage qryProzentualerUmsatz wird mit Hilfe der Funktion DomSumme() der Gesamtumsatz berechnet und der kumulierte Umsatz des einzelnen Kunden durch dieses Ergebnis dividiert (s. Abb. 5). Das Ergebnis dieser Division - als Prozentzahl formatiert - liefert bereits die gewünschte Information.

    Abb. 6: Die DomWert-Funktion in dieser Abfrage muss für jeden Datensatz ausgeführt werden.

    Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

    Sind Sie Abonnent?Jetzt einloggen ...
     

    Kompletten Artikel lesen?

    Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

    E-Mail:

    © 2003-2015 André Minhorst Alle Rechte vorbehalten.