### Title

Using a summary count in one table to perform calculations in another

### Post

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.

Thanks!

I'm guessing you have one record per workshop, with a repeating field (not usually Best Practice in this scenario...) with various units of supplies costs. You can create a calculation field that adds all of those unit costs.

Presumably you have a field in your Attendance Table that links to this Workshop record, so you could include a Count calculation, using the relationship from the Workshop Table to the Attendance Table, to count an always-filled field, such as the AttendanceID, and multiply the total of the costs by that.

So:

Unit Costs (your repeating field)

Wood $1.00

Nails $2.00

Glue $4.00

Total $7.00 <- calculation in the Workshop Table

Workshop Total Costs = Total Unit Costs x Count (Workshop_Attendance::WorkshopID )