Eine Vereinsverwaltung, die nur Mitglieder und Beiträge kennt, ist schnell gebaut. Soll der Verein die Beiträge per Lastschrift einziehen, fehlen ihr jedoch Angaben, die sich später nur mit Mühe ergänzen lassen: Bankverbindungen, Mandate mit Referenz und Unterschriftsdatum, Forderungen und die Läufe, in denen wir Lastschriften einreichen. Wie wir aus den Angaben der Datei „pain.008“ ein passendes Datenmodell ableiten und mit Beispieldaten füllen, zeigt der vorliegende Beitrag.<\/b><\/p>\n
Was die Datei vom Modell verlangt<\/h2>\n
Die Datei aus dem Beitrag SEPA-Lastschrift: Das Format „pain.008“ (www.access-im-unternehmen.de\/1628)<\/b> verlangt für jeden Einzug Angaben, die ein Datenmodell liefern muss.<\/p>\n
Wir ordnen sie den Tabellen zu, die wir in diesem Beitrag anlegen:<\/p>\n
Die Vereinsverwaltung aus den Beiträgen Vereinsverwaltung: Von Excel zum Datenmodell (www.access-im-unternehmen.de\/1106)<\/b>, Vereinsverwaltung: Migration (www.access-im-unternehmen.de\/1107)<\/b> und Vereinsverwaltung: Formulare (www.access-im-unternehmen.de\/1134)<\/b> endet bei den Formularen.<\/p>\n
Sie enthält weder Bankverbindungen noch Mandate, Forderungen oder Läufe. Wir legen daher ein neues Modell an. Sie finden es in der Datenbank VereinsverwaltungDatenmodell.accdb<\/b>.<\/p>\nPersonen und Familien<\/h2>\n
Die Tabelle tblPersonen<\/b> enthält die Stammdaten: Anrede<\/b>, Vorname<\/b>, Nachname<\/b>, Geburtsdatum<\/b>, Strasse<\/b>, PLZ<\/b>, Ort<\/b> und EMail<\/b> (siehe Bild 1).<\/p>\n
![\"Entwurf](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_001.png\")
<\/p>\n
Bild 1: Entwurf der Tabelle tblPersonen<\/span><\/b><\/p>\n Die Mitgliedschaft steht in der eigenen Tabelle tblMitgliedschaften<\/b>, damit eine Person nach einem Austritt wieder eintreten kann.<\/p>\n Sie referenziert die Person, zu der die Mitgliedschaft gehört, sowie die Beitragsart über entsprechende Fremdschlüsselfelder. Außerdem speichert sie das Eintritts- und das Austrittsdatum (siehe Bild 2).<\/p>\n Bild 2: Entwurf der Tabelle tblMitgliedschaften<\/span><\/b><\/p>\n Die Tabelle tblFamilien<\/b> besteht nur aus dem Primärschlüsselfeld FamilieID<\/b> und dem Feld Familienname<\/b>. Die Tabelle tblPersonen<\/b> verweist über das Feld FamilieID<\/b> darauf (siehe Bild 3). Das Feld ist optional, denn nicht jedes Mitglied gehört zu einer Familie. Die referenzielle Integrität lässt sich trotzdem erzwingen, weil Access für den Wert NULL<\/b> keinen Verweis prüft. Wir nutzen die Familie später, um einen Familienbeitrag zu berechnen.<\/p>\n Bild 3: Entwurf der Tabelle tblFamilien<\/span><\/b><\/p>\n Die Tabelle tblMitgliedschaften<\/b> verknüpft eine Person mit einer Beitragsart und enthält die Felder Eintritt<\/b> und Austritt<\/b>. Solange das Feld Austritt<\/b> den Wert NULL<\/b> hat, ist die Person Mitglied. Ein Wiedereintritt erzeugt eine zweite Mitgliedschaft derselben Person.<\/p>\n Die Tabelle tblBeitragsarten<\/b> enthält die vier Arten Kinder<\/b>, Jugendliche<\/b>, Erwachsene<\/b> und Senioren<\/b>. Die Beträge stehen nicht dort, sondern in der Tabelle tblBeitragssaetze<\/b> mit den Feldern GueltigAb<\/b>, GueltigBis<\/b> und Jahresbeitrag<\/b>. Warum? Ganz einfach: So behalten wir bei einer Beitragsänderung den bisherigen Satz für zurückliegende Jahre und können für jeden Stichtag den gültigen Beitrag ermitteln. In den Beispieldaten kostet die Beitragsart Erwachsene<\/b> bis zum 31.12.2025<\/b> 55,00<\/b> Euro und ab dem 01.01.2026<\/b> 60,00<\/b> Euro im Jahr (siehe Bild 4).<\/p>\n Bild 4: Daten der Tabellen tblBeitragsarten und tblBeitragssaetze<\/span><\/b><\/p>\n Den Satz, der an einem Stichtag gilt, liefert die Abfrage qryBeitragssaetzeAmStichtag<\/b>:<\/p>\n Die Abfrage erwartet den Parameter Stichtag<\/b>. Sie wählt alle Sätze, die an diesem Tag bereits gültig und noch nicht abgelaufen sind. Ein offener Satz hat im Feld GueltigBis<\/b> den Wert NULL<\/b>.<\/p>\n Ein Vergleich mit NULL<\/b> ergibt NULL<\/b> statt Wahr<\/b>. Daher prüfen wir das Feld zusätzlich mit IS NULL<\/b>. Für den 30.06.2025<\/b> liefert die Abfrage bei der Beitragsart Erwachsene<\/b> den Betrag 55,00<\/b>, für den 01.01.2026<\/b> den Betrag 60,00<\/b> (siehe Bild 5).<\/p>\n Bild 5: Beitragssätze am 1.1.2026<\/span><\/b><\/p>\n Die Tabelle tblBankverbindungen<\/b> gehört zu einer Person und enthält die Felder Kontoinhaber<\/b>, IBAN<\/b> und BIC<\/b>.<\/p>\n Der Kontoinhaber ist ein Textfeld, denn der Name auf dem Konto muss nicht der des Mitglieds sein: Bei einem Kind zahlt in der Regel ein Elternteil. Das Feld BIC<\/b> bleibt optional, weil die Datei ohne BIC auskommt (siehe Bild 6).<\/p>\n Bild 6: Tabelle zum Speichern der Bankverbindung einer Person<\/span><\/b><\/p>\n Das Mandat steht in der Tabelle tblMandate<\/b> (siehe Bild 7).<\/p>\n Bild 7: Tabelle zum Speichern der Mandate<\/span><\/b><\/p>\n Sie enthält diese Felder:<\/p>\n In den Beispieldaten gibt es zu jedem Mitglied eine Bankverbindung und ein Mandat. Die vier Mitglieder der Familie Mueller teilen sich ein Konto, haben aber jeweils ein eigenes Mandat (siehe Bild 8).<\/p>\n Bild 8: Personen mit Mandaten und Bankdaten<\/span><\/b><\/p>\n Den Folgetyp speichern wir nicht. Er ergibt sich aus den Lastschriften, die ein Mandat bereits hatte: Hat das Mandat die Mandatsart Einmalig<\/b>, gilt OOFF<\/b>. Gibt es noch keine Lastschrift in einem eingereichten Lauf, gilt FRST<\/b>, andernfalls RCUR<\/b>. Ein gespeicherter Wert könnte dem tatsächlichen Verlauf widersprechen. Wie wir diese Ableitung per VBA umsetzen, zeigt der Beitrag Vereinsverwaltung: Mitglieder, Bankverbindungen und Mandate (www.access-im-unternehmen.de\/1630)<\/b>.<\/p>\n Die Tabelle tblBeitragsforderungen<\/b> enthält je Mitgliedschaft und Beitragsjahr eine Forderung mit den Feldern MitgliedschaftID<\/b>, Beitragsjahr<\/b>, Betrag<\/b> und Status<\/b>. Der Standardwert des Feldes Status<\/b> ist Offen<\/b>. Den Betrag speichern wir, statt ihn aus dem Beitragssatz zu berechnen, denn bei einem Eintritt im Laufe des Jahres oder bei einem Rabatt weicht er vom Jahresbeitrag ab (siehe Bild 9).<\/p>\n Bild 9: Tabelle zum Speichern der Beitragsforderungen<\/span><\/b><\/p>\n Ein Einzug entspricht einer Datei und damit einem Datensatz der Tabelle tblLastschriftlaeufe<\/b>. Die Felder Erstellt<\/b>, Faelligkeit<\/b> und MsgId<\/b> übernehmen wir später in den Kopf der Datei. Das Feld Status<\/b> hat den Standardwert Angelegt<\/b>. Sobald wir den Lauf bei der Bank eingereicht haben, ändern wir den Wert des Feldes Status<\/b> (siehe Bild 10).<\/p>\n Bild 10: Tabelle zum Speichern der Lastschriftläufe<\/span><\/b><\/p>\n Die Tabelle tblLastschriftpositionen<\/b> verbindet je eine Forderung mit einem Mandat und einem Lauf. Das Feld Betrag<\/b> kopiert den Betrag der Forderung. Ändert sich die Forderung später, bleibt die eingereichte Position unverändert (siehe Bild 11).<\/p>\n Bild 11: Tabelle zum Speichern der Lastschriftpositionen<\/span><\/b><\/p>\n Anzahl und Summe eines Laufs berechnen wir mit einer Abfrage. Für den Lauf mit dem Wert 1<\/b> im Feld LaufID<\/b> ermitteln wir die Werte für NbOfTxs<\/b> und CtrlSum<\/b> mit dieser Abfrage:<\/p>\n Solange die Beispieldatenbank keine Positionen enthält, liefert die Abfrage für NbOfTxs<\/b> den Wert 0<\/b> und für CtrlSum<\/b> den Wert NULL<\/b>.<\/p>\n Die Tabelle tblOptionen<\/b> enthält einen Datensatz mit den Angaben des Vereins: Vereinsname<\/b>, VereinsIBAN<\/b>, GlaeubigerID<\/b> und Altersstichtag<\/b>. Die Gläubiger-Identifikationsnummer steht hier und nicht beim Mandat, denn sie gilt für den ganzen Verein. Die Beispieldaten enthalten die Test-ID DE98ZZZ09999999999<\/b>. Den Altersstichtag 31.12.2026<\/b> nutzen wir später, um aus dem Alter die Beitragsart zu bestimmen (siehe Bild 12).<\/p>\n Bild 12: Tabelle zum Speichern der Optionen der Anwendungen<\/span><\/b><\/p>\n Zehn Beziehungen verbinden die Tabellen, darunter relFamilienPersonen<\/b> und relPersonenMitgliedschaften<\/b>. Jede davon erzwingt die referenzielle Integrität (siehe Bild 13). Ein Mandat zu einer nicht vorhandenen Bankverbindung lehnt Access deshalb ab. Warum diese Einstellung sinnvoll ist, erläutert der Beitrag Warum Beziehungen mit referenzieller Integrität? (www.access-im-unternehmen.de\/1586)<\/b>.<\/p>\n Bild 13: Übersicht über das Datenmodell der Anwendung<\/span><\/b><\/p>\n Für die folgenden Beiträge benötigen wir Daten. Wir erzeugen sie im Modul mdlBeispieldaten<\/b> per VBA-Prozedur: 40 Personen, davon drei Familien mit zusammen zehn Mitgliedern und 30 Einzelpersonen, dazu je Person eine Mitgliedschaft, eine Bankverbindung und ein Mandat.<\/p>\n Damit Sie dieselben Daten erhalten wie wir, setzen wir den Zufallsgenerator mit Rnd -1<\/b> zurück und starten ihn mit Randomize 42<\/b>. Die Zufallswerte folgen dann derselben Reihenfolge.<\/p>\n Die Funktion PersonAnlegen<\/b> legt eine einzelne Person an (siehe Listing 1). Als Parameter übergeben wir die FamilieID<\/b>, den Nachnamen und die Angabe, ob die Person erwachsen ist.<\/p>\n Listing 1: Eine Person mit zufälligen Daten anlegen<\/span><\/b><\/p>\n Die Funktion wählt Vornamen, Nachnamen und Straßen mit der Hilfsfunktion ZufallsWert<\/b> aus kommaseparierten Listen, die als Konstanten am Anfang des Moduls stehen. Das Geburtsdatum liefert die Hilfsfunktion ZufallsDatum<\/b>. Die Funktion IIf<\/b> wertet beide Zweige aus. Dadurch verbraucht sie mehr Zufallszahlen, was die Zufallsfolge verändert. Da der Startwert fest ist, bleibt das Ergebnis reproduzierbar. Nach dem Speichern setzt die Funktion das Lesezeichen mit der Eigenschaft LastModified<\/b> auf den neuen Datensatz, liest dessen PersonID<\/b> und gibt sie zurück.<\/p>\n Die Mandate und Bankverbindungen legt die Prozedur BankverbindungAnlegen<\/b> an (siehe Listing 2). Die Prozedur BeispieldatenErzeugen<\/b> ruft sie für jede Person auf, nachdem die Mitgliedschaften angelegt sind.<\/p>\n Listing 2: Bankverbindung und Mandat einer Person anlegen<\/span><\/b><\/p>\n Die Prozedur bestimmt zuerst, wessen Konto die Person nutzt. Gehört sie zu einer Familie, nutzt sie das Konto des Familienmitglieds mit der kleinsten PersonID<\/b>. Andernfalls nutzt sie ein eigenes Konto. Nur wer sein eigenes Konto nutzt, erhält eine neue IBAN. Sie entsteht mit der Funktion IbanErzeugen<\/b> aus dem Beitrag Prüfziffern per VBA berechnen und prüfen (www.access-im-unternehmen.de\/1618)<\/b>, die wir mit den Modulen mdlPruefziffern<\/b> und mdlRegEx<\/b> übernommen haben. Die Mandatsreferenz<\/b> setzt sich aus dem Buchstaben M<\/b>, dem Jahr der Unterschrift und der PersonID<\/b> zusammen, etwa M2019-0001<\/b>.<\/p>\n Die Prozedur BeispieldatenErzeugen<\/b> ruft die Prozeduren zum Anlegen der Tabellendaten nacheinander auf. Zuvor löscht sie die vorhandenen Daten, zuerst in den Detailtabellen. Starten Sie BeispieldatenErzeugen<\/b> im Direktbereich:<\/p>\n Beim Löschen der Daten setzt Access die Autowerte nicht zurück. Wer die Prozedur ein zweites Mal startet, erhält daher neue Werte für PersonID<\/b> und damit andere Mandatsreferenzen. Damit die Nummern wieder bei 1<\/b> beginnen, komprimieren und reparieren Sie die Datenbank, solange die Tabellen leer sind.<\/p>\n Die Abfrage qryMitgliederProBeitragsart<\/b> zählt die Mitgliedschaften je Beitragsart. In den Beispieldaten sind es 32<\/b> Erwachsene, 3<\/b> Jugendliche, 2<\/b> Kinder und 3<\/b> Senioren. Bei drei Mitgliedschaften steht im Feld Austritt<\/b> der Wert 30.06.2026<\/b>, damit wir später auch anteilige Beiträge berechnen können.<\/p>\n![\"Entwurf](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_002.png\")
<\/p>\n![\"Entwurf](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_003.png\")
<\/p>\nMitgliedschaften und Beitragsarten<\/h2>\n
![\"Daten](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_004.png\")
<\/p>\nPARAMETERS Stichtag DateTime;\r\nSELECT a.Beitragsart, s.GueltigAb, s.GueltigBis, s.Jahresbeitrag\r\nFROM tblBeitragssaetze AS s INNER JOIN tblBeitragsarten AS a\r\nON s.BeitragsartID = a.BeitragsartID\r\nWHERE s.GueltigAb <= [Stichtag]\r\nAND (s.GueltigBis >= [Stichtag] OR s.GueltigBis IS NULL)\r\nORDER BY a.Beitragsart;<\/pre>\n
![\"Beitragssätze](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_005.png\")
<\/p>\nBankverbindungen und Mandate<\/h2>\n
![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_006.png\")
<\/p>\n![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_007.png\")
<\/p>\n\n
![\"Personen](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_008.png\")
<\/p>\nForderungen, Läufe und Positionen<\/h2>\n
![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_009.png\")
<\/p>\n![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_010.png\")
<\/p>\n![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_011.png\")
<\/p>\nSELECT Count(*) AS NbOfTxs, Sum(Betrag) AS CtrlSum\r\nFROM tblLastschriftpositionen WHERE LaufID = 1;<\/pre>\n
Optionen des Vereins<\/h2>\n
![\"Tabelle](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_012.png\")
<\/p>\nBeziehungen und Integrität<\/h2>\n
![\"Übersicht](\"..\/fileadmin\/_temp_\/2026_06\/pic_1629_013.png\")
<\/p>\nBeispieldaten erzeugen<\/h2>\n
Private Function <\/span>PersonAnlegen(ByVal lngFamilieID As Long<\/span>, ByVal strNachname As String<\/span>, ByVal blnErwachsen As <\/span>_\r\n Boolean) As Long<\/span>\r\n ' Legt eine Person mit zufaelligen Daten an, liefert die PersonID\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>blnMaennlich As Boolean<\/span>\r\n Dim <\/span>strVorname As String<\/span>\r\n blnMaennlich = (Rnd < 0.5)\r\n If <\/span>blnMaennlich Then<\/span>\r\n strVorname = ZufallsWert(cstrVornamenMaennlich)\r\n Else<\/span>\r\n strVorname = ZufallsWert(cstrVornamenWeiblich)\r\n End If<\/span>\r\n If <\/span>strNachname = \"\" Then<\/span> strNachname = ZufallsWert(cstrNachnamen)\r\n Set<\/span> rst = CurrentDb.OpenRecordset(\"tblPersonen\", dbOpenDynaset)\r\n rst.AddNew<\/span>\r\n If <\/span>lngFamilieID > 0 Then<\/span> rst!FamilieID = lngFamilieID\r\n rst!Anrede = IIf(blnMaennlich, \"Herr\", \"Frau\")\r\n rst!Vorname = strVorname\r\n rst!Nachname = strNachname\r\n rst!Geburtsdatum = IIf(blnErwachsen, ZufallsDatum(1955, 2002), ZufallsDatum(2008, 2018))\r\n rst!Strasse = ZufallsWert(cstrStrassen) & \" \" & (1 + Int(Rnd * 60))\r\n rst!PLZ = \"12345\"\r\n rst!Ort = \"Beispielstadt\"\r\n rst!EMail = LCase(strVorname & \".\" & strNachname) & \"@example.org\"\r\n rst.Update\r\n rst.Bookmark = rst.LastModified\r\n PersonAnlegen = rst!PersonID\r\n rst.Close\r\nEnd Function<\/span><\/pre>\nPrivate Sub <\/span>BankverbindungAnlegen(ByVal lngPersonID As Long<\/span>)\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>lngFamilieID As Long<\/span>\r\n Dim <\/span>lngKontoPerson As Long<\/span>\r\n Dim <\/span>lngBankID As Long<\/span>\r\n Dim <\/span>strIban As String<\/span>\r\n Dim <\/span>datUnterschrift As Date<\/span>\r\n lngFamilieID = Nz(DLookup(\"FamilieID\", \"tblPersonen\", \"PersonID=\" & lngPersonID), 0)\r\n lngKontoPerson = lngPersonID\r\n If <\/span>lngFamilieID > 0 Then<\/span>\r\n lngKontoPerson = DMin(\"PersonID\", \"tblPersonen\", \"FamilieID=\" & lngFamilieID)\r\n End If<\/span>\r\n If <\/span>lngKontoPerson = lngPersonID Then<\/span>\r\n strIban = IbanErzeugen(\"DE\", \"12345678\" & _\r\n Format(CLng(Rnd * 89999999) + 10000000, \"0000000000\"))\r\n Else<\/span>\r\n strIban = DLookup(\"IBAN\", \"tblBankverbindungen\", \"PersonID=\" & lngKontoPerson)\r\n End If<\/span>\r\n Set<\/span> rst = CurrentDb.OpenRecordset(\"tblBankverbindungen\", dbOpenDynaset)\r\n rst.AddNew<\/span>\r\n rst!PersonID = lngPersonID\r\n rst!Kontoinhaber = DLookup(\"Vorname\", \"tblPersonen\", _\r\n \"PersonID=\" & lngKontoPerson) & \" \" & _\r\n DLookup(\"Nachname\", \"tblPersonen\", \"PersonID=\" & lngKontoPerson)\r\n rst!IBAN = strIban\r\n rst.Update\r\n rst.Bookmark = rst.LastModified\r\n lngBankID = rst!BankverbindungID\r\n rst.Close\r\n datUnterschrift = DLookup(\"Eintritt\", \"tblMitgliedschaften\", \"PersonID=\" & lngPersonID) + Int(Rnd * 14)\r\n Set<\/span> rst = CurrentDb.OpenRecordset(\"tblMandate\", dbOpenDynaset)\r\n rst.AddNew<\/span>\r\n rst!BankverbindungID = lngBankID\r\n rst!Mandatsreferenz = \"M\" & Year(datUnterschrift) & \"-\" & Format(lngPersonID, \"0000\")\r\n rst!Unterschriftsdatum = datUnterschrift\r\n rst.Update\r\n rst.Close\r\nEnd Sub<\/span><\/pre>\nBeispieldatenErzeugen\r\n40 Personen, 40 Mandate<\/pre>\n