Hallo Zusammen,
ich habe mich vor einigen Jahren etwas mit Access und VBA beschäftigt, leider ist aus dieser Zeit nicht viel in meinem Kopf geblieben. Vielleicht könnt Ihr mir auf die Sprünge helfen.
Ich möchte eine DB für unseren Haushalt erstellen, in der ich zu zahlende Rechungen (Arztrechnungen, Autoreparaturen, Gas, Wasser, Strom, usw.) verwalte. Nun gibt es Posten (wie etwa die Abschläge für Strom), die sich in bestimmten Intervallen wiederholen.
Ich habe die Tabelle tbl_Rechung angelegt. So gibt es nun einen Datensatz
RID 1; Rechnungssteller RWE; Betrag 178 €; Fälligkeit 15.10.2010; wiederkehrend ja; Intervall monatlich; usw...
Wenn die Datenbank geöffnet wird, soll automatisch geprüft werden, ob es DS gibt, bei denen in den nächsten 14 Tagen eine neue Rechnungsfälligkeit entsteht (also wiederkehrend = ja; neue Fälligkeit = Fälligkeit plus Intervall < heute +14 Tage
Wenn dem so ist, soll einer neuer DS mit der neuen Fälligkeit angelegt werden, bei dem alten DS muss wiederkehrend auf nein gesetzt werden.
Abgesehen davon, dass ich sicherlich auch Hilfe bei der Syntax benötige. Ich habe keinen blassen Schimmer mehr, wo das alles hingehört!? Benötige ich dazu eine Abfrage, die prüft, welche DS zur "Erneuerung" anstehen? Wenn ja...das was mit den Datensätzen geschehen soll...gehört das auch noch in die Abfrage (RecordSet? in der Abfrage?)
Vielen Dank für Eure Hilfe,
Tom
Hallo Tommy!
Erstelle eine Abfrage. Gehe bitte auf die SQL-Ansicht und werfe folgendes ein:
Select Rechnungsersteller,Betrag, Faelligkeit from tbl_rechnung where Faelligkeit between date() and date() +14
Anschließend kannst Du in die Entwurfsansicht gehen und Dir anschauen, was rauskommt.
Statt "Fälligkeit" habe ich "Faelligkeit" geschrieben. Tabellenfelder mit Umlauten sollte man vermeiden.
Um diese Abfrage bei Start Deines Programms aufzurufen musst Du ein Formular entwerfen, dass die Datensätze auf Basis der obigen Abfrage zeigt.
Neues Formular als Tabelle etc.
Hallo Shai,
vielen Dank für Deine Hilfe. Aber so einfach ist es leider nicht. Die nächste Fälligkeit muss ja erst aufgrund von "Faelligkeit" und "Intervall" berechnet werden.
Beispiel: Rechnung angelegt mit Fälligkeit 01.10.2010, Intervall "alle 2 Monate". Dann muss doch erst berechnet werden, dass die nächste Fälligkeit am 01.12.2010 ist, und dann erst, ob "nächste Fälligkeit" minus Heute kleiner gleich 14 Tage ist.
Oder habe ich da etwas falsch?
Danke,
Tom
Ok, das habe ich jetzt verstanden.
Damit ändert sich die Komplexität.
Das Hauptproblem ist die Art und Weise, in der der Intervall gespeichert ist.
Im Idealfall ist das eine Zahl und eine Einheit, sauber getrennt in 2 Feldern.
z.B.
Intervallfaktor: 2, Intervalleinheit: Monat -> "alle 2 Monate"
Intervallfaktor: 14, Intervalleinheit: Tag -> "alle 14 Tage"
Intervallfaktor: 1, Intervalleinheit: Jahr -> "1x pro Jahr"
Du legst also sinnigerweise eine Tabelle "tbl_Intervall" dafür an.
Felder:
- IntervallID: Autowert
- Faktor: Zahl (Long Integer)
- Einheit: Text
- Beschreibung: Text
Die Werte für die Einheit dürfen folgende Werte haben:
yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Hintergrund: Die Einheit wird direkt an die Funktion DateAdd weitergereicht, die wir benutzen werden.
Inhalt der Tabelle tbl_Intervall z.B.
ID, Faktor, Einheit, Beschreibung
1, 2, "m", "alle 2 Monate"
2, 14, "d", "alle 14 Tage"
3, 1, "yyyy", "1x pro Jahr"
4, 3, "m", "pro Quartal"
5, 6, "m", "halbjährlilch"
In der Rechnungstabelle muss es dann ein Feld "InvervallID", Typ Zahl (Long Integer) geben, dazu eine relationale Verknüpfung zwischen Rechnungstabelle und Intervalltabelle, die Verknüpfung erfolgt über die IntervallID
In der Erfassung kannst Du z.B. über ein Kombifeld einer Rechnung einen Intervall zuweisen.
So und wenn Du das alles fertig hast, dann erstellst Du eine Abfrage:
SELECT Rechnungsersteller,Betrag, Faelligkeit as LetzteFaelligkeit,
DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) as Termin
FROM tbl_rechnung INNER JOIN tbl_Intervall ON tbl_rechnung.IntervallID = tbl_Invervall.IntervallID
WHERE DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) between date() and date() +14
Oder aufteilen in 2 Abfragen: Speichere folgendes unter dem Namen "qryTermine" ab
SELECT Rechnungsersteller,Betrag, Faelligkeit as LetzteFaelligkeit,
DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) as Termin
FROM tbl_rechnung INNER JOIN tbl_Intervall ON tbl_rechnung.IntervallID = tbl_Invervall.IntervallID
und beziehe Dich darauf:
SELECT * FROM qryTermine WHERE Termin between date() and date() +14
Was mich jetzt noch interessiert: Wie willst Du eine Zahlung verwalten?
Wenn nämlich die letzte Rechnung am 15.10. war, und Du am 29.10, also 14 Tage später eine neue Zahlung tätigst, überschreibst Du einfach das Feld "Fälligkeit" durch das neue Datum?
Hallo Shai,
zu Deiner Frage nach der Verwaltung einer Rechnung. Nein, ich möchte nicht überschreiben. Alle DS im Ergebnis der Abfrage soll ein neuer DS hinzugefügt werden, mit der neuen Fälligkeit. Gleichzeitig soll bei dem "alten DS" das Ja/Nein-Feld "Wiederkehrend" auf nein gesetzt werden.
Danke für Deine Mühe, ich werde mich heute Abend mit Deiner Abfrage beschäftigen. Vielen Dank schon mal, ich melde mich, wenn ich soweit bin.
Gruß,
Tom
Mir fällt gerade auf, dass ich diese Einschränkung mit dem wiederkehrend gar nicht gemacht habe.
Vielleicht kannst Du das ja noch selber mit hinzufügen.
Hallo Shai,
sorry, ich war sehr spät zu Hause und konnte Deine Ideen leider nicht mehr umsetzten, werde es aber spätestens Sonntag probieren.
Vorab: Was bedeutet in der ersten Zeile der Abfrage "Faelligkeit as LetzteFaelligkeit"?
"LetzteFaelligkeit" ist doch weder ein Feld in meiner Tabelle, noch wird dieser Begriff wieder in der Abfrage aufgegriffen?
Danke,
Tom
Du erfasst Rechnung in Deinem Haushaltsprogramm. Diese erfassten Rechnungen bilden die Basis für die Auswertung, die die zukünftigen Fälligkeiten ermittelt.
Für die Auswertung stellt das Feld "Fälligkeit" aus der Tabelle tbl_Rechnung die letzte Fälligkeit dar, auf Basis dessen die nächste Fälligkeit bestimmt werden soll.
Deshalb habe ich das Feld für die Auswertung umbenannt.
Aber OK, geht auch anders.
Hier die entsprechend korrigierten Abfragen, auch die zusätzliche Bedingung für wiederkehrende Rechnungen
zusammen
SELECT tbl_Rechnung.Rechnungsersteller,tbl_Rechnung.Betrag,tbl_Rechnung.Faelligkeit,
DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) as NaechsteFaelligkeit
FROM tbl_Rechnung INNER JOIN tbl_Intervall ON tbl_Rechnung.IntervallID = tbl_Invervall.IntervallID
WHERE (DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) between Date() and Date() +14)
AND (tbl_Rechnung.wiederkehrend = true)
oder getrennt. Das folgende als "qryNaechsteFaelligkeit" speichern.
SELECT tbl_Rechnung.Rechnungsersteller,tbl_Rechnung.Betrag,tbl_Rechnung.Faelligkeit,
DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.Faelligkeit) as NaechsteFaelligkeit
FROM tbl_Rechnung INNER JOIN tbl_Intervall ON tbl_Rechnung.IntervallID = tbl_Invervall.IntervallID
WHERE (tbl_Rechnung.wiederkehrend = true)
und hier einsetzen:
SELECT * FROM qryNaechsteFaelligkeit WHERE NaechsteFaelligkeit between date() and date() +14
Hallo Shai,
vielen Dank für Deine Hilfe. Es funktioniert. Ich habe es in eine Abfrage gepackt, die so aussieht:
SELECT tbl_Rechnungssteller.RSName AS Ausdr1, tbl_Rechnungssteller.RSVorname AS Ausdr2, tbl_Rechnung.RID, tbl_Rechnung.RNummer, tbl_Rechnung.RBetrag, tbl_Rechnung.RDatum, tbl_Rechnung.RFaelligkeit, tbl_Rechnung.wiederkehrend, tbl_Rechnung.Intervall, tbl_Rechnung.bezahlt, tbl_Rechnung.ZahlungsArt, tbl_Rechnung.bezahltam, DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.RFaelligkeit) AS NaechsteFaelligkeit
FROM tbl_Rechnungssteller INNER JOIN (tbl_Rechnung INNER JOIN tbl_Intervall ON tbl_Rechnung.Intervall=tbl_Intervall.IntervallID) ON tbl_Rechnungssteller.RsID=tbl_Rechnung.RsID
WHERE (((tbl_Rechnung.wiederkehrend)=True) And ((DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.RFaelligkeit)) Between Date() And Date()+14));
Ich habe noch eine Sache überdacht. Schön wäre, wenn nicht nur die naechstenFaelligkeiten der Zukunft, sondern auch der Vergangeheit, also zwischen Fälligkeit und heute, ausgeworfen würden. Angenommen ich habe eine Rechnung mit Fälligkeit 01.09.2010, Intervall monatlich. Und ich starte die Datenbank aber erst wieder am 15.10.2010. Dann wird die Fälligkeit am 01.10.2010 nicht berücksichtigt.
also anstelle von Between Date() And Date()+14
sowas wie Between tbl_Rechnung.RFaelligkeit And Date()+14
Allerdings müssten dann nicht nur die naechte Faelligeit sondern die naechsten Faelligkeiten angezeigt werden, dann der 01.11.2010 wäre ja auch schon in Sichtweite.
Zu den Datensätzen, die die Abfrage auswirft, sollen jetzt neue Datensätze mit der neuen Fälligkeit erstellt werden. Ich habe mir einiges über RecordSets angesehen. Aber wo kommt der Code hin? Und wie spreche ich das Ergebnis der Abfrage an. Also "Zu allen Datensätzen aus dem Ergebnis der Abfrage abf_faellig14Tage erstelle neue Datensätze mit Faelligkeit gleich naechsteFaelligkeit"?
Danke,
Tom
Hallo Tom,
Zu Deiner ersten Anforderung:
Ich würde es so machen, dass Du eingeben kannst, für welchen Zeitraum die Abfrage gelten soll. Sonst guckst Du (nach ausgedehntem Urlaub) erst nach 3 Wochen in Dein Programm und schon kappen Sie Dir Strom, Wasser und Telefon, weil Du eine Woche übersehen hast. :D
Schreibe dazu in die Abfrage statt "Between Date() And Date()+14" sowas wie "Between [VonDatum] And [BisDatum]" rein.
Beim Aufruf wirst Du nun entsprechend gefragt.
Du musst das Datum folgendermaßen erfassen: mm/tt/jjjj, also mit / statt mit Punkt und dazu auch noch in englischer Reihenfolge, Monat vorne.
Gewöhnungsbedürftig!
Zu unkomfortabel?
Nächste Komfortstufe:
- Du erstellst ein Formular, Name z.B. "frmAuswertung", auf dem Du VonDatum und BisDatum eingeben kannst.
- In die Abfrage baust Du dann statt "Between Date() And Date()+14" sowas wie "Between Forms!frmAuswertung!VonDatum And Forms!frmAuswertung!BisDatum" ein.
- Dazu ein Button, mit dem Du die Abfrage startest .
Leider muss das Datum immer noch genau so umständlich erfasst werden.
Also nochmal anders.
Du willst doch sowieso einen Bericht erstellen, der Dir die fälligen Rechnungen zeigt, oder? *ganz feste zwinker*
Beim Aufruf eines Berichts gibt es nämlich die Möglichkeit, eine vorherige Selektion zu übergeben.
Du müsstest die grundsätzliche Abfrage so abändern, wie ich das bei meiner 2. Variante gemacht habe.
Diese ist dann Datengrundlage des Berichtes "rptFaelligkeit".
Ich habe übrigens "Ausdr1" und "Ausdr2" durch sinnvollere Namen ersetzt. ;)
SELECT tbl_Rechnungssteller.RSName AS RSName, tbl_Rechnungssteller.RSVorname AS RSVorname, tbl_Rechnung.RID, tbl_Rechnung.RNummer,
tbl_Rechnung.RBetrag, tbl_Rechnung.RDatum, tbl_Rechnung.RFaelligkeit, tbl_Rechnung.wiederkehrend, tbl_Rechnung.Intervall, tbl_Rechnung.bezahlt,
tbl_Rechnung.ZahlungsArt, tbl_Rechnung.bezahltam, DateAdd(tbl_Intervall.Einheit,tbl_Intervall.Faktor,tbl_Rechnung.RFaelligkeit) AS NaechsteFaelligkeit
FROM tbl_Rechnungssteller
INNER JOIN (tbl_Rechnung
INNER JOIN tbl_Intervall ON tbl_Rechnung.Intervall=tbl_Intervall.IntervallID)
ON tbl_Rechnungssteller.RsID=tbl_Rechnung.RsID
WHERE (((tbl_Rechnung.wiederkehrend)=True));
Wenn man sich einen Button generieren lässt, der einen Bericht aufruft, dann erstellt Access Dir eine Ereignisprozedur. Diese änderst Du wie folgt ab
Beim Aufrufbefehl "DoCmd.OpenQuery" wird am Schluss die Selektion übergeben.
Private Sub btnBericht_Click()
On Error GoTo Err_btnBericht_Click
Dim stDocName As String
Dim strVonDatum As String
Dim strBisDatum As String
Dim datVonDatum As Date
Dim datBisDatum As Date
datVonDatum = CDate(Nz(Me!VonDatum, 0))
datBisDatum = CDate(Nz(Me!BisDatum, 0))
strVonDatum = GetSQLDatum(datVonDatum)
strBisDatum = GetSQLDatum(datBisDatum)
stDocName = "rptTestWA"
DoCmd.OpenReport stDocName, acPreview, , "[NaechsteFaelligkeit] Between " & strVonDatum & " And " & strBisDatum
Exit_btnBericht_Click:
Exit Sub
Err_btnBericht_Click:
MsgBox Err.Description
Resume Exit_btnBericht_Click
End Sub
Zusätzlich brauchst Du diese kleine Funktion, um das Datumsformat zu verändern:
Public Function GetSQLDatum(ByVal datDatum As Date) As String
GetSQLDatum = Format(CDate(datDatum), "\#mm\/dd\/yyyy\#")
End Function
Sooo, bau das erstmal. :D
Danach gucken wir mal, wie wir der automatischen Anlage neuer Datensätze für die neuen Fälligkeiten hinbekommen.
Schon mal vorweg das Grundprinzip: Man ändert dazu die Abfrage zu einer Anfügeabfrage.
Wie stellst Du Dir das ablauftechnisch vor?
Gruß Wilfried
Hallo Wilfried,
ich habe ein paar Fragen:
ZitatIch würde es so machen, dass Du eingeben kannst, für welchen Zeitraum die Abfrage gelten soll. Sonst guckst Du (nach ausgedehntem Urlaub) erst nach 3 Wochen in Dein Programm und schon kappen Sie Dir Strom, Wasser und Telefon, weil Du eine Woche übersehen hast.
Aber gerade wenn ich den Zeitraum selber vorgebe, laufe ich doch Gefahr, dass eine neue Fälligkeit nicht generiert wird, oder? Daher dachte ich eben an eine automatisierte Variante.
ZitatDu willst doch sowieso einen Bericht erstellen, der Dir die fälligen Rechnungen zeigt, oder?
Ja, aber ein Bericht der mir dann eben die fälligen Zahlungen anzeigt. So weit sind wir doch noch nicht. Jetzt sollen doch erstmal Rechnungen selektiert werden, zu denen eine neue Fälligkeit generiert werden muss... ICh bin jetzt etwas verwirrt.
ZitatDanach gucken wir mal, wie wir der automatischen Anlage neuer Datensätze für die neuen Fälligkeiten hinbekommen.
Schon mal vorweg das Grundprinzip: Man ändert dazu die Abfrage zu einer Anfügeabfrage.
Kann eine Anfügeabfrage denn auch den bestehenden Datensatz ändern, oder nur einen neuen DS erstellen? Bei dem alten DS muss ja "wiederkehrend" auf nein gesetzt werden.
ZitatWie stellst Du Dir das ablauftechnisch vor?
Ich gebe Rechnungen in die Datenbank ein. Über verschiedene Abfragen, kann ich mir dann z.B. anzeigen lassen, welche Rechnungen in den nächsten 14 Tagen fällig werden und noch nicht bezahlt sind.
Jetzt gibt es auch Rechnungen, die immer wieder fällig werden, und sich nur durch das Fälligkeitsdatum unterscheiden. Diese Rechnungen haben das Merkmal "wiederholend" = ja und ein bestimmtes Intervall. Wir waren ja schon soweit, dass in dem Ergebnis einer Abfrage zu diesen DS angezeigt wird, wann die nächste Fälligkeit ist. Das hat mir sehr gut gefallen. Jetzt soll ein neuer Datensatz erstellt werden, mit weitestgehend den gleichen Angaben, RFaelligkeit = NaechsteFaelligkeit us der Abfrage, "Wiederkehrend" = ja; der alte Datensatz soll verändert werden in "wiederkehrend" = nein. Das würde dazu führen, dass immer wieder eine neue Fälligkeit erzeugt würde, solange ich den aktuellen Datensatz auf "wiederkehrend" = ja belasse.
Was sagst Du dazu?
Gruß und einen schönen Sonntag,
Tom
Ja, da ist was dran, wenn Du den Zeitraum selbst wählen kannst, könntest Du etwas übersehen.
Die Automatik müsste auf die zuletzt bezahlte Rechnung basieren, habe ich Recht?
Der Prozess, Rechnungen zu selektieren, auf dessen Basis neue Fälligkeiten generiert werden ist im Prinzip doch der gleiche.
Der Bericht, der die fälligen Zahlungen zeigt, darf also auf den berechnete Fälligkeiten basieren, oder?
Nein, eine Anfügeabfrage fügt etwas an, wie der Name schon sagt, eine Änderungsabfrage ändert dagegen etwas.
Irgendwie gefällt mir der Ablauf noch nicht. Wiederkehrend-Kennzeichen, Neue Datensätze für Fälligkeiten generieren ...
Hast Du schon mal überlegt, wie die Banken das machen? Da kann man einen Dauerauftrag für wiederkehrende Rechnungen anlegen. So ein Dauerauftrag enthält die ganzen Zahlungsinformationen, dazu einen Zahlungsturnus und die Information, wann die letzte Zahlung erfolgt ist. Die eigentlichen Zahlungen laufen im Prinzip parallel.
Übertragen auf Deine Aufgabenstellung hätten wir eine Tabelle, in der die "Daueraufträge" verwaltet werden und eine weitere Tabelle, in der die Zahlungen gespeichert sind.
Für die Berechnung der Fälligkeiten wird einzig und allein die Tabelle für die Daueraufträge verwendet. In dieser Tabelle wird bei jeder Zahlung einfach immer wieder die nächste Fälligkeit eingetragen. Nicht wiederkehrende Rechnungen tauchen gar nicht erst in den Daueraufträgen auf.
Was hälst Du von dem Konzept?
Hallo Wilfried,
das Grundkonzept verstehe ich. Du willst die Ausgangsdatensätze von den sich wiederholenden DS trennen? Also auf Basis A wird B, C, D, E usw. generiert und nicht aus A wird B, aus B wird C, aus C wird D???
Für die Umsetzung fehlt es mir allerdings.
Bei dem alten Vorhaben stoße ich gerade aber auch an meine Grenzen. Ich habe es hinbekommen, über ein RecordSet .Edit den alten Datensatz zu verändern (wiederholend = false); aber ich weiß nicht, wie ich allen DS aus dem Ergebnis der Abfrage jeweils einen neuen DS hinzufüge. Denn über eine Schleife laufe ich ins Endlose...
Gruß,
Tom
P.S.
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("abf_Rechnung", dbOpenDynaset)
With rst
If .Updatable Then
While Not .EOF
.Edit
!wiederkehrend = False
.Update
.MoveNext
Wend
End If
End With
With rst
If .Updatable Then
Move Last
While Not .BOF
.AddNew
!wiederkehrend = True
.Update
.MovePrevious
Wend
End If
End With
'With rst
' If .Updatable Then
' .AddNew
' !RBetrag = Me.RBetrag
' .Update
' End If
'End With
rst.Close
Ich habe nur einen DS in der Tabelle und somit auch in der Abfrage. Warum werden mir 3 neue DS mit dem Code generiert???
Ich habe inzwischen herausgefunden, dass immer genau 2 DS zuviel erstellt werden.
Jemand ne Ahnung?
Danke,
Tom
Hallo,
anbei mal eine kleine DB wie ich es machen würde.
Ich hab aus faulheit den tabellen keine namen gegeben daher
Tabelle5 Liste aller Rechnungen
Tabelle6 Liste der bezahlten Rechnungen
Formular4 zeigt an welche Rechnungen in innerhalb von 14 Tagen zu zahlen sind und die Rechnungen bei denen der zahlungstermin überschritten wurden. Evtl.nicht ganz was du suchst aber manchmal braucht man nur nen kleinen Hinweis um auf eigene Ideen zu kommen.
Der legt gleich 2 Datensätze an, weil der auf Basis des neu angelegten Datensatzes gleich noch einen anlegt.
Probiers mal so:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL as string
Set db = CurrentDb()
Set rst = db.OpenRecordset("abf_Rechnung", dbOpenDynaset)
Set rst2 = db.OpenRecordset(tlb_Rechnungssteller",dbOpenDynaset)
With rst
If .Updatable Then
While Not .EOF
.Edit
!wiederkehrend = False
.Update
With rst2
.AddNew
!RSName = rst!RSName
!RSVorname = rst!RSVorname
!RBetrag = rst!RBetrag
' [...] usw. alle anderen Felder der Rechnungstabelle hier entsprechend eintragen.
!wiederkehrend = true
.Update
End With
.MoveNext
Wend
End If
End With
Erklärung: Auf Basis der Abfrage werden in der Tabelle tbl_Rechnungsersteller neue Datensätze angelegt und bei dem alten das Feld "wiederkehrend" auf false gesetzt.
Danke @lumbumba. Ich werde in den nächsten Tagen wieder sehr wenig Zeit haben. Vielleicht kannst Du weitermachen?