Query To Calculate Balance Based On Different Records In Another Table.

[SashaSandy]

I have a database with two tables.




1.tblFee_Structure.
2.tblStudent_fees.


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


tblFee_Structure 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_fees) FROM "School_Fees" in (tblFee_Structure) 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_Structure", 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??

[offie]

I might be making this more simple because I don't know how your data works.

I'm assuming that every student_ID only has 1 Structure_ID

So this is a simple relationship.

Make a query and add both tables. Click and drag from one Structure_ID to the one on the other table. This makes a relationhip(looks like a line). Double click on any fields that you want to show up (ex: Student_name)

In the "Field" row, in the a new colums enter

Balance: [tblFee_Structure].[School_Fees]-[tblstudent_fees].[fee_paid]

I think this is what you want, try it out and let me know if it works.

[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_Marks 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.
thanks.