monthly billing of classes

[RunningP]

I'm currenlty working on a database that is for a gymnastics school. The owner (my sister) doesn't need alot just a way to track who's paid and who owes, no needs for tuition for the year and other fancy stuff.

Here are the important tables:

Student Tbl
StudentID (PK)
First
Last
Active Y/N
Gymnastics Y/N
Competitive Y/N
Boy Fitness Y/N

Owed/Paid tbl
OwedID (PK)
StudentID
Owed Amount
Class owed for
Amount paid
Date Paid

what I woud like is one of the following:

A way to at the first of every month to add a bill (global with a button I would hope) to the student account that could be pulled up and paid upon. The pramaters for this would look something like this:
- Student is Active
---Student has not paid for the month
------Student iis in Gymnastics fee 35.00
(If student doesn't pay by 10th of month 10.00 fee applies)
There would also be a chance that the student is in multiple classes so my fear is if I use an update query it would write over the record I just made before it could be paid

OR

I'm not sure if this would work but IF we could enter payments and have a query to show those who have not paid in full since (certain date) and could look at this data and see a total that they would owe. My big question with this way would be how would access know what they owe if I've not put the data in? I would see this as a backwards approach. With my limited knowledge of Access I'm not sure how to do it but it seems like even this way could be done.

I've really racked my brain looked through my bible and visited my local Barnes & Nobel. I'm thinking I've worked too long on it and the answer is going to be EASY.

Thanks in advance,

ScottP

 

[Pat Hartman]

You need to start by normalizing your tables. You need separate tables for the static information for a student and then you need a table to hold the billing transactions that are generated every month

tblStudent
StudentID (PK)
ParentID (foreign key to tblParent)
First
Last
Status

tblClasses
ClassID (PK)
ClassName
ClassFee

tblRoster
StudentID (pk fld1, foreign key to tblStudent)
ClassID (pk fld2, foreign key to tblClass)
Status

Then every month, you generate a row for each class that an active student takes.

tblReceivable
ReceivableID (pk)
StudentID (foreign key to tblRoster)
ClassID (foreign key to tblRoster)
FeeType (regular class fee or late fee or credit, etc)
BillAmt
AmtPaid
PaidDt

With this table structure, you should be able to generate receivable transactions every month by using a query that selects appropriate records. You would then have a manual interface where you could add late charges (or you could also do these with an append query) or any adjustments.

By including a parent in the student table, you can produce consolidated bills for parents with multiple students.

 

[WolfgangP]

Dear Pat,

Hi, great advice you are giving people, I have been following your comments, throughout the board!

I have a question, I am doing something similar as runningP, but I would like to know how do I create the code behind the button, for the automatic file creation(automated billing)I guess, I need a SQL query, but I am not sure what to put into it, I know I need a list of customers to bill, and from the other table(tblClasses) the amount, but how do I add them to tblReceivable?

Thanks again, for your time.

Wolfgang
\