Query to calculate Balance based on Different Records in another table

[sashasandy]

I have a database with two tables.

1.tblFee_Struct ure.
2.tblStudent_fe es.

tblstudent_fees contains the fields: student_ID,Stud ent_name,Struct ure_ID and fee_paid.
In this case the fields we are concerned about are "Fee_Paid & Structure_ID"

tblFee_Structur e contains two fields: Structure_ID and School_Fees.
-this table has more than 2 records i.e, more than 1 fee structure.
-School_fees Balance is calculated By subtracting "fee_paid" in (tblstudent_fee s) FROM "School_Fee s" in (tblFee_Structu re) i.e (School_Fees)-(fee_paid)

QUESTION:
i would like to create a query that calculates a students fees balance based on the fees structure chosen in tblstudent_fees .
-in a simple query it would be easy if there was only one record in "tblFee_Structu re", but how can it be done with more than one record in that table and even more calculate Balance based on the fees structure of a student??

[Rabbit]

I don't know what you mean by "how can it be done with more than one record in that table". You would have to show us some sample data and results.

If you mean that the fee structure table has multiple rows with the same structure ids. Then you have designed your tables wrong, because you have no way of tying the correct fee structure to a student.

Whether or not this is the case, your structure is already unnormalized because you should not have student name in your student fee table. Please read this article on normalization so you can design the tables correctly: http://bytes.com/topic/access/insigh...ble-structures

[sashasandy]

wow! am surprised its very simple, i was thinking deeper than i should...its actually is a simple relationship that works the trick, i created a relationship from tblStudents_Mar ks to tblfees and joined the fields FeeStructure_ID which is in both tables.Then the query automatically picks the correct fees in reference to students fee structure(which are more than 5 fees structure records, like STR1,STR2,STR3. .etc) and calculates the balance.
the table is designe is good. tblfee_structur e has only the school fees, and structure ID.

[Rabbit]

I'm glad you got it working. But this is an incorrect statement: "the table is designe is good"

The data is still unnormalized so the design is not "good". You should read the article I linked earlier so that you can work towards good design.