Payments Schedule

[John]

Hi Folks,
I am really stuck on this problem and don't know how to
approach it. I have a Database comprising a Table of members of a club and I
need to track donations and payments made by them as they fall due. If there
are weekly monthly or biannual payments made by different members how do I
generate a weekly report showing non payment as they occur. Do I need to
make a separate table as a "payment due" schedule or what.I already have a
table for tracking timely payments which are made.Any advice gratefully
accepted. John

 

[John Vinson]

Hi Folks,
I am really stuck on this problem and don't know how to
approach it. I have a Database comprising a Table of members of a club and I
need to track donations and payments made by them as they fall due. If there
are weekly monthly or biannual payments made by different members how do I
generate a weekly report showing non payment as they occur. Do I need to
make a separate table as a "payment due" schedule or what.I already have a
table for tracking timely payments which are made.Any advice gratefully
accepted. John

Of course you need the information *somewhere* that a payment is due,
otherwise it's impossible to tell when one is missing! It probably
need not be a Table though.

Where is the information that a payment is expected weekly, monthly,
or biennially stored? In the Member table? How is it stored - what
field or fields, what do they contain?

I think a Query adding the appropriate time unit to the date of most
recent payment would work - but I need to know how that time unit is
stored to provide an answer.

John W. Vinson[MVP]

 

[John]

Hi John,
my thinking on it was as follows. I would add a field to the
members table called "members initialisation date" showing a date from which
I could schedule payments for that member. I would also have a "membership
category" showing the type of membership payment preferred,e.g.
weekly,monthly,biannually,etc.. Then using these two fields I could possibly
generate an annual payment schedule for each member at the start of each
year.
Otherwise I could somehow create a payment schedule for each member as dates
fall due, dynamically if you like. This is probably a more efficient/correct
way of doing things.At the start of each month I want to be able to print
details of late or overdue payments and subsequently to print a reminder
letter where appropriate, complete with mailing address labels. Am I going
about this in the right way or is there a known convention for designing
such a system. John

 

[John]

Time unit I was thinking of is days (24hrs)....John

 

[Joe Cilinceon]

I'm doing something similar right now. I'm using a query based on the latest
paidthru date in a system for tracking storage unit rental. At the moment I
have a payment ledger that quary for LedgerID (custno & unit) and the Max(
PaidThru date ) and on that query I built a second query that will gererate
the amounts due based on the current paidthru date. I also have to figure
things like late fees that are applied on the 10th day of being late etc.
I've been using it now for awhile and find it dead on accurate. Now if I
could figure out the base way to store what each item is a payment covers I
would be done.

 

[John Vinson]

Hi John,
my thinking on it was as follows. I would add a field to the
members table called "members initialisation date" showing a date from which
I could schedule payments for that member. I would also have a "membership
category" showing the type of membership payment preferred,e.g.
weekly,monthly,biannually,etc.. Then using these two fields I could possibly
generate an annual payment schedule for each member at the start of each
year.
Otherwise I could somehow create a payment schedule for each member as dates
fall due, dynamically if you like. This is probably a more efficient/correct
way of doing things.At the start of each month I want to be able to print
details of late or overdue payments and subsequently to print a reminder
letter where appropriate, complete with mailing address labels. Am I going
about this in the right way or is there a known convention for designing
such a system. John

I like your first alternative better. For convenience you might want
to add an Integer RenewalInterval field - with values 7 for weekly, 30
(rough but probably adequate) for monthly, 180 (ditto) for semiannual.
A Query based on this table in conjunction with the payments table
would work. You may not even need the [Members initialization date]
field in the Members table unless you need it for other purposes; you
could use the NZ() function to get a default date if the member has
had no payments at all, or you could add a "starter" record to the
payments table.

Let's consider a Query:

SELECT LastName & ", " & FirstName AS Member,
DateAdd("d", [Members].[RenewalInterval], NZ(DMax("[PaymentDate]",
"Payments", "[MemberID] = " & MemberID), [Members].[InitialDate])))
AS DateDue, <etc>
From MEMBERS;

This would find the most recent payment if any, and add the desired
number of days to calculate the next due date.

John W. Vinson[MVP]

 

[John]

Thanks for the help there guys, Im away for the night to try yer
suggestions. John