Item counts based on multiple attendee projects at multiple events

Hi there! I’m trying to determine the count and order date of specific items needed for students at many classroom sites, each site completing multiple projects over a season of classes.

This calculation would be based on these inputs:

  • units per each of a standard list of materials needed, per individual project, e.g. wheels for three different projects that use anywhere from two to six wheels. (Projects Table)
  • List of sites, with number of enrolled students (Sites Table)
  • Program Start and End Dates (Sites Table)
  • Current inventory of materials, e.g. wheels in stock (Inventory Table)
  • Lead time needed for delivery of materials from order date (Inventory Table)

And result in three outputs (destinations):

  • total units needed per item, subtracting items in stock (Sites Table)
  • Total units needed, per item (Inventory table)
  • Dates Needed (Sites Table)

This may be bigger than asking the community, happy to work with a pro, lemme know!

…and then turn this into a report, which isn’t critical, but would be nice.