AnsweredAssumed Answered

Update value in field in one table based on Summary total from different table

Question asked by john.s on Feb 18, 2014
Latest reply on Feb 28, 2014 by john.s


Update value in field in one table based on Summary total from different table


     Hi Folks,

      Once again I think I am in over my head and could use some help.  I have several schools for which I photograph their Sporting events, Pep Rallies, dances etc.  I need to get a handle on which sports have been covered for each school.  Each school has different sports that they participate in so keeping track is a nightmare.  Compounded by the fact that I need to provide a certain number of images for their yearbook for each sport and sometimes it takes going to more than one game to get that many images.  I need to be able to sum the number of images for each sport by school and if the required number of images is reached I want to set a status field as complete.  This doesn't sound too difficult but with the table relationships I have I can't figure it out.  Any help would be greatly appreciated.  Here is what I have for relationships and data.

                         ---< JOIN_SCHOOLS_JOBS >--- JOBS >---Sports
                         ---< JOIN_SCHOOLS_SPORTS >--- SPORTS
     Top relationship is:
     SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_JOBS::_fk_SchoolCode
        JOBS::_pk-JobID to JOIN_SCHOOLS_JOBS::_fk_JobID
         SPORTS::_pk_SportID to JOBS::_fk_SportID
     Bottom relationship is:
     SCHOOLS::_pk_SchoolCode to JOIN_SCHOOLS_SPORTS::_fk_SchoolCode
     SPORTS::_pk_SportID to JOIN_SCHOOLS_SPORTS::_fk_JobID
     SCHOOLS table has School Name, address, etc
     JOBS table is created via a layout and has a portal to JOIN_SCHOOLS_JOBS  one record is created in the jobs table and one record for each school photographed for that job is created in the JOIN table  so Job1, Job2 and Job3 would be records in the Job table and the JOIN table might have 5 records for those three jobs
         job1   school1   Sport1
         job2   school2   Sport2
         job3   school1   Sport1
     JOIN_SCHOOLS_JOBS  Table has the following fields: (ImageCount is blank when created)   _fk_JobID   _fk_SchoolCode     ImageCount
       Data might look like this
         job1   school1
         job1   school2
         job2   school2
         job3   school1
         job3   school3
     SPORTS Table has a list of Sports and the season in which they are played
        Season  (1=winter, 2=fall, 3=spring)
     JOIN_SCHOOLS_SPORTS table has records for each school and sport that needs to be photographer  it will be set up at the beginning of each season. It has three fields, _fk_SchoolCode,  _fk_SportID, and Status
       School1   Sport1
       School1   Sport2
       School1   Sport5
       School1   Sport10
       School2   Sport1
       School2   Sport2
       School2   Sport3
       School2   Sport14
       School2   Sport15
     Not all schools participate in all sports
     After photographing a game the photographer edits the images and puts them in folders by school,  the images are provided to the school and the number of images is entered into the ImageCount field on the JOIN_SCHOOLS_JOBS table.
     What I need to do is be able to sum the number of images by school and sport (more than one job might have been necessary to get enough images for that sport) and now the real tricky part...if the image count is equal to or greater than lets say 150 I need to set the status in the JOIN_SCHOOS_SPORTS table to complete for that particular school and sport.
     I am way over my head on this one.  I have created a layout to monitor the status of work to be done. It has a self join portal for schools with a go to related record and two other portals on the layout.  One is a list of jobs for that school and I was able to pull the images for that school by job.  I can’t sort by Sport because that field in not in the table which the portal is based on it is in a related table.  The third section is a portal to the Sports that need to be covered for each school with a checkbox to show if it is complete or not.