title: Sum of column from subquery
body: <p>The below diagram represents relationships between three tables in an Access database.  </p>
  
  <ul>
  <li>Table <code>[Shared Mailbox]</code> contains a list of shared mailboxes (SMB) in Exchange.   </li>
  <li>Table <code>[Shared Mailbox User]</code> lists the email addresses that have access to a shared mailbox.    </li>
  <li>Table <code>[Exchange Census]</code> contains the details of all mailboxes whether that of a person or a shared mailbox.  The column <code>[Exchange Mailbox].MailboxSize(MB)</code> is the size of a mailbox.</li>
  </ul>
  
  <p>For each <code>[Shared Mailbox].SharedMailbox</code>, I need to sum the volume of the user mailboxes that have access to it.</p>
  
  <p>I am working with the below query:</p>
  
  <pre><code>SELECT  [Shared Mailbox].SharedMailbox, [Shared Mailbox].MailboxSmtpAddress,
  (select Sum([MailboxSize(MB)]) from [Exchange Census] Where ([Shared Mailbox].SharedMailbox = [Shared Mailbox User].SharedMailbox) And 
  ([Shared Mailbox User].UserEmail = [Exchange Census].PrimarySmtpAddress) Group by [Shared Mailbox].SharedMailbox ) AS UserMailboxVolume
  FROM ([Shared Mailbox] 
  INNER JOIN [Exchange Census] ON [Shared Mailbox].MailboxSmtpAddress = [Exchange Census].PrimarySmtpAddress) 
  INNER JOIN [Shared Mailbox User] ON [Shared Mailbox].SharedMailbox = [Shared Mailbox User].SharedMailbox;
  </code></pre>
  
  <p>This produces a result set that looks like this, where the subquery returns the size of the child mailboxes rather than their sum:</p>
  
  <p><a href="https://i.stack.imgur.com/zcXtg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zcXtg.png" alt="enter image description here"></a></p>
  
  <p>What do I need to change to get one row per SMB with the aggregate volume of the mailboxes for those who have access?</p>
  
  <p><a href="https://i.stack.imgur.com/SXzhk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SXzhk.png" alt="enter image description here"></a></p>