PDA

Vollständige Version anzeigen : Summenbildung in Abfrage-eigentlich ein Fall für eine for-Schleife


Karina_S
02.05.2011, 10:47
Hallo,

bevor ich jetzt gleich in meine Tastatur beiße, frag ich lieber mal hier nach.

Also, ich versuche grad eine kleine Vertrags-Datenbank mit Access 2003 aufzubauen. Soweit klappt das auch ganz gut und ich erinnere mich an vieles, was ich irgendwann mal gelernt habe. Jetzt komme ich aber zu folgendem Problem: Ich möchte in einer Abfrage die Summe seit Laufzeitbeginn abbilden. Dabei ist wichtig, dass sich bei manchen Verträgen (die Filterung krieg ich hin) der monatliche Betrag ab dem ersten Jahr um einen festgesetzten Prozentwert, z.b. 2% erhöht, nach dem 2. Jahr wieder um 2 % usw.

Im Moment löse ich das innerhalb der Abfrage so:

Summe seit Laufzeitbeginn: Wenn(DatDiff("m";[tab_vertragsdaten]![Beginn];Datum())<=12;[tab_vertragsdaten]![Monatsrate]*DatDiff("m";[tab_vertragsdaten]![Beginn];Datum());[tab_vertragsdaten]![Monatsrate]*DatDiff("m";[tab_vertragsdaten]![Beginn];DatAdd("yyyy";1;[tab_vertragsdaten]![Beginn]))+[indexierte Monatsraten]![Monatsraten indexiert]*DatDiff("m";DatAdd("yyyy";1;[tab_vertragsdaten]![Beginn]);Datum()))
(ich hoffe das kann man einigermaßen nachvollziehen. Die Grundidee ist: Prüfe ob die Vertragslaufzeit 1 Jahr oder weniger beträgt, falls ja, rechne Monatsbeitrag * Laufzeit in Monaten, falls die Laufzeit größer ist, addiere zu (Monatsbeitrag 1. Jahr * 12) den erhöhten Wert * die Laufzeit in Monaten ab dem 2. Jahr)

Das funktioniert auch soweit ganz gut. ABER: das rechnet ja nur 2 Jahre zurück, ist also für die Zukunft nicht wirklich brauchbar.

Mit VBA und programmieren im Prinzipiellen kenn ich mich etwas aus, mit VBA allerdings nur in Excel.
Von der Grundidee würd ich jetzt sagen, dass das ja eigentlich ein typischer Fall für eine For- oder If-Schleife ist, allerdings hab ich keine Ahnung, wie ich das passend in die Abfrage einbinden kann. Ich möchte nicht noch extra Buttons haben oder sonst irgendwas.

Weiß jemand Rat?

Schon mal vielen Dank für jede Hilfe oder Denkanstöße!! :)

hcscherzer
02.05.2011, 10:56
Ich würde das über eine extra Tabelle lösen, in der die Prozentwerte abhängig von der Laufzeit hinterlegt sind.

Atrus2711
02.05.2011, 10:57
Hi,

man könnte zwar einen Ansatz wie bei der Zinseszinsformel wählen, aber wenn es um Verträge geht, sag ich mir: Verträge kann man ändern. Und dann ist der "feste" Prozentsatz nicht mehr fest.

Daher schlage ich vor, die Monatsbeiträge für jeden Vertrag abzulegen:
VertragID DatumVon DatumBis Beitrag
DatumVon und -Bis sind dabei die Zeiträume, für die den Beitrag gilt. Wenn die Modalitäten wechseln, gibt es eine neue Zeile.

Diese Tabelle kann man in einer Pflegeroutine auch jährlich fortschreiben lassen ("2%-Inkrement") und ist damit lästige Routionearbeit los. Aber man kann für Einzelfälle immer noch manuell eingreifen.

Bei jeder "Nutzung" der Daten lässt sich der zum Zeitpunkt X aktive Satz ermitteln.

ebs17
02.05.2011, 11:17
Vertrags-Datenbank
Ehe Du loslegst: Im Versicherungsbereich ist es üblich, dass eine vereinbarte dynamische Erhöhung von Leistung und Beitrag durch den Versicherungsnehmer jährlich ausgeschlagen werden kann (dreimal in Folge, ohne dass das Recht auf dynamische Anpassung insgesamt erlischt).

Eine automatische Hochrechnung kannst Du dann also nur für eine Zielrechnung bei planmäßigem Ablauf vornehmen (= Modellrechnung). Das reale Verhalten des Versicherungsnehmers wird man aber nicht vorausberechnen können und somit eine jährliche Übernahme des Realzustandes (nach Ablauf der gültigen Fristen) in die Vertragsdaten notwendig werden.

Karina_S
02.05.2011, 11:31
Uh, schon so viele Antworten :)

@ebs17: Es handelt sich um Dienstleistungsverträge, die Indexierung wurde vereinbart, da kommen die auch nicht mehr raus, außer durch Kündigung. Vondaher kann also von einer jährlichen Fortschreibung ausgegangen werden.

@Atrus2711: Ich habe eine Tabelle mit den Vertragsdaten, in der die Daten soweit schon festgehalten sind. Auf die Idee, die hochgerechneten Daten dort zu hinterlegen bin ich allerdings noch nicht gekommen. Aber das müsste ich ja auch wieder manuell festlegen oder?

@hcscherzer: Ich habe eine extra Tabelle für die Vertragsdaten, da sind u.a. das Anfangsdatum, der Monatsbeitrag und der Index-Satz hinterlegt. Da sich ja evtl die Monatsbeiträge unterscheiden, müsste ich dann ja für jeden Vertrag ne extra Tabelle anlegen oder?

Atrus2711
02.05.2011, 11:35
da kommen die auch nicht mehr raus, außer durch Kündigung
Oder durch eine Klage auf Unwirksamkeit, der Recht gegeben wird.
Oder durch eine Gesetzesänderung.
Oder oder oder.
"pacta sunt servanda"? Ja. Bis zum wirksamen oder durchgesetzen Vertragsende.

Auf die Idee, die hochgerechneten Daten dort zu hinterlegen bin ich allerdings noch nicht gekommen. Aber das müsste ich ja auch wieder manuell festlegen oder?
Wie erwähnt: grundsätzlich ja, aber man kann diese lästige manuelle Arbeit durch Anfüge- und Aktualisierungsabfragen automatisieren. Wichtig ist die Einsicht, dass "2% auf 10 Jahre" kein gottgegebener Selbstläufer sind, sondern allenfalls ein Regelfall.

für jeden Vertrag ne extra Tabelle anlegen oder?
Galt zwar nicht mir, aber dennoch: Verträge sind grundsätzlich alle ähnlich und sollten damit auch grundsätzlich in einer Tabelle liegen. Evtl. Spezialfelder (z.B. je nach Vertragsgegenstand/Versichertem Risio oder whatever) können in Detailtabellen je Typ abgelegt sein. Aber das sind eben nur Spezialfelder.

Karina_S
02.05.2011, 11:40
ok, ich glaube ich darf bei meiner Datenbank davon ausgehen, dass die da nicht gegen kündigen ;) ist wie gesagt auch recht klein angelegt.

wie sähe das denn dann Praktisch aus? Lege ich in meiner Vertragsdaten-Tabelle prophylaktisch die Werte für 10 Jahre an und ziehe die dann zur Berechnung in die Abfrage, bei der ich dann allerdings immernoch die Wenn-Abfrage brauch zur laufzeitbestimmung?

Atrus2711
02.05.2011, 11:49
Hi,

da die Summe der Beiträge bis zum Laufzeitende ohnehin nur eine Erwartungsrechnung sein kann, würde ich dafür wohl eine Hilfstabelle aufbauen, die für jede Auswertung (die ja den Zeitraum vorgeben muss) neu gefüllt wird.

Diese Hilfstabelle hätte etwa folgenden Aufbau:
F_VertragID Laufdatum Beitrag
(= "wofür, welcher Monat(serste), welcher Betrag")

Diese Tabelle könnte durch eine Anfügeabfrage gespeist werden, die die Beitragshistorie ausliest, mit dem Auswertungszeitraum überlappt und die jeweils gültigen Beträge für jeden Monat in die Hilfstabelle einträgt. Der Überlapp der Beitragszeiträume mit dem Auswertungszeitraum kann durch eine For-Next-Schleife oder durch eine Kalendertabelle geschehen, die die Monatsersten als Datumsfelder enthält und einmalig für ein paar Dutzend Jahre gefüllt wird.

Die Hilfstabelle könnte zum Schluss auch in eine separate, temporäre Datenbank ausgelagert werden, damit das Komprimieren wegfällt.

Karina_S
02.05.2011, 11:51
uff ok, jetztb bitte inmal langsam...
Anfügeabfragen?

und wie mache ich das mit der Hilfstabelle praktisch?

Atrus2711
02.05.2011, 11:56
Du hast bei all deinen Erfahrungen ("erinnere mich", "mal gelernt", komplexe Wenn-Konstrukte) noch nichts von Anfügeabfragen gehört?

:entsetzt:

Kennst du das Access-Tutorial in meiner Signatur (ist nicht von mir)? Schau dir da mal die Abfrage-Rubrik an.

Und die Praxis der Tabellenanlage hast du doch auch schon geübt. Du hast doch schon Tabellen angelegt. Die Hilfstabelle wird eine weitere. Nur, dass deren Werte eben bei jeder Auswertung ersetzt werden und somit nicht permanent sind.

Karina_S
02.05.2011, 12:03
hmmm nein :o aber ok, hol ich nach, krieg ich hin ;)

bis jetzt habe ich fast immer nur mit permanenten werten gearbeitet... aber ich bastel mal rum ;)

danke schon mal für die Hilfe!! :)

ebs17
02.05.2011, 12:19
Summe seit Laufzeitbeginn
Diese wird dann auch unterjährig (zu beliebigen Monaten) benötigt?

Wenn die Verträge wie genannt fix sind, würde ich eine Zahlungstabelle wie von Martin genannt anlegen, allerdings nicht temporär. Mit Neuanlegen eines Vertrages würden dann die Datensätze mit Zahldatum (Monatserster) und Betrag eingetragen (per Recordsetschleife). Die Berechnung der dynamischen Erhöhungen wäre somit einmalig vorzunehmen. Aus der Beitragstabelle könnten dann zu einem beliebigen Zeitpunkt die Beitragssummen ermittelt werden.

Wie man die dynamischen Änderungen innerhalb einer Anfügeabfrage ermitteln kann, um sie direkt in die Tabelle zu bringen, würde ich gerne sehen.

Atrus2711
02.05.2011, 12:28
Wie man die dynamischen Änderungen innerhalb einer Anfügeabfrage ermitteln kann, um sie direkt in die Tabelle zu bringen, würde ich gerne sehen.

Die Dynamik liegt eher in den Sätzen, die den im Zeitraum jeweils gültigen Monatsbeitrag enthalten. Diese Tabelle lässt sich für Auswertungszwecke auslesen. Befüllt werden sollte sie bei Anlage oder eben (da die Zukunft ohnehin nicht feststeht) nach und nach, immer nur für einen "Festlegungszeitraum". Der aktuellste Stand ist eben immer nur der aktuellste Stand und gilt nur bis zum Beginn des nächsten Zeitraums.

Ein 5-Jahres-Vertrag soll 5 Jahre laufen. Er muss es nicht. Die einzige Kohle, die wirklich interssiert, ist der nächste Einzug. Alles andere ist Fiktion. Das halte ich auch immer den Kaufleuten vor, die mit Barwerten rumrechne (und ja, ich weiß wovon ich rede). :)

Karina_S
02.05.2011, 12:33
ahhhh verwirrung....
Die Verträge haben immer nur eine Laufzeit von einem Jahr mit einer Kündigungsfrist von 3 Monaten, werden sie nicht gekündigt, verlängert sich die Laufzeit um ein jahr und der Beitrag erhöht sich um 2 %.
Ist es möglich, aus der Tabelle mit den vertragsdaten eine Verknüpfung zu erstellen, je nachdem ob bei "gekündigt" ein Häkchen gesetzt ist oder nicht, so dass man das damit berechnen kann?

@Eberhard: Ja, ich möchte jederzeit nachschauen können, wie hoch der aktuelle Betrag seit Laufzeitbeginn ist

Atrus2711
02.05.2011, 12:40
Hi,

@Karina:
Das ändert nichts am Ablauf. Der Betrag ist phasenweise fest. Wie lang die Phasen sind, spielt für die Datenbank keine Rolle.

Du könntest bei Anlage des Vertrags und bei jeder Verlängerung (also regelmäßig) über die jetzt mehrfach erwähnten Wege die Beiträge für die nächsten 12 Monate erzeugen lassen. Ob es Nachfolgesätze dafür gibt, hängt dann von der Kündigung und/oder dem Erreichen einer evtl. Gesamtlaufzeit ab.

Erkenne das Prinzip.

Karina_S
02.05.2011, 12:42
Prinzip erkannt :)
Die Umsetzung ist mir noch nicht zu 100% klar, aber die Denkanstöße sollten ausreichen dass ich das hinbekomme!

Danke!!! :)

ebs17
02.05.2011, 12:51
Die angedeutete Beitragstabelle könnte man bei Neuanlegen eines Vertrages so füllen:
Sub NeueBeitraege(ByVal VertragID As Long, _
ByVal Startdatum As Date, _
ByVal LaufzeitMonate As Long, _
ByVal Beitrag As Double, _
ByVal Prozentsatz As Long)

Dim rs As DAO.Recordset
Dim i As Long

Set rs = CurrentDb.OpenRecordset("Beitragstabelle")
With rs
For i = 0 To LaufzeitMonate - 1
.AddNew
.Fields("F_VertragID") = VertragID
.Fields("Laufdatum") = DateAdd("m", i, Startdatum)
.Fields("Beitrag") = Beitrag * (1 + Prozentsatz / 100) ^ (i \ 12)
.Update
' Debug.Print i + 1, DateAdd("m", i, Startdatum), _
' i \ 12, _
' 1 + Prozentsatz / 100, _
' (1 + Prozentsatz / 100) ^ (i \ 12), _
' Beitrag * (1 + Prozentsatz / 100) ^ (i \ 12)
Next
.Close
End With
Set rs = Nothing
End Sub
Für eine Summenermittlung muss man dann nur die Beiträge gewünschter Verträge und eines gewählten Zeitraumes summieren. Das geht dann im Rahmen einer einfachen Abfrage.

Wenn die Verträge nicht fix, sondern jährlich kündbar sind, kannst Du eben immer nur um eine Laufzeit von 12 Monaten verlängern.

Karina_S
02.05.2011, 13:15
Wow, danke!

Ich werd das mal ausprobieren, versuche es grad noch irgendwie ohne VBA hinzukriegen.