Report using Main table and connected child tables
I need help on how to make an student inspection report based on the following:
Main table is called STUDENTS
Each student has a related room tables : KITCHEN, LIVINGROOM, BATH, etc.
Each room has inspection fields with potential charges such as the KITCHEN.
For example in the KITCHEN there are fields like: Clean_Oven, Replace_Microwave, General Cleaning, etc. Each field has a charge amount like $7.00, $15.00, etc.
From this database I need to create a report that will print the students name from the record and then go to each related room table and if any room has charges, each charge needs to be a line item on the report and then totaled. I assume I need some kind of script or something to go thru each student then each students related tables.....
So the report would look like if the students deposit was greater than the charges.
Jane Doe Room 302C Security Deposit $250.00
Clean Oven $7.00
Repair Tile $85.00
Total Charges $92.00 Refunded Amt($158.00)