I’m trying to organize a summer arts camp. I have 4 tables: Guardians(parents), Campers(their kids), Weeks(the weeks we are operating), and Invoices.
I’d like to set it up so that:
The Weeks table contains the week and the cost of that week (different weeks can cost different amounts). I have done this already.
In the Campers table, I can assign the Weeks that each Camper has signed up to the camper (we are able to assign more than 1 week to a camper if they sign up for more than one week). I have done this already.
In the Guardians table, we can assign Campers (more than one if they have several kids attending) to each Guardian. I have done this already.
Here is where I am having trouble: In the Invoices table, I’d like to assign a Guardian to each Invoice. I’d like to be able to look up the Campers assigned to each Guardian and the Weeks assigned to each Camper assigned to each Guardian so that I can get the total cost that each Guardian needs to pay for all of their kids.
How can I do this?