Required Tables (1 Viewer)

[Fandy]

Please I am trying to build database for fees billing and payments for university students but
struggling with the required tables to use. I want to know at the end of the day all fees
and payments of each student at different academic levels and years of study.

Can someone offer me a helping hand and direction?

Thank you.

 

[theDBguy]

Hi. Use pen and paper first. Write down all the information you want to track. Then, identify the main entities and their attributes. The entities will become tables, and their attributes will become the columns for those tables.

 

[Fandy]

Please below are my tables and their attributes:
1.Tbl.Student info
Student id
Name
Telephone No.
Dept
2.Tbl.Payment
Payment id
PaidDate
Payment amount
Payment Mode
3. Tbl.Fee
FeeID
Academic year
Course fee
SRC fee
Penalty
Total fee
My challenge is how to link the above tables so I can track all the fees of a student and their payment and balance at each academic year.
Please I need your help or can someone direct me to similar projects so I can follow suit?
Thank you.

 

[oleronesoftwares]

Hi,
You will need one common field name in Tbl.Student info,Tbl.Payment,Tbl.Fee.

since a student will have more than one bill and make more than one payment in the duration of their course

Tbl.Student info will have a one to many relationship with the other two tables
student_id in Tbl.Student info will be the primary key of the table, no duplicate

you will create a student_id field in the other two tables, but allow for duplicates value in the two tables


The student_id field is better it's the student registration number.

You will then go to relationship window and create the relationship between student_info table and the two other tables

 

[strive4peace]

hi @Fandy ,

adding on ...

I would make a field in each table with an AutoNumber field to be the primary key. I like to put ID on the ends of these fields (StudentID, PaymentID, FeeID), which means you need to call the student number something else ... if it's text, maybe StudentCode? Best not to use spaces in field names.

The Fee table needs StudentID Long Integer (Default Value is Null) to correlate it to the student

You should have a FeeTypes table with AutoNumber FeeTypeID and a text field, FeeType, to describe it. The Fee table would then also have a FeeTypeID Long Integer (Default Value is Null) with different records for each fee, instead of using different fields -- this is a common mistake. Then all the fees are in the same FeeAmount field, and you can always get a total for each student

The Payment table needs StudentID Long Integer (Default Value is Null) to correlate it to the student

This structure allows them to make multiple payments. You can use a query or form to add fees and payments up to get a balance.

This is pretty simplistic, but a good start ... by next year, you'll be better with Access

 

[arnelgp]

there is a model here:
Student Fees Collection Data Model (databaseanswers.org)

 

[Pat Hartman]

I don't like the models in databaseanswers.org. This one isn't terrible but it is more complex than you probably need. To fix your proposed schema.
1. Remove the special characters
2. Remove the embedded spaces
3. Do NOT use the names of properties or functions. "Name" and "Date" are especially problematic as column names. Do NOT use them.
3. Every table needs an autonumber ID (which you seem to have).
4. Those PK's need to be used in other tables as FK's to connect tables together.
5. tblPayment needs both StudentID to connect the payment to a student and FeeID to connect the payment to a fee.
6. The table tblFee is muddled. It has multiple fee types and includes a calculated value. Remove the calculation. And then each fee type should be a separate row in tblFee. You might want to include a DueDT field. That can be used to calculate the penalty if the fee is not paid on time.

So, look at the recommended model and use my suggestions to make your proposal more like the model. Then incorporate anything in the model that seems to make sense.

And finally, prefixes on column names are more annoying than useful - which is one of the reasons I don't like the model.

 

[Fandy]

Hi,
You will need one common field name in Tbl.Student info,Tbl.Payment,Tbl.Fee.

since a student will have more than one bill and make more than one payment in the duration of their course

Tbl.Student info will have a one to many relationship with the other two tables
student_id in Tbl.Student info will be the primary key of the table, no duplicate

you will create a student_id field in the other two tables, but allow for duplicates value in the two tables


The student_id field is better it's the student registration number.

You will then go to relationship window and create the relationship between student_info table and the two other tables

Hi Sir,
I have created the three tables as you directed and filled them with some records at attached.
I have also created a query that give the total fee of each student.
My challenge now is how to query a common query to sum up all fees owned by a student,
all payments made by a student and the prevailing balance due the student to pay.
Kindly help me.
Thank you.

 

[Pat Hartman]

The fees table is incorrect. There should be a fee type column and a fee amount column. If there are three types of fees, you end up with three rows per student. One per fee type.

 

[oleronesoftwares]

I have created the three tables as you directed and filled them with some records at attached.
I have also created a query that give the total fee of each student.
My challenge now is how to query a common query to sum up all fees owned by a student,
all payments made by a student and the prevailing balance due the student to pay.
Kindly help me.
Thank you.

As rightly said by @Pat Hartman , the student fees table should have a fee category,so all feeamounts goes to a single column

e.g

studentfee_category tuition
amount 500.24


studentfee_category src dues
amount 200


etc.

once you have done that please re-share the images, then we can proceed.

 

[Fandy]

As rightly said by @Pat Hartman , the student fees table should have a fee category,so all feeamounts goes to a single column

e.g

studentfee_category tuition
amount 500.24


studentfee_category src dues
amount 200


etc.

once you have done that please re-share the images, then we can proceed.

The total fee of a student consist tuition fee, STC Dues and penalty. Do you mean I should have a new column that will sum up all the fees components in the fees table?

 

[oleronesoftwares]

The total fee of a student consist tuition fee, STC Dues and penalty. Do you mean I should have a new column that will sum up all the fees components in the fees table?

No

Have a new text column that will be a combo box, the options to pick from will be the various fee types, e.g tuition fee,stc dues, penalty.

Then use only one number field for the fee amounts.

Check the sample i put in my previous post.

 

[Pat Hartman]

Think about it this way. What kind of changes would have to be made to the application if a new fee type were needed. Maybe, you need to charge some students for transportation. If you properly normalize the table, NO changes would be necessary. transportation would just be a row in a table that would sometimes exist and sometimes not.

 

[Fandy]

No

Have a new text column that will be a combo box, the options to pick from will be the various fee types, e.g tuition fee,stc dues, penalty.

Then use only one number field for the fee amounts.

Check the sample i put in my previous post.

Please is that how my fee table should look like as directed?
Kindly look at it and advise me please

 

[The_Doc_Man]

That looks reasonable. There are ways to spiffy it up a little bit, but the truth is that what you have should work OK.

 

[Fandy]

Tha

That looks reasonable. There are ways to spiffy it up a little bit, but the truth is that what you have should work OK.

Thanks for swift response but please what is the way forward with regards to my initiate question?

 

[oleronesoftwares]

Please is that how my fee table should look like as directed?
Kindly look at it and advise me please

Yes that is how it should look like, well done.

 

[oleronesoftwares]

Please re-share the field names in your fees table.

To your original question
"
I want to know at the end of the day all fees
and payments of each student at different academic levels and years of study"

Is it fees due, and payments made by each student or
Initial fees due, payments made and balance due?

Which of the two above?

 

[Fandy]

Please re-share the field names in your fees table.

To your original question
"
I want to know at the end of the day all fees
and payments of each student at different academic levels and years of study"

Is it fees due, and payments made by each student or
Initial fees due, payments made and balance due?

Which of the two above?

Please attached is the field list of the fee table.
1. I want to a query to show sum up of all fees of each student
2. A query to show Initial fees due, payments made and balance due

 

[oleronesoftwares]

1. I want to a query to show sum up of all fees of each student

This will sum by day by each student fees due by fee type
SELECT fees.feesdate, fees.student_id, fees.feetype, Sum(fees.feeamount) AS SumOffeeamount
FROM fees
GROUP BY fees.feesdate, fees.student_id, fees.feetype;

2. A query to show Initial fees due, payments made and balance due

For this you have to add student_id field in your payment table, then create a relationship between it and the fees table, though i still have a feeling your database is still not well structured.