Filtering the results from a sub-summary report, and using charts with sub subsummary reports
I'm creating a database to manage a stock portfolio across multiple brokerage accounts. What I have now is a Transactions table that has the imported data from the different brokerage accounts.
I need the database to be able to calculate the total shares across all accounts as well as which shares are held in individual accounts.
I also need to be able to see a pie chart that will show the distribution of shares in each account as well as the total distribution across all accounts.
I have started on a database to accomplish this, and I'm getting some of the results that I want, but I'm having a hard time getting everything that I want out of it. So here is what I have so far that seems to be working. I can do a Subsummary report based on Account number and then Stock Symbol that will show me the total number of shares of each stock in each account.
What I would like is in the top part of the subsummary where it is sorted by account is to have a pie chart that show the distribution of the stock shares. I would also like the report to exclude stock shares of 0 or less, so that the report only shows shares that are currently held and not shares that have been held in the past.
Following are the tables that I currently have. If any one has any ideas on how I can get the results that I'm looking for I would appreciate it.
|fk_accountID||Connects to pk accountID in Account Table|
|fk_stockID||Connects to pk stock ID in stocktable|
Connects to pk type ID for type of trade, because some trades are option trade
which do not effect number of shares.
|QTY||Number of shares bought or sold|
=Total of cStockTypeQTYHelper (running with Restart) when sorted by Stocks::StockSymbol
|pk account ID||connected to transactions table fk_accountID|
|pk_stockID||Connected to Transactions table fk_stockID|
|Type Table||for different types of transactions stock, option, dividend|
|pk_typeID||connected to fk_typeID in Transaction table|