Trying to make payments system for dance school

[Tommo]

Hi



I am trying to make a system of payments for a dance school database.

I have student and class table. These are linked in a junction table enrolments.

Having a payments field in the junction field means I can set up datasheets for students by class and how much money each class has taken.

I want to make a payments table that will record each payment. What am i doing wrong here please.

I have attached a screenshot. Let me know if i need to upload the DB.

Thanks.

[Tommo]

Just to be clear i need to do some renaming. The amountpaid in tblPayments refers to each individual payment.

The amountpaid in tblEnrollments refers to the total amount paid to date on each class by each student.

[June7]

In a conventional accounting system, customer charges and payments need not be associated with each other. Charge and payment transactions are associated with customer account - the difference of their sums is the customer account balance. Payments do not care what was bought - it is just a remittance on the account.

However, if you want to associate payment with specific enrollment, and partial or excess payments will be accepted, need an EnrollmentID PK in tblEnrollments. Then save that PK as FK in tblPayments (not StudentID and ClassID) and AmountPaid and DepositPaid will not be in tblEnrollments. Total AmountPaid should be calculated, not saved into table.

If each enrollment will have only one associated payment then record that payment in tblEnrollments and tblPayments is not needed - but since you seem to allow partial payments (as indicated by DepositPaid field), this approach does not seem appropriate.

[Tommo]

Ok great.

That is very helpful. I did think this would be the route a while ago HOWEVER,

I am using Enrollments as my junction table for classes and students.

Can i have a new field enrollments as a primary key somehow or do i need a seperate table??

Thanks

[June7]

I already described creating a PK field in tblEnrollments. Read my previous post again.

[Tommo]

Will the junction table tblEnrollments still work for the many-to-many relationship without the classID and studentID being PK.

[June7]

Certainly. They aren't currently saved as FK anywhere. You can still set them as a compound index to prevent duplicate pairs.

[Tommo]

Ok Thanks for this.

I have done all this and it is much better.

I am a bit confused where i put the AmountPaid and DepositPaid Fields now though. It seems to me these still make sense in the Enrollments Table.

Sorry

[June7]

A deposit is still a payment toward the enrollment and should be in tblPayments.

A total amount paid should be calculated when needed, not saved into table. So the AmountPaid field in tblPayments is for individual payments. Calculate a total of payments when needed.

[Tommo]

Thanks for your help

[Tommo]

I am still struggling a bit.

I want to be able to display the deposit whether or not it has been paid.

ie i want a datasheet that displays the enrolment, and the deposit box so that i can put conditional formatting on it. (Red, Amber, Green Type thing)

Now the payments have their own ID and i have no payments I am struggling to work out how to display it.

[June7]

If a deposit isn't paid then it isn't a deposit. So if wasn't received, you want to show empty box? Could have a field in tblPayments to indicate category of payment (Deposit, OnBalance, etc). Then the form can have code that does a search on tblPayments to see if there is a deposit for the enrollment. An expression in textbox or query field using DLookup() function could do that.

[Tommo]

I want to show amount paid £0.00 and the deposit either ticked or not.

I am trying now to build a function in a query to collect all payments with the EnrollmentID_FK=toEnrollmentID as i think this would sort the TotalPaid field. A bit out of my depth though.


The deposit paid is more of an indicator. I know it's odd but i want it tickable even if no payment is going in as the school has kids that they aren't going to expect a deposit from.
This will let me indicate the RED people who havent paid and the people we arent really worried about.

[June7]

Okay, can have a yes/no field to indicate if this enrollment requires a deposit. Then an expression in another textbox can show the deposit amount if there is one in tblPayments.

Have you even considered handling refunds or other types of credits? How much of an accounting system do you need to build?

[Tommo]

Nah that will be unnecessary. No refunds or credit. They sign up and pay before the term start.

I just need to show how much the class costs (ClassID - Term Cost)
How much they have paid (sum of payments by enrollmentID_FK)
and how much they owe.

I think i am tired and struggling to work out how to get enrollments and payment data in to one datasheet or query so i can do all the formulas.