Update value in field in one table based on Summary total from different table
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
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
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.