Total Row when e-mailing query result in excel format

[brownpride]

I have this Query....

SELECT [Deals Summary].[Deal ID], [Date], Format([Time],"hh:mm AM/PM") as [Trade Time], [Traders].[Trader], [Brokerage Clients].[Company Name], Format([Deals Summary].[Total Quantity],'###,###,###') as Quantity, Format([Period],'mmm yyyy') as Term, [Premium], [Deals Summary].[Total (Buying)] as Brokerage
FROM (([Brokerage Clients] INNER JOIN [Deals Summary] 
ON [Brokerage Clients].[Brokerage ID] = [Deals Summary].[Brokerage ID (Buyer)]) 
INNER JOIN [Traders] ON [Deals Summary].[Trader ID (Buyer)] = [Traders].[Trader ID])
WHERE [Deals Summary].[Date] = Date()


UNION ALL SELECT [Deals Summary].[Deal ID], [Date], Format([Time],"hh:mm AM/PM") as [Trade Time], [Traders].[Trader], [Brokerage Clients].[Company Name], Format([Deals Summary].[Total Quantity],'###,###,###') as Quantity, Format([Period],'mmm yyyy') as Term, [Premium], [Deals Summary].[Total (Selling)] as Brokerage
FROM (([Brokerage Clients] INNER JOIN [Deals Summary]
ON [Brokerage Clients].[Brokerage ID] = [Deals Summary].[Brokerage ID (Seller)]) 
INNER JOIN [Traders] ON [Deals Summary].[Trader ID (Seller)] = [Traders].[Trader ID])
WHERE [Deals Summary].[Date] = Date();

I currently have VBA code to send it via email which is working perfectly fine. However, I would like to sum the Brokerage column and show the result either in the excel file or the email text itself. If I manual export it via Access, the totals stay. But if I export using VBA, then the totals don't show up. Any help would be appreciated.

[Bulzie]

Maybe in email code:
BrokerageTotal = DSum("Brokerage", "YourAboveQueryName") 'or do the DSUM on the field and table that has the values.

Put it inside your email subject or body text. "Body of email with total " & BrokerageTotal & " like you want."

[aytee111]

The total can be in another query joined here with UNION - get the sort order correct and it will be the last record in the query.

[brownpride]

Bulzie, that worked perfectly! Thanks!