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
Objectives:
-
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!
