Using a summary count in one table to perform calculations in another
I have an ATTENDANCE (ATT) table where I'm tracking registrations (head counts) for various workshops and a WORKSHOP (WS) table where I'm estimating expenses for overhead and student supplies for each workshop. I'm breaking down expenses for student supplies per student so this total will change with each new registration.
In the WS table, I have one repeating field for student supplies (static cost per student) and another for other expenses. In the WS table, I want to take the total of the student supplies and multiply it by the head count that's in the ATT table. I also want to be able to add this value to the other expenses as well as displaying it separately. I've spent hours trying a number of things, including get summary, but I can't seem to work it out.
I'm sure I can accomplish this with portals, but I don't want to add anymore tables or records for a non-critical feature. I hope this is possible, but if it isn't, I hope someone can let me know, in which case I will pass on the expense tracking. Or maybe I'll try a script to calculate expenses. I just thought of that now, but I'd still rather the totals update dynamically.