I have two bases that I am trying to design a relationship between and am feeling puzzled about how to get them to do the things I need. I have been staring at the records for a few days now, so it is time to ask for help– thank you in advance!
Base 1: Volunteers– this has our database of volunteers with their info and interests and volunteer notes
Base 2: Project Management– this has a Projects table and Tasks table that are linked
Create a Volunteer Log table for tasks completed (from Tasks table in P.M. base) and amount of time spent volunteering – If possible, I would love for this log to originate in the Volunteers base rather than P.M. base.
Task report view (in Tasks table of P.M. base) that shows how much volunteer time was spent on a given task. It seems like this number should come from a rollup from Volunteer Log…
Project report views (in Projects table of P.M. base) showing how many volunteers were involved in a project (rollup count) and the number of volunteer hours (rollup sum) spent on a project. Each Task from Tasks table is assigned to a project, so it seems like this number should be a rollup from the Task volunteer hour entries (which maybe is derived from Volunteer Log?)
Have comprehensive volunteer data IN the Volunteers base so we have an overview of how many hours a person has volunteered, what kind of tasks they have volunteered for, what projects they worked on… all while NOT cluttering my P.M. base with too much volunteer data
I feel like I have almost figured this out through various linked records and table syncs between the two bases, but I keep missing one step in the relationship. For the reporting values, I know this will come from rollup fields but the order of linking/syncing to GET the correct numbers is where I’m stuck. So, to reiterate:
- We need a comprehensive overview of volunteer info and activity within our Volunteers base.
- We need to log volunteer hours and activities– hours linked to tasks in the Volunteer Log should be reflected in the Tasks table (e.g. Task X shows a total of 25 volunteer hours because there are 5 volunteers who spent 5 hours each on Task X)
- We need to be able to pull volunteer numbers for projects and task reports to show how many hours were spent on specific tasks and Projects. Optimally, I would like to maintain comprehensive project data within the Projects table of the P.M. base so we have an overview of everything needed to put a project together (i.e. I would like to see the total volunteer hours spent on a project within Projects table, rather than having to create a view in Volunteer Log).
If anyone can advise on design that would make these things possible, I would be very appreciative!