Not sure where to go next

[jimd1768]

I have started a DB for a dance school. I have 4 tables and I have created the relationships I thought I needed. I am not getting what I want and hope that someone can guide me on the next steps.

1. I want to have a form that allows me to enter customer details
2. From these details, I want to be able to track payments. For example if they have a balance of 100 and they are being billed 20 each month, that this will total.
3. Also, I want to be able to show payments, and have this deducted from the amount due.

I only really want a form for registering customer details and a for to track money owed and payments.

Any help would be greatly appreciated.

Jim

 

[jdraw]

Could a Customer have 2 or more kids in a class?
Do you have a description of your "business" in simple, plain English?

Usually there would be a Customer who enrolls/contracts for a Service for some period of time. The Service has associated fees. You Invoice the Customer according to the Contract. The Customer pays the Invoice and you record Invoices and Payments. --generally---

 

[isladogs]

I remember answering your previous thread https://www.access-programmers.co.uk/forums/showthread.php?t=294870 where you were asking how many tables you needed.

I suggested 2 - Customers & Payments
Ranman suggested 3 - splitting the money part into Charges & Billing

Either of those would work though my preference is to keep it simple

What you DEFINITELY don't need is the 4th table you've added AmountsOwedT.
This is just calculated values that you can get from your query BalanceQ.
It seems to me that its only purpose is a junction table between Billing & Charges.

If you combine them, you are back to 2 tables - much easier.

As stated in previous thread, your payments table would have fields & datatypes similar to this:

PaymentID (PK - autonumber)
CustomerID (FK - same as PK field in Customers table)
PaymentDate (DateTime)
PaymentAmount (Currency)
PaymentType (Text)
StaffInitials (Text) - optional
Notes (long text / memo) - optional

Just remember to define one type of payment value as positive & the other as negative

This will then be perfect for the 2 forms you want.

One other thing which relates to jdraw's comment:
I would change your Customers table so it is based on the CHILD taking the Dance class(es). Same idea but the opposite focus.
You also need the child's last name, gender & DOB.
Suggest also another field which allows you to enter medical details where relevant.

I would also store other contact details for the parent - phone number / mobile number / email. You will need them if a problem occurs e.g. a child gets hurt

By this point you may decide to split the people part into one table for the students and another for the parent/carer

Hope that helps

 

[jimd1768]

I remember answering your previous thread https://www.access-programmers.co.uk/forums/showthread.php?t=294870 where you were asking how many tables you needed.

I suggested 2 - Customers & Payments
Ranman suggested 3 - splitting the money part into Charges & Billing

Either of those would work though my preference is to keep it simple

What you DEFINITELY don't need is the 4th table you've added AmountsOwedT.
This is just calculated values that you can get from your query BalanceQ.
It seems to me that its only purpose is a junction table between Billing & Charges.

If you combine them, you are back to 2 tables - much easier.

As stated in previous thread, your payments table would have fields & datatypes similar to this:

PaymentID (PK - autonumber)
CustomerID (FK - same as PK field in Customers table)
PaymentDate (DateTime)
PaymentAmount (Currency)
PaymentType (Text)
StaffInitials (Text) - optional
Notes (long text / memo) - optional

Just remember to define one type of payment value as positive & the other as negative

This will then be perfect for the 2 forms you want.

One other thing which relates to jdraw's comment:
I would change your Customers table so it is based on the CHILD taking the Dance class(es). Same idea but the opposite focus.
You also need the child's last name, gender & DOB.
Suggest also another field which allows you to enter medical details where relevant.

I would also store other contact details for the parent - phone number / mobile number / email. You will need them if a problem occurs e.g. a child gets hurt

By this point you may decide to split the people part into one table for the students and another for the parent/carer

Hope that helps

Thank you Ridders.

I have spent so long on this, I can't seem to get the basics right.
This example is a shortened version of customer details. I have all the details of parents and children. That table is fine.

My issues really come when I try to do the payments. If I give you an example:

a customer is carrying a balance of £1,035. Their billing will start again on 01 September. They will be billed £25 per month. What I am trying to do is input all outstanding balances and then have running totals (-payments) for each month.

I couldn't seem to do this on one table. That was why I spread it out. But I agree, the simpler the better.

I wasn't sure what you meant by : Just remember to define one type of payment value as positive & the other as negative

If you could explain this for me, I would appreciate it.

Thank you so much for responding

 

[isladogs]

I wasn't sure what you meant by : Just remember to define one type of payment value as positive & the other as negative

This is so you can easily get the account balance:
So e.g.
- payments made marked as NEGATIVE (as they reduce the amount owed)
- payments due marked as POSITIVE (as they increase the amount owed)

If total payments made are £25.00 (-25) and total amount due = £45.00, amount owed = £45-£25=£20

That will allow you to calculate running totals for each month using queries

As for the Customers table, whilst it may be adequate for now, I'd strongly recommend you change it (as per my last post) or you may regret it later
Definitely get full student & contact details - ICE (in case of emergency).

The reason for putting the focus on the student is because one family may have several children at the dance school & you need a separate record for each.
You need student last name as it won't always match that of the parent

 

[jimd1768]

This is so you can easily get the account balance:
So e.g.
- payments made marked as NEGATIVE (as they reduce the amount owed)
- payments due marked as POSITIVE (as they increase the amount owed)

If total payments made are £25.00 (-25) and total amount due = £45.00, amount owed = £45-£25=£20

That will allow you to calculate running totals for each month using queries

As for the Customers table, whilst it may be adequate for now, I'd strongly recommend you change it (as per my last post) or you may regret it later
Definitely get full student & contact details - ICE (in case of emergency).

The reason for putting the focus on the student is because one family may have several children at the dance school & you need a separate record for each.
You need student last name as it won't always match that of the parent

I have got a few steps further. Thank you. I may need to post for more help. Thank you for your help