Your form will need the textbox controls to determine the quantities (as you've shown) plus you need calculated controls to determine the allocations. You should disable or lock the commodity total controls so the values cannot be edited (you should use a separate form for handling the receipts and disbursements to arrive at the quantities). If I get the drift, the calculated controls would divide the sum of carrots by the grand total of members (70/6=11.66) to allocate 11 to each member. I don't know what you want to do with the other 4 carrots remaining. If you're printing this and already have the query designed for the form, I'd consider using it to designing a report and printing that instead. Forms can be printed, but they are very much lacking in the quality of the output and power that can be harnessed by a report. I "see" a report with the commodity totals in the header (no need to print the count of a commodity for each team) with details grouped by team. The calculated controls in the team groupings refer to the commodity totals in the header and simply report the commodity count distribution per team.
If you go the report route, you can turn this form into one that can handle the receipts/disbursements, or just start over. Lookup fields are for editing inside the tables themselves, which you really should avoid. Use forms with controls instead.
Hope that helps!
Last edited by Micron; 07-24-2016 at 04:21 PM.
Reason: clarification
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.