I am trying to write a query that will calculate unrealized gain/loss and group by Current Partner.
My tables are: Partners - with Fields ID and PartnerName
PortfolioCompanies - with Fields PortfolioCoName and Sector
Partners_PortfolioCo - with Fields PortfolioCompany and InitialPartner and Current Partner (this is used as a junction table as
there are multiple initial partner and current partners for one portfolio company - however my PortfolioCompany table is showing as a Parent to this junction table, but the Partner table is not showing as a Parent to the junction table.....this may be the problem)
Transactions - with Fields ID, PortfolioCoName, FundName, CostUnrealized, FMVUnrealized
My Query is: SELECT Partners_PortfolioCompanies.CurrentPartner, Sum([Transactions].[FMVUnrealized]-[Transactions].[CostUnrealized]) AS Unrealized, Sum([Transactions].[ProceedsRealized]-[Transactions].[CostRealized]) AS Realized, (Sum([Transactions].[FMVUnrealized]-[Transactions].[CostUnrealized]))+(Sum([Transactions].[ProceedsRealized]-[Transactions].[CostRealized])) AS TotalGainLoss, Partners.PartnerNameFROM (PortfolioCompanies INNER JOIN (Partners INNER JOIN Partners_PortfolioCompanies ON Partners.PartnerName = Partners_PortfolioCompanies.CurrentPartner) ON PortfolioCompanies.PortfolioCoName = Partners_PortfolioCompanies.PortfolioCompany) INNER JOIN Transactions ON PortfolioCompanies.PortfolioCoName = Transactions.PortfolioCoName
GROUP BY Partners_PortfolioCompanies.CurrentPartner, Partners.PartnerName;
I want the query to calculate the unrealized gain/loss (FMVUnrealized - Cost Unrealized) by company and then sum that value across companies that are linked to each Current Partner to end up with a table that shows:
Current partner Unrealized Gain/Loss
Partner X Value X
Partner Y Value Y
The code I have written above is working, except that it seems to be double counting the gain/loss somehow.
Please help!!! Urgent
Thank you,
Becka