Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Im Beitrag “Vereinsverwaltung: Von Excel zum Datenmodell” haben wir gezeigt, wie Sie eine exemplarische Excel-Tabelle mit den Daten zur Mitgliedsverwaltung in ein -Access-Datenmodell umwandeln. Dabei haben wir die Daten der Excel-Datei bereits in der Datenbank verfügbar gemacht. Neben der damit verbundenen konzeptionellen Arbeit kommt nun der interessante Teil: die Programmierung von VBA-Code und Abfragen, um die Daten aus der monolithischen Excel-Tabelle in das Datenmodell zu übertragen.
Dabei haben wir einige Brocken bereits aus dem Weg geräumt: Die Excel-Tabelle enthielt nämlich beispielsweise nicht nur reine Daten in Form von Zahlen und Buchstaben, sondern auch verschiedene Markierungen in Form von verschiedenen Hintergrundfarben, fett gedrucktem Text oder farbigem Text. Diese haben wir bereits in der Excel-Tabelle in entsprechende Werte umgewandelt, die wie allerdings auf den folgenden Seiten noch interpretieren müssen.
Schritt 1: Mitglieder übertragen
Die Basis der beiden Tabellen, also der Ausgangstabelle im Excel-Format als auch der Zieltabellen ist eine Tabelle zum Speichern der Personen, hier von Mitgliedern eines Sportvereins. Damit können wir auch beginnen. Die Tabelle Mitglieder enthält alle Daten, welche die Excel-Tabelle uns bereitgestellt hat. Daraus suchen wir uns nun die Felder heraus, die in die von uns entworfenen Tabelle tblMitglieder passen.
Der Automatismus zum Migrieren der Daten besteht aus ein paar Abfrage, welche uns die Daten in der gewünschten Form extrahieren, sowie einer VBA-Prozedur im Modul mdlMigration. Diese heißt Migrieren und steuert die Migration der Daten aus der Excel-Tabelle in die Tabellen unseres Datenmodells.
Für die übernahme der Daten aus der Tabelle Mitglieder in die Tabelle tblMitglieder erstellen wir eine Abfrage auf Basis der Tabelle Mitglieder. Dieser fügen wir also nach dem Erstellen zunächst die Tabelle Mitglieder aus dem Dialog Tabellen anzeigen hinzu – mit dem Ergebnis aus Bild 1.
Bild 1: Anlegen einer Abfrage zum übernehmen der Stammdaten
Wir wollen aus dieser Abfrage eine Anfügeabfrage machen, welche Daten aus der Tabelle Mitglieder an die Tabelle tblMitglieder anfügt. Dazu wählen Sie aus dem Ribbon den Befehl Entwurf|Abfragetyp|Anfügen aus.
Dies öffnet den Dialog Anfügen, mit dem Sie die Tabelle tblMitglieder als Zieltabelle festlegen (s. Bild 2).
Bild 2: Angabe der Zieltabelle
Danach können Sie die Felder der Quelltabelle aus der Liste im oberen Bereich in das Entwurfsraster ziehen oder per Doppelklick dorthin befördern. Sofern Access automatisch ein gleichnamiges Feld in der Zieltabelle erkennt, fügt es direkt einen entsprechenden Eintrag in die Zeile Anfügename ein. Sollte dies nicht automatisch funktionieren, was etwa beim Feld Geb.-Datum der Fall ist, wählen Sie das Zielfeld selbst aus – in diesem Fall Geburtsdatum (s. Bild 3).
Bild 3: Auswahl der Zielfelder
Nun gibt es beim Feld PLZ das erste Hindernis: Wir haben ja schon beim Import der Tabelle gesehen, dass Access das zunächst vorgesehene Zahlenfeld nicht komplett füllen konnte, da einige Datensätze einen zusätzlichen Buchstaben enthielten – nämlich den führenden Ländercode etwa in CH-8005. Genau genommen liefert das Feld PLZ für die Schweizer Mitglieder das Länderkürzel, die Postleitzahl und auch noch den Ort – warum auch immer der Ersteller das so gemacht hat: Die Daten gehören natürlich auf drei Felder aufgeteilt. Also ändern wir den Ausdruck für das Feld PLZ ein wenig um:
PLZ: Wert(Ersetzen([Postlz];"CH-";""))
Dieser Ausdruck ersetzt zunächst im Feld Postlz die Zeichenfolge CH- durch eine leere Zeichenfolge. Aus CH-8005 Zürich wird dann schon einmal 8005 Zürich. Wie erhalten wir davon nur die führenden Ziffern Ganz einfach – mit der Wert-Funktion (unter VBA Val).
Der Ort könnte so einfach sein, wenn dieser nicht für die oben genannten Fälle komplett in der Zeile Postlz stehen würde und für alle anderen in der Spalte Ort. Doch mit ein wenig Geschick erhalten wir die gewünschten Werte. Dazu legen wir für das Feld Ort einen neuen Ausdruck namens OrtX an (damit es keinen Zirkelbezug gibt) und füllen diesen mit der folgenden Formel:
OrtX: Wenn(IstNull([Ort]);Teil([Postlz];InStrRev([Postlz];" ")+1);[Ort])
Hier finden wir eine Wenn-Bedingung, die im ersten Abschnitt prüft, ob Ort den Wert Null enthält. In diesem Fall ermitteln wir aus dem Feld Postlz den Teil hinter dem letzten gefundenen Leerzeichen – bei CH-8005 Zürich liefert dies also Zürich. Ist das Feld Ort nicht leer, liefern wir einfach den Wert dieses Feldes zurück.
Komplizierter wird es mit dem Land, denn da wir ja nun die Länderkennzeichen aus der Postleitzahl entfernen wollen, soll das Land in das Feld Land eingetragen werden. Das ist auch interessant, weil wir in der Ursprungstabelle überhaupt kein Feld haben, welches das Land angibt. Wir gehen aber davon aus, dass alle Datensätze, die nicht das Länderkürzel CH in der Postleitzahl tragen, Deutschland als Land aufweisen. Also verwenden wir den folgenden Ausdruck:
Land: Wenn(Wenn(InStr([postlz];"-")>0;Links([postlz];InStr([postlz];"-")-1);"DE")=''''CH'''';''''Schweiz'''';''''Deutschland'''')
Hier haben wir es mit ein paar Verschachtelungen zu tun. Die innere Wenn-Bedingung liefert das Länderkürzel, wenn Postlz ein Minuszeichen enthält, wenn kein Minuszeichen vorliegt, folgt DE als Länderkennzeichen.
Die äußere Wenn-Bedingung liefert das Land Schweiz, wenn das von der inneren Bedingung gelieferte Kürzel CH lautet, sonst Deutschland. Das ist zugegebenermaßen improvisiert und wird kompliziert, wenn es mehr solcher Sonderfälle gibt.
An einer solchen Stelle muss man dann entscheiden, ob man doch manuell die fehlerhaften Datensätze anpasst oder gegebenenfalls eine VBA-Routine baut, die etwas übersichtlicher daherkommt.
Diese könnte etwa wie folgt aussehen:
Public Function LandErmitteln(strPLZ As String) Dim strLaenderkuerzel As String Dim intPosStrich As Integer Dim strLand As String intPosStrich = InStr(strPLZ, "-") If intPosStrich = 0 Then strLand = "Deutschland" Else strLaenderkuerzel = Left(strPLZ, intPosStrich - 1) Select Case strLaenderkuerzel Case "CH" strLand = "Schweiz" Case "NL" strLand = "Niederlande" End Select End If LandErmitteln = strLand End Function
In der Abfrage greifen Sie wie folgt auf diese Funktion zu:
Land: LandErmitteln([Postlz])
Die Funktion erhält den Wert für das Feld Postlz als Parameterwert. Sie ermittelt dann die Position des ersten Minuszeichens und speichert diese in der Variablen intPosStrich.
Ist kein Minuszeichen vorhanden, erhält die Variable den Wert 0. In diesem Fall liefert die Funktion Deutschland zurück. Anderenfalls liest sie das Länderkürzel aus und weist in einer Select Case-Bedingung die Bezeichnung des entsprechenden Landes zu.
PLZ und Ort per VBA
Für den Fall, dass mehrere Länder hinzukommen, stellen wir auch die Ermittlung von PLZ und Ort noch auf VBA um. Für die PLZ sieht die Funktion wie folgt aus:
Public Function PLZErmitteln(strPLZ As String) Dim strLaenderkuerzel As String Dim intPosStrich As Integer intPosStrich = InStr(strPLZ, "-") If Not intPosStrich = 0 Then strPLZ = Mid(strPLZ, intPosStrich + 1) strPLZ = Val(strPLZ) End If PLZErmitteln = strPLZ End Function
Die Funktion nimmt den Werte des Feldes Postlz entgegen und ermittelt die Position des Minuszeichens. Wenn der so ermittelte Wert größer als 0 ist, ist ein Minuszeichen vorhanden. In diesem Fall entfernt die Funktion zuerst den Teil vor dem Minuszeichen inklusive Minuszeichen.
Im zweiten Schritt ermittelt sie mit der Val-Funktion alle Ziffern bis zum ersten Zeichen, das keine Ziffer ist, und gibt das Ergebnis zurück. Sollte der Parameter strPLZ eine einfache PLZ liefern, wird diese als Funktionsergebnis zurückgegeben.
Der Ausdruck im Feld der Abfrage lautet:
PLZ: PLZErmitteln([Postlz])
Den Ort ermitteln wir, indem wir die PLZ und den Ort untersuchen und diese Felder auch als Parameter übergeben. Das ist problemlos möglich, wie der folgende Ausdruck für das Feld OrtX zeigt (X angefügt, um keinen Zirkelbezug zu erzeugen):
OrtX: OrtErmitteln([Postlz];[Ort])
Die Funktion nimmt diese beiden Parameter wie folgt entgegen:
Public Function OrtErmitteln(strPLZ As String, strOrt As String) Dim strLaenderkuerzel As String Dim intPosStrich As Integer Dim intLeerzeichen As Integer intPosStrich = InStr(strPLZ, "-") If Not intPosStrich = 0 Then intLeerzeichen = InStrRev(strPLZ, " ") If Not intLeerzeichen = 0 Then strOrt = Mid(strPLZ, InStrRev(strPLZ, " ")) End If End If OrtErmitteln = strOrt End Function
Dabei prüft sie wieder, an welcher Position sich gegebenenfalls das Minuszeichen befindet. Falls eines vorhanden ist, ermittelt die Funktion als Nächstes, wo sich die hinterste Leerstelle befindet.
Ist eine vorhanden, wie in CH-8005 Zürich, erhält die Variable intLeerzeichen einen Wert ungleich 0 und der Teil hinter dem letzten Leerzeichen wird in der Variablen strOrt gespeichert. Dieser Wert wird dann als Rückgabewert zurückgeliefert. Wenn kein Minuszeichen gefunden wird, liefert die Funktion den Inhalt des Parameters strOrt zurück.
Aktives oder passives Mitglied
Ob es sich um ein aktives oder passives Mitglied handelt, finden wir über das Feld Ak/Pa der Originaltabelle heraus. In den Originaldaten finden wir dabei redundante Daten, denn diese enthält sowohl die Altersklasse als auch das Alter des Mitglieds.
Die Altersklasse soll aber aus dem Geburtsdatum und dem Stichtag für das aktuelle Jahr ermittelt werden. Also entnehmen wir diesem Feld nur die Angabe, ob es sich um ein aktives oder passives Mitglied handelt. Dafür legen wir direkt eine passende VBA-Routine an:
Public Function AktivPassiv(strAktivPassiv As String) As Boolean If InStr(1, strAktivPassiv, "P") = 0 Then AktivPassiv = True End If End Function
Der Ausdruck für das Feld lautet:
Aktiv: AktivPassiv([Ak/Pa])
Geschlecht
Aus dem Feld Ak/Pa können wir auch das Geschlecht des Mitglieds ermitteln. Wie im Beitrag Vereinsverwaltung: Von Excel zum Datenmodell (www.access-im-unternehmen.de/1106) erwähnt, weisen jeweils die Buchstaben M und W auf das Geschlecht hin – etwa wie in EPM (Erwachsene Passiv Männlich) oder WJ (Weibliche Jugend).
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo