I am maintaining my tiny portfolio of stocks and trying to expand it further to keep the track of dividend I received. I have created a query that tracks my trades and various columns of that query are as follows -

qryTrade
TradeID
AccountID
StockID
TradeDate
Quantity
Price
TradeCommission
ExtendedPrice

The quantity is positive for buying and negative for selling a security. Further, I have a table that maintains the dividend history of various stocks I hold.

tblDividend
DividendID
StockID
ExDividendDate
PayoutDate
DividendPerShare

I am trying to calculate number of shares I held for a given StockID and AccountID on the day prior to ExDividendDate indicated on tblDividend, which is essentially a sum of the Quantity column. If I sell a security on the ExDividendDate, I am still eligible for the full dividend. This summation is then multiplied with DividendPerShare column in the table tblDividend to arrive at total dividend received for the corresponding ExDividendDate.

Is there a way to calculate this? I understand that DSUM function is the right candidate here but not sure how to write this query.

Thanks, Nimish