Skip to main content
Question

Summary field based upon two many to many relationships

  • December 11, 2018
  • 0 replies
  • 51 views
I have four tables Projects, Kickoffs, Technicians and Site Visits.  The relationships between the tables are set up as Projects < Kickoffs > Technicians but also Projects < Site Visits > Technicians.

I need to be able to show if the Technician assigned to the Site Visit has had the Kickoff already for this specific Project.  I want to know did Sam the Technician get a Kickoff for the Saving Puppies Project (so good to go for all those visits) but she has not had the Kickoff for the Saving Kitties Project (so needs to have the kickoff for these visits). 

I am sure it is a super easy solution but I'm stumped.  I am not sure how to create a summary field that will show on the Site Visit that either the date of the Kickoff for the related Project and related Technician or at minimum that there is one Kickoff record that matches the related Project and related Technician.  I would even be happy to have a report link that shows just the one record that matches these two criteria but I can only have it match the related project or related technician.  How do I indicate that the technician has had a kickoff for that specific Project when assigned to a Site Visit?
    This topic has been closed for replies.

    So, Kickoffs and Site Visits are both join tables between Techs and Projects? I suppose there's a good reason they can't be unified in one table, say, Activities?

    How do you identify/refer to Kickoff records? It seems that what you basically need is a way to identify what Kickoff each Site Visit is related to - so a unique field identifying the Kickoff, which you can then lookup down to Project and from there back up to Site Visits.

    Though I have the sense there's something I'm missing about your structure. Conceptually I find it more comfortable to think of Projects as a parent table, with Site Visits and Kickoffs as its children, assigning Techs to the latter two from their own table. That makes it easy to use Project ID as the organizing principle and communication vector for the child tables. I know this isn't helpful, but I'm struggling with the same concepts you are.

    It's an interesting question. I'll be curious to see what the pros have to say.
    • XeniaWatterson
    • Author
    • Registered
    • December 12, 2018
    Thanks for responding.  Kickoffs aren't the same as Site Visits because a single tech can do multiple Site Visits for a project (so they could save Puppies in Chicago and Detroit and Minneapolis) but they only need one Kickoff per project.  I currently have a report link where the related projects match, but that's not being limited by the matching technician, so it will show all the techs that have the Kickoff for the project or all the projects the tech has had a kickoff for.
    Terms & ConditionsAccessibility statement