Payment Plan Database

[jthorneuk]

Hi, I hope you can help. I am setting up a membership payment database
to take over a manual paper folder and I need a little help.

What I have is a from that adds the members to the database and sets
out their payment plans. They have 4 different ways they can pay.
Monthly on the 1st, Monthly on the 10th, Every 4 weeks and every 2
weeks These are selected then stored from a pull down box.

What I am trying to do is imput in all of the current members and
allow for future members. Because of the history there are different
payment dates and and plans for different members.

I need to find a way (and im totally clueless) that I can have the
database show a form of everybody that owes money as of today and then
I can tick them to remove them from the list as and when they pay what
they owe. So on a daily bases i can see who gave me money then go in
and tick them.

The problem I am having getting my head around is that I need the
database to work out what their payment dates are automaticly. so....

Mr J Bloggs. Joined 23/10/09 paying 4 weekly so he needs to pay again
on 20/11/09 then again on 18/12/09 and so on....

Miss S Smith joined on 01/10/09 paying monthly so needs to pay on
01/11/09 and 01/12/09 and so on.....

What I dont know is how i get this table generated.. I know if i had
the figures already typed in then I could create a form that only
shows payments due up untill todays date and lets me tick them when
they arrive and that then removes them from the list for the next time
i view it.. then when the date passes their next payment date it then
shows up their next payment in the list...


So i guess the question is... How do i generate a ongoing list of
payment dates per person based on their joining date and payment
plan ???

Any help would be fantastic!!!

 

[John W. Vinson]

So i guess the question is... How do i generate a ongoing list of
payment dates per person based on their joining date and payment
plan ???

What's your current table structure? I would strongly suggest a table of
Payments (with a DatePaid field, and perhaps an AmountPaid field if you accept
partial payments or advance payments), and some sort of Append query to
populate it... but without knowing what you're currently storing it's hard to
be specific.

 

[jthorneuk]

What's your current table structure? I would strongly suggest a table of
Payments (with a DatePaid field, and perhaps an AmountPaid field if you accept
partial payments or advance payments), and some sort of Append query to
populate it... but without knowing what you're currently storing it's hard to
be specific.

Currently I store the "join date" and the "payment cycle" then there
is a total amount. This part of the database is very new so i can
store whatever however as its a bit of a bult on as the original
design was just to fill out the membership forms on screen so they
could be printed...

So the payments table currently stores the join date" and the "payment
cycle" then the database needs to take the "join date" and then using
the "payment cycle" it will know what the "due date" is then i mark
this as paid as and when it gets paid. Then i guess the database then
needs to update so that it takes the last due date as the join date
and then works out the next new "due date" for that person and enters
it into the same table and awaits confirmation that its been paid
before it does the same process again.....

Im guessing an append queary would be the way to go and have it so
when i clicked a button it entered the information into the table in
the right places and then created a new line filling in the info in
the right places again but also calculating the next due date at the
same time..... problem is i dont know how to do that at all :-(

 

[John W. Vinson]

Currently I store the "join date" and the "payment cycle" then there
is a total amount. This part of the database is very new so i can
store whatever however as its a bit of a bult on as the original
design was just to fill out the membership forms on screen so they
could be printed...

So the payments table currently stores the join date" and the "payment
cycle" then the database needs to take the "join date" and then using
the "payment cycle" it will know what the "due date" is then i mark
this as paid as and when it gets paid. Then i guess the database then
needs to update so that it takes the last due date as the join date
and then works out the next new "due date" for that person and enters
it into the same table and awaits confirmation that its been paid
before it does the same process again.....

Im guessing an append queary would be the way to go and have it so
when i clicked a button it entered the information into the table in
the right places and then created a new line filling in the info in
the right places again but also calculating the next due date at the
same time..... problem is i dont know how to do that at all :-(

Again:

Please post the *ACTUAL STRUCTURE* of your tables.

I'd suggest a format such as

Tablename
Field (Datatype)
Field (Datatype)

e.g.

Payments
PaymentID (Autonumber, primary key)
JoinDate
PaymentCycle

using your actual fieldnames. Post some examples of what is actually stored in
your table for the payment cycle, I don't know what you're storing there!

You will *quite certainly* need at least two tables. A Member is a different
kind of entity than a Payment; you will need a Members table, with a unique
MemberID, biographical information, and a JoinDate; this should be related one
to many to a Payments table, since each member will make multiple payments.

 

[jthorneuk]

Again:

Please post the *ACTUAL STRUCTURE* of your tables.

I'd suggest a format such as

Tablename
  Field (Datatype)
  Field (Datatype)

e.g.

Payments
  PaymentID (Autonumber, primary key)
  JoinDate
  PaymentCycle

using your actual fieldnames. Post some examples of what is actually stored in
your table for the payment cycle, I don't know what you're storing there!

You will *quite certainly* need at least two tables. A Member is a different
kind of entity than a Payment; you will need a Members table, with a unique
MemberID, biographical information, and a JoinDate; this should be related one
to many to a Payments table, since each member will make multiple payments.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

OK I think this is what you are looking for...

In the Admissions table I store

Admissions
NINumber
Title
Firstname
Middlename
Sirname
Address
DOB
AdmissionDate


Then in the Payments table I store

Payments
NINumber
Funding
Fpw
Fpm
Cycle
DueDate
Paid



FPW is funding per week, fpw is funding per month
This is because people have a different payment amount depending on
what they use and thats a weekly fee thats then worked out into a
monthly fee

Cysle is either 1st month, 10th, month 4 weeks or 2 weeks.

What I need i guess is the append queary to add extra lines to the
payments table as i click paid. It needs to work out the next due date
for the new entery from the "cycle" and "due date" fields to calculate
the next due date...


Hope this is what you needed to know. Im new to this...