Results 1 to 11 of 11
  1. #1
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13

    Select Query is double counting data

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you joining several 'many' side tables in this query? Possibly you need to do separate aggregate queries then join those queries on common ID fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13
    Hmm. Yes that could be the case. I also tried by using a query. My query calculated the unrealized gain/loss by portfolio company. And then I did a query on top that sorts by Partner and aggregates, but company, but am having the same problem of double counting values. This was the other query I tried:

    SELECT Partners_PortfolioCompanies.CurrentPartner, Sum([Unreal/Real_Gain/Loss_byCo].Unrealized) AS SumOfUnrealized
    FROM (Partners INNER JOIN Partners_PortfolioCompanies ON Partners.PartnerName = Partners_PortfolioCompanies.CurrentPartner) INNER JOIN [Unreal/Real_Gain/Loss_byCo] ON Partners_PortfolioCompanies.PortfolioCompany = [Unreal/Real_Gain/Loss_byCo].PortfolioCoName
    GROUP BY Partners_PortfolioCompanies.CurrentPartner, Partners.PartnerName;

    I'm not sure how to do more than one query and then join the fields as there is no value in the Partners_PortfolioCompanies table to aggregate......

    Thank you!! Still not sure it is working....

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I would have to review data if you want to provide. Follow instructions at bottom of my post. Will look at this weekend.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13
    Here is my structure....I am not entirely familiar with access, so maybe my structure is not correct or I am not understanding the aggregate query idea...
    Attached Thumbnails Attached Thumbnails AccessStructure.PNG  

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you aren't saveing the primary key ID field from Partners as the foreign key, then don't designate it as the primary key.

    Will each company have multiple records in Partners_PortfolioCompany? If there will be only one record for each company then think one too many tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13
    Yes, each company will have multiple records in Partners_PortfolioCompany (this is supposed to be a junction table, however I may not have it set up right).

    Because for each Company there could be more than one initial partner and more than one current partner assigned to that company......

    Is this how you mean for the primary key vs. foreign key? Seems this is a little better....
    Click image for larger version. 

Name:	AccessStructure2.PNG 
Views:	5 
Size:	19.7 KB 
ID:	11883

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, but names really don't make very good unique identifiers.

    If there are multiple companies in Partners_PortfolioCompany then I expect that is the source of the duplication. The query doesn't know which partner should be retrieved so it gets all of them for each company. And since the transactions are associated with company, not partner, all the query can do is associate transaction with all the company partner records. Do you want only the most recent CurrentPartner for each company to be included in the record association?

    Why group on partner and not just the company?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13
    Hmm...

    Well for example, this is the question I am trying to answer:

    What is the total unrealized gain/loss across companies for each current partner.

    For each company there may be two CurrentPartner's but I would want the gain/loss to pull for each of those Partners (i.e. the gain/loss for a specific company with two CurrentPartners will be counted for CurrentPartnerA and also for CurrentPartnerB).

    It seems like this is working better, but not flawless yet:
    SELECT InitialPartner_PortfolioCo.InitialPartner, Sum([Unreal/Real_Gain/Loss_byCo].Unrealized) AS SumOfUnrealized1
    FROM Partners INNER JOIN (InitialPartner_PortfolioCo INNER JOIN [Unreal/Real_Gain/Loss_byCo] ON InitialPartner_PortfolioCo.PortfolioCoName = [Unreal/Real_Gain/Loss_byCo].PortfolioCoName) ON Partners.PartnerName = InitialPartner_PortfolioCo.InitialPartner
    GROUP BY InitialPartner_PortfolioCo.InitialPartner, Partners.PartnerName;

    Thank you for your help!!

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Gain/Loss is based on transactions data? Transactions are not associated with partners. Cannot determine Gain/Loss per partner. Any query that includes all the partner records will cause 'duplication' of transactions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    becka11 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    13
    Got it! It had to do with having the right primary key as you stated. Thank you so much for your help. This was my final SQL:
    SELECT InitialPartner_PortfolioCo.InitialPartner, Sum([Unreal/Real_Gain/Loss_byCo].Unrealized) AS UnrealizedGL, Sum([Unreal/Real_Gain/Loss_byCo].Realized) AS RealizedGL, Sum([Unreal/Real_Gain/Loss_byCo].TotalGainLoss) AS TotalGL
    FROM Partners INNER JOIN (InitialPartner_PortfolioCo INNER JOIN [Unreal/Real_Gain/Loss_byCo] ON InitialPartner_PortfolioCo.PortfolioCoName = [Unreal/Real_Gain/Loss_byCo].PortfolioCoName) ON Partners.PartnerName = InitialPartner_PortfolioCo.InitialPartner
    GROUP BY InitialPartner_PortfolioCo.InitialPartner, Partners.PartnerName;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-30-2012, 03:59 AM
  2. Building double data entry database
    By Hyunjee in forum Programming
    Replies: 5
    Last Post: 12-13-2011, 10:40 AM
  3. Double click combo box to add data
    By sam49 in forum Database Design
    Replies: 29
    Last Post: 03-28-2011, 08:58 AM
  4. Replies: 2
    Last Post: 01-31-2011, 08:31 AM
  5. Replies: 4
    Last Post: 11-06-2009, 09:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums