Vereinsverwaltung: Beiträge berechnen

Wer die Beiträge eines Vereins von Hand berechnet, muss für jedes Mitglied Alter, Beitragsart und Beitragssatz nachschlagen. Bei einem Eintritt oder Austritt im Laufe des Jahres kommt die Berechnung des anteiligen Beitrags hinzu, und bei Familien soll der Beitrag gedeckelt werden. Das ist aufwendig und fehleranfällig. Wie wir aus den Mitgliedschaften und den Beitragssätzen die Forderungen eines Beitragsjahres berechnen, zeigt der vorliegende Beitrag.

Die Regeln im Überblick

Die Beispieldatenbank VereinsverwaltungBeitraegeBerechnen.accdb baut auf der Datenbank aus dem Beitrag Vereinsverwaltung: Mitglieder, Bankverbindungen und Mandate (www.access-im-unternehmen.de/1630) auf.

Hinzu kommt das Modul mdlBeitraege mit den Funktionen und Prozeduren dieses Beitrags. Die Berechnung folgt fünf Regeln:

• Beitragsart: Sie ergibt sich aus dem Alter des Mitglieds am Altersstichtag des Beitragsjahres.
• Beitragssatz: Es gilt der Jahresbeitrag, der am 1. Januar des Beitragsjahres für die Beitragsart gültig ist.
• Anteil: Tritt ein Mitglied im Beitragsjahr ein oder aus, zählt jeder angefangene Monat voll.
• Familiendeckel: Die Summe der Beiträge einer Familie darf einen Höchstbetrag nicht überschreiten, wenn für die Familie der Deckel gilt. Wir kürzen dafür zuerst die Beiträge der jüngsten Mitglieder.
• Forderung: Das Ergebnis speichern wir je Mitgliedschaft und Beitragsjahr in der Tabelle tblBeitragsforderungen.

Für die Regeln erweitern wir das Datenmodell um vier Felder. Die Tabelle tblBeitragsarten erhält die Felder AlterVon und AlterBis, die den Altersbereich einer Beitragsart festlegen (siehe Bild 1).

Bild 1: Die neuen Alters-Felder in der Tabelle tblBeitragsarten

Die Tabelle tblOptionen erhält das Feld FamilienHoechstbetrag (siehe Bild 2). Die Tabelle tblFamilien erhält das Feld Deckel vom Datentyp Ja/Nein, mit dem wir für jede Familie festlegen, ob der Familiendeckel gilt. Damit stehen die Altersgrenzen, der Höchstbetrag und die Entscheidung je Familie in den Daten und nicht im Code. Die Beispieldaten enthalten die Beitragsarten Kinder (0 bis 13 Jahre), Jugendliche (14 bis 17 Jahre), Erwachsene (18 bis 64 Jahre) und Senioren (ab 65 Jahren). Der Höchstbetrag beträgt 150,00 Euro, der Altersstichtag ist der 31.12.2026.

Bild 2: Das neue Feld FamilienHoechstbeitrag in der Tabelle tblOptionen

Die Beitragsart nach dem Alter bestimmen

Das Alter in vollen Jahren am Stichtag liefert die Funktion AlterAm im Modul mdlBeispieldaten. Die Prozeduren zum Anlegen der Beispieldaten verwenden sie bereits. Wir haben sie auf öffentlich umgestellt. Welche Beitragsart zu einem Alter gehört, ermittelt die Funktion BeitragsartFuerAlter (siehe Listing 1).

Public Function BeitragsartFuerAlter(ByVal lngAlter As Long) As Long
    ' Liefert die Beitragsart, deren Altersbereich das Alter enthaelt
    BeitragsartFuerAlter = Nz(DLookup("BeitragsartID", "tblBeitragsarten", _
        "AlterVon<=" & lngAlter & " AND AlterBis>=" & lngAlter), 0)
End Function

Listing 1: Die Beitragsart zu einem Alter ermitteln

Die Funktion sucht mit DLookup die Beitragsart, deren Altersbereich das übergebene Alter einschließt. Findet DLookup keine Beitragsart, liefert die Funktion Nz den Wert 0. Das geschieht, wenn kein Altersbereich das Alter enthält, etwa bei einer Lücke zwischen zwei Bereichen. Für ein Alter von 14 Jahren liefert die Funktion die BeitragsartID 2, also die Beitragsart Jugendliche.

Den Jahresbeitrag am Stichtag ermitteln

Den Beitragssatz zu einer Beitragsart liefert die Funktion JahresbeitragAm (siehe Listing 2).

Public Function JahresbeitragAm(ByVal lngBeitragsartID As Long, ByVal datStichtag As Date) As Currency
    ' Liefert den Jahresbeitrag, der an einem Stichtag gilt
    Dim strDatum As String
    strDatum = "#" & Format(datStichtag, "yyyy-mm-dd") & "#"
    JahresbeitragAm = Nz(DLookup("Jahresbeitrag", "tblBeitragssaetze", _
        "BeitragsartID=" & lngBeitragsartID & " AND GueltigAb<=" & strDatum & _
        " AND (GueltigBis>=" & strDatum & " OR GueltigBis IS NULL)"), 0)
End Function

Listing 2: Den gültigen Jahresbeitrag zu einem Stichtag ermitteln

Sie arbeitet wie die Abfrage qryBeitragssaetzeAmStichtag aus dem Beitrag Vereinsverwaltung: Datenmodell (www.access-im-unternehmen.de/1629), fragt aber nur eine Beitragsart ab.

Die Funktion setzt das Datum im Format JJJJ-MM-TT zwischen zwei Rautenzeichen (#) in die Bedingung ein.

Dieses Format legt die Reihenfolge von Jahr, Monat und Tag fest. Ein offener Beitragssatz hat im Feld GueltigBis den Wert NULL, deshalb prüfen wir das Feld zusätzlich mit IS NULL. Im Direktbereich prüfen wir den Beitragswechsel der Erwachsenen mit der BeitragsartID 3:

Debug.Print JahresbeitragAm(3, #6/30/2025#)
55
Debug.Print JahresbeitragAm(3, #1/1/2026#)
60

Für die Berechnung verwenden wir den 1. Januar des Beitragsjahres als Stichtag. Ändert der Verein den Beitragssatz im Laufe des Jahres, gilt für das ganze Jahr der Beitragssatz vom Jahresbeginn.

Anteilige Beiträge bei Eintritt und Austritt

Tritt ein Mitglied im Beitragsjahr ein oder aus, zahlt es nur für die Monate seiner Mitgliedschaft.

Wie viele Monate das sind, ermittelt die Funktion MonateImJahr (siehe Listing 3). Sie erwartet den Eintritt, den Austritt und das Beitragsjahr. Der Parameter varAustritt hat den Datentyp Variant, weil das Feld Austritt der Tabelle tblMitgliedschaften den Wert NULL enthalten kann.

Public Function MonateImJahr(ByVal datEintritt As Date, ByVal varAustritt As Variant, ByVal lngJahr As Long) As Long
    ' Zaehlt die Monate der Mitgliedschaft im Jahr, angefangene Monate zaehlen voll
    Dim lngVon As Long
    Dim lngBis As Long
    lngVon = 1
    lngBis = 12
    If Year(datEintritt) > lngJahr Then Exit Function
    If Year(datEintritt) = lngJahr Then lngVon = Month(datEintritt)
    If Not IsNull(varAustritt) Then
        If Year(varAustritt) < lngJahr Then Exit Function
        If Year(varAustritt) = lngJahr Then lngBis = Month(varAustritt)
    End If
    If lngBis >= lngVon Then MonateImJahr = lngBis - lngVon + 1
End Function

Listing 3: Die Monate einer Mitgliedschaft im Beitragsjahr zählen

Die Funktion beginnt mit dem Zeitraum 1 bis 12. Liegt der Eintritt im Beitragsjahr, beginnt der Zeitraum im Eintrittsmonat. Liegt der Eintritt in einem späteren Jahr, gibt es keine Monate.

Die Funktion endet dann mit dem Wert 0. Liegt der Austritt in einem früheren Jahr, endet sie ebenfalls mit 0. Liegt der Austritt im Beitragsjahr, endet der Zeitraum im Austrittsmonat. Das Ergebnis sind die Monate vom ersten bis zum letzten Monat einschließlich.

Zwei Aufrufe im Direktbereich zeigen die Rechnung:

Debug.Print MonateImJahr(#5/10/2019#, #6/30/2026#, 2026)
6
Debug.Print MonateImJahr(#3/15/2026#, Null, 2026)
10

Das erste Mitglied ist am 10.05.2019 eingetreten und tritt am 30.06.2026 aus. Es zahlt daher sechs Monate.

Das zweite Mitglied tritt am 15.03.2026 ein und ist weiterhin Mitglied.

Es zahlt daher zehn Monate. Aus den Monaten ergibt sich der Anteil des Jahresbeitrags: Für ein Mitglied mit einem Jahresbeitrag von 60,00 Euro und sechs Monaten sind das 30,00 Euro.

Die Prozedur ForderungenAnlegen rundet den Anteil mit der Funktion fctRound auf zwei Nachkommastellen (die Funktion finden Sie im Modul mdlTools).

Diese verwenden wir, da die VBA-Funktion Round nicht kaufmännisch rundet, sondern das sogenannte Bankers Rounding nutzt.

Den Familiendeckel anwenden

Eine Familie zahlt höchstens den Betrag, den der Verein als Höchstbetrag festlegt, im Beispiel 150,00 Euro im Jahr.

Zunächst berechnen wir die Forderungen ohne den Deckel.

Dazu setzen wir das Feld Deckel in der Tabelle tblFamilien bei allen Familien auf Nein und legen die Forderungen mit der Prozedur ForderungenAnlegen an, die wir weiter unten beschreiben.

Die Forderungen der drei Familien summieren sich dann auf 180,00 Euro (Mueller), 156,00 Euro (Schmidt) und 120,00 Euro (Weber, siehe Bild 3).

Bild 3: Die Forderungen der drei Familien ohne Familiendeckel im Formular frmFamilienForderungen

Die Familien Mueller und Schmidt überschreiten den Höchstbetrag. Die Prozedur FamiliendeckelAnwenden kürzt die Forderungen der Familie, bis die Summe den Höchstbetrag erreicht (siehe Listing 4). Sie erwartet die FamilieID und das Beitragsjahr.

Public Sub FamiliendeckelAnwenden(ByVal lngFamilieID As Long, ByVal lngJahr As Long)
    ' Kuerzt die Forderungen einer Familie mit Deckel, die juengsten zuerst
    Dim rst As DAO.Recordset
    Dim curHoechst As Currency
    Dim curSumme As Currency
    Dim curUeberschuss As Currency
    Dim curKuerzung As Currency
    curHoechst = Nz(DLookup("FamilienHoechstbetrag", "tblOptionen"), 0)
    If curHoechst = 0 Then Exit Sub
    If Not Nz(DLookup("Deckel", "tblFamilien", _
            "FamilieID=" & lngFamilieID), True) Then Exit Sub
    Set rst = CurrentDb.OpenRecordset("SELECT tblBeitragsforderungen.ForderungID, " & _
        "tblBeitragsforderungen.Betrag FROM (tblBeitragsforderungen INNER JOIN " & _
        "tblMitgliedschaften ON tblBeitragsforderungen.MitgliedschaftID = " & _
        "tblMitgliedschaften.MitgliedschaftID) INNER JOIN tblPersonen ON " & _
        "tblMitgliedschaften.PersonID = tblPersonen.PersonID " & _
        "WHERE tblPersonen.FamilieID = " & lngFamilieID & _
        " AND tblBeitragsforderungen.Beitragsjahr = " & lngJahr & _
        " ORDER BY tblPersonen.Geburtsdatum DESC", dbOpenSnapshot)
    Do Until rst.EOF
        curSumme = curSumme + rst!Betrag
        rst.MoveNext
    Loop
    curUeberschuss = curSumme - curHoechst
    If curUeberschuss > 0 Then
        rst.MoveFirst
        Do Until rst.EOF Or curUeberschuss <= 0
            curKuerzung = IIf(rst!Betrag < curUeberschuss, rst!Betrag, curUeberschuss)
            CurrentDb.Execute "UPDATE tblBeitragsforderungen SET Betrag = Betrag - " & _
                Replace(CStr(curKuerzung), ",", ".") & " WHERE ForderungID = " & _
                rst!ForderungID, dbFailOnError
            curUeberschuss = curUeberschuss - curKuerzung
            rst.MoveNext
        Loop
    End If
    rst.Close
End Sub

Listing 4: Die Forderungen einer Familie auf den Höchstbetrag kürzen

Die Prozedur endet ohne Änderung, wenn im Feld FamilienHoechstbetrag der Wert NULL oder 0 steht oder wenn die Familie im Feld Deckel den Wert Nein hat. Andernfalls öffnet sie die Forderungen der Familie, absteigend nach dem Feld Geburtsdatum sortiert, sodass die jüngsten Mitglieder zuerst erscheinen.

In einem ersten Durchlauf summiert sie die Werte des Feldes Betrag. Ist die Summe größer als der Höchstbetrag, geht sie die Forderungen in einem zweiten Durchlauf durch und kürzt sie jeweils um den Überschuss, der noch über dem Höchstbetrag liegt, höchstens aber um den Betrag der Forderung.

Die Kürzung schreibt die Prozedur mit einer UPDATE-Anweisung, in der sie das Dezimaltrennzeichen vom Komma (,) auf den Punkt (.) umstellt.

Bei der Familie Mueller beträgt der Überschuss 30,00 Euro. Hans, das jüngste Mitglied, zahlt 24,00 Euro und wird auf 0,00 Euro gekürzt. Die restlichen 6,00 Euro kürzt die Prozedur bei Jens von 36,00 auf 30,00 Euro.

Bei der Familie Schmidt beträgt der Überschuss 6,00 Euro. Die Prozedur kürzt ihn bei Emma, deren Forderung von 36,00 auf 30,00 Euro sinkt. Die Familie Weber liegt unter dem Höchstbetrag, ihre Forderungen bleiben unverändert (siehe Bild 4).