Sum of column from subquery

The below diagram represents relationships between three tables in an Access database.

• Table [Shared Mailbox] contains a list of shared mailboxes (SMB) in Exchange.
• Table [Shared Mailbox User] lists the email addresses that have access to a shared mailbox.
• Table [Exchange Census] contains the details of all mailboxes whether that of a person or a shared mailbox. The column [Exchange Mailbox].MailboxSize(MB) is the size of a mailbox.

For each [Shared Mailbox].SharedMailbox, I need to sum the volume of the user mailboxes that have access to it.

I am working with the below query:

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;

This produces a result set that looks like this, where the subquery returns the size of the child mailboxes rather than their sum:

What do I need to change to get one row per SMB with the aggregate volume of the mailboxes for those who have access?

Answer

Unless I’ve misunderstood your data structures or requirements, I believe you should join the Exchange Census table to your Shared Mailbox User table (since it is the user’s mailbox whose size you are looking to calculate), and omit the correlated subquery entirely.

I would suggest something along the lines of the following:

select
    sm.sharedmailbox,
    sm.mailboxsmtpaddress,
    sum(ec.[MailboxSize(MB)]) as usermailboxvolume
from
    (
        [shared mailbox] sm inner join [shared mailbox user] su on
        sm.sharedmailbox = su.sharedmailbox
    ) 
    inner join [Exchange Census] ec on su.useremail = ec.primarysmtpaddress
group by
    sm.sharedmailbox,
    sm.mailboxsmtpaddress