title: Calculate daily goals based on file type worked
body: <p>I have a table in Access 2016 that contains completed files by employees by completed date. I need to create a table that establishes daily goals for employees by completed date. </p>
  
  <p>Daily goals are defined by the type of file completed – if an employee completes ONLY Initial files, their daily goal is 7; otherwise, their daily goal is 9. </p>
  
  <p>An example is below for illustration. </p>
  
  <ul>
  <li>Mary 6/1/2020 Initial</li>
  <li>Mary 6/1/2020 Initial</li>
  <li>Mary 6/1/2020 Initial</li>
  <li>Cathy 6/2/2020 Renewal</li>
  <li>Cathy 6/2/2020 Initial</li>
  <li>Jessica 6/1/2020 Renewal</li>
  <li>Jessica 6/1/2020 Dedelegated </li>
  <li>Jessica 6/1/2020 Renewal</li>
  </ul>
  
  <p>The table output that I would like to create for employee daily goals.</p>
  
  <ul>
  <li>Employee: Mary; Completed Date: 6/1/2020; Completed Count: 3; Daily Goal: 7 (b/c only worked Initial 
  files)</li>
  <li>Employee: Cathy; Completed Date: 6/2/2020; Completed Count: 2; Daily Goal: 9 (b/c worked on mixed file 
  types)</li>
  <li>Employee: Jessica; Completed Date: 6/1/2020; Completed Count: 3; Daily Goal: 9 (b/c worked on mixed 
  file types)</li>
  </ul>
  
  <p>Any advice on the best way to calculate the daily goals? I have tried using a calculated field with an IIF statement, but it does not work correctly as it looks at the data at an individual record level, not at an aggregate level for each Completed Date.</p>