I am working on a day care database that has children running up fees, parents who get charged with fees, and payments the parents make. Each fee and payment is referred to a parent by their ID number, and each fee and payment has a date and a description. I want to put these together, grouped by parent, ordered by date from past date to present, and calculate a balance, line by line. I want to be able to refer to that balance in other forms of the database. I want to refer to the latest balance a parent has. How should I set up the tables and forms so I can do this? Should I have separate tables: one for parent fee, one for child fee, one for payment? I tried this and could not group the dates as one field in my query. Or should I have one table with all finances: one field for date, one for parent fee, one for payment, one for child fee?
