Fee Table Design

[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?

 

[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

 

[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?