Fee Table Design (1 Viewer)

[ZEEq]

Hello Everyone!
My question is regarding Table Design , Please guide me what's the best way to Design Fee Table
1. School Fee have different Categories ( Transport, Hostel, Tuition, Fine, etc)
2. New Students have to pay Enrolment Fee as well
3.Tution and Transport are monthly charged others once a year
4.School has 5 classes from (1 to 5)
5.Each class has different Fee Structure
So in short 5 classes have different Fee Structures , In each class new students have to pay additional Enrolment Fee, also some students does not require Transportation and hostel

 

[Ranman256]

I have a tFee table:
FeeName, Amt, Interval
Rent , 400, M (monthly)

this then supplies the tRecurringFee table for client charges:
clientID, FeeName
1234, Rent

every time period,(monthly,yearly, or 1 time only) a macro is run to add the fees
to the tClientFeesCharged table.

 

[ZEEq]

thanks @Ranman256 for your reply so Should i put ClassID in tFee? and second i want to attach Fee's to Student with Junctiontbl and what fields do i need in junctiontbl?

 

[mike60smart]

Hello Everyone!
My question is regarding Table Design , Please guide me what's the best way to Design Fee Table
1. School Fee have different Categories ( Transport, Hostel, Tuition, Fine, etc)
2. New Students have to pay Enrolment Fee as well
3.Tution and Transport are monthly charged others once a year
4.School has 5 classes from (1 to 5)
5.Each class has different Fee Structure
So in short 5 classes have different Fee Structures , In each class new students have to pay additional Enrolment Fee, also some students does not require Transportation and hostel

Hi
Have you started constructing your tables?
Can you upload a screenshot of the Relationships you currently have between each table?

 

[ZEEq]

Hi
Have you started constructing your tables?
Can you upload a screenshot of the Relationships you currently have between each table?

Yes Studenttbl Done

StudentFeetbl (
StudentFee_ID PK
Student_ID FK
FeeDetail_ID FK
Discount
Fine )

FeeDetailtbl
FeeDetail_ID PK
Grade_ID FK
Session_ID FK
FeeType (Tution, Transportation, etc)

 

[mike60smart]

Hi

You said
"4.School has 5 classes from (1 to 5)
5.Each class has different Fee Structure."

This would mean that the Fees apply to a Specific Class.

Many Students have Many Classes and each Class has Many Fees

 

[ZEEq]

Hi

You said
"4.School has 5 classes from (1 to 5)
5.Each class has different Fee Structure."

This would mean that the Fees apply to a Specific Class.

Many Students have Many Classes and each Class has Many Fees

@mike60smart if student gets admission in class 1 next year he will be promoted to class 2

 

[mike60smart]

So at the end of each year Students move up 1 Class

 

[ZEEq]

So at the end of each year Students move up 1 Class

correct

 

[mike60smart]

Can you upload your current database?

 

[Pat Hartman]

You have fees that apply to enrollment and transportation. They recur at different periods so you need a code that indicates that. Some are once per year and some are monthly. You also have fees that apply to specific classes so you need classID. You can add a flag that says "required" which means that all students pay these fees. That helps when setting up a new student.

You have a class table

You have students.

You need StudentFees to relate fees to students. This is the table that controls billing regardless of the period.

Some fees can be assigned to a student automatically such as tuition and new enrollment. Other fees are optional and so you need to assign them manually when you create the student billing profile. Not all students need transportation. Not all students need lodging. Some might not need books because they had an older sibling who took the same courses.

Then you need a Billed table. Each time you run your billing process, it will read through the StudentFees table and determine which fees need to be billed and add a record to the Billed table. The Billed table has a field with payment amount and date. It can get more complicated if you allow multiple payment options.

Take a stab at the tables and we'll help.

 

[ZEEq]

thanks @Pat Hartman for detailed reply ,

This is my FeeT

Fee_ID 1
SessionID 2022
StudentType Promoted
ClassID Class-2
FeeFrequency Monthly
FeeType Tuition Fee
FeeAmount 250
FeeFlag Required

Please guide me how to improve this design

 

[Pat Hartman]

That looks good. I'm going to add a link to a many-many relationship to show you how to create StudentFees. The Example isn't Students-fees; it is Employees-Classes so you'll need to think a little about how your tables relate. Employees = Students and Classes = Fees. When you make your Students form, it will have two subforms. One for Classes and a second for fees Notice the Employees example. It has a subform for classes where you pick the Class from a combo. You will pick the fees from a combo.

Also look at the tblEmpClass. Open the Indexes dialog. You will see that the autonumber is the PK but there is also a unique index that prevents you from adding the same class to the employee twice. You need a similar unique index on your StudentFees table to prevent a fee from being added multiple times. If most of the fees are required, you can automatically add them by using an append query when you add a new student.

Fines would be added directly to the Billing table and included in the next bill.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[ZEEq]

That looks good. I'm going to add a link to a many-many relationship to show you how to create StudentFees. The Example isn't Students-fees; it is Employees-Classes so you'll need to think a little about how your tables relate. Employees = Students and Classes = Fees. When you make your Students form, it will have two subforms. One for Classes and a second for fees Notice the Employees example. It has a subform for classes where you pick the Class from a combo. You will pick the fees from a combo.

Also look at the tblEmpClass. Open the Indexes dialog. You will see that the autonumber is the PK but there is also a unique index that prevents you from adding the same class to the employee twice. You need a similar unique index on your StudentFees table to prevent a fee from being added multiple times. If most of the fees are required, you can automatically add them by using an append query when you add a new student.

Fines would be added directly to the Billing table and included in the next bill.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

@Pat Hartman little confusion you said (When you make your Students form, it will have two subforms. One for Classes and a second for fees Notice the Employees example. It has a subform for classes where you pick the Class from a combo. You will pick the fees from a combo.)
main form is based on students second subform will be JtbStudentFees right? whats second subform based on?

 

[Pat Hartman]

The example I posted has only one relationship. You are talking about two. You have classes for a student (which is similar to what my sample shows) but you also have fees for a student. "Fees" is the second subform. It works the same way as the first. Just with different tables.

For the classes subform, you pick the class from a combo to make the relationship. For the fees subform, you would pick the fee from a como to make the relationship.