Student class payments

  • Thread starter Thread starter Nomy
  • Start date Start date
N

Nomy

Hi,

I building a database for karate classes and payments. I have these tables:
tblStudents - for basic info (name, address etc)
tblKarateCls - a lookup table for the classes
tblEnrollment - a join table with these fields:
EnrollmentID - PK,
StudentID - Foriegn key - to relate to the student table,
KarateClsID - foriegn key to relate to the karate class table, and
additional fields that only pertain to that
student for that class for that year like StartDate, EndDate, TuitionAmt
etc.

tblPayment with DtPd, AmtPd, LtFee (if appropriate).

I can't figure out how to record the payments. Should I build a form based
on tblEnrollment as the parent form and a subform on tblPayments? This
would give me a list of payments for each student but then every payment
must be entered in a separate form (based on the tblEnrollment).

Is there a better way?

Thanks.
 
It depends on how and what they pay for. Do they pay per class or do they pay
one bill for all classes?
Are partial payments possible?
If a student owes $10 for Class A and $10 for Class B and they pay $15, how
do you allocate the payments?
You need to figure out the 'business rules' before you design your database.
Your Payments table will probably need to link to your Enrollment table so
you know what payment applies to what class..
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
I expect there should be a cost field in tblKarateCls. I would create a
payment table that is independent of the enrollments. This would allow for
partial payments or payments that would cover the end of one class and the
beginning of another.

Don't forget the studentId in the payment table.

You should only need to compare the total class cost vs the total payments
made.
 
Dorian,
The students pay either monthly or quarterly on an annual basis. The
tuition cost is in the tblKarateCls table. Sometimes two children in a
family are students and the parents will pay one check for both. A payment
amount is posted for each child. Yes, partial payments could be made. If
$10 is paid for class A and only $5 is paid for class B, then I have to show
that the student owes $5 for class B plus a late fee.

Thanks.
 
Keep in mind in your example that the $5 still owed by the student for Class
B could either be stored (BAD IDEA!) or calculated (GREAT IDEA!). The total
of all amounts received on behalf of that student, less the total of all
amounts credited on behalf of that student = "amount owed".

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,
Would you store the DtDue and AmtDue (the monthly class fee) in the payment
table? I added these fields to my payment table: DtDue and AmtDue.
Fields now are DtDue, AmtDue, DtPd, AmtPd and LtFee.

My objective when I build the forms is to see the term for the class
(BeginDt and EndDt), the full tuition, the monthly fee in the Parent form.
The first installment is due one month after the BeginDt. Each payment is
due on the same day of the month depending on the BeginDt.

In the subform I want to show the DtDue and AmtDue (per month) the DtPd and
AmtPd. I could then calculate the amounts due and paid with a balance.

Am I on the right track.

Thank you all so much for the help.
 
By adding in fields specific to one type of transaction (i.e., dues/fees),
you're starting down a slippery slope. What happens when you come up with
another type of fee? And the one after that? Adding another column or set
of columns is what you'd do in a spreadsheet, but Access is a relational
database. If "relational" and "normalization" are unfamiliar, plan on
brushing up!

If your transaction table has a field for TransactionAmount and a field for
TransactionDate, what about the idea of adding one more field for
[TransactionType]. In that field, you'd store the foreign key that points
back to the row in your (new) [tlkpTransactionType] lookup table that lists
each type of transaction.

With a design like this, when you add a new fee (i.e., type of
transaction), you just add a row to that lookup table. That's it!

Make sense?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top