Aggregating fields in Select Query

[becka11]

Hi,



Here is my structure:


I am trying to pull the data in the BackUp_byCo table by Partner. I need this data by Partner name whether the Partner is listed as the initial partner or the current partner in the InitialPartner_PortfolioCo table. The result I need is Partner Name and all of the companies they are associated with (whether they are the initial partner or current partner associated with that PortfolioCoName. Please HELP!! Below are a few SQL's I have tried and have failed.

SELECT Partners.PartnerName, Backup_byCo.PortfolioCoName, Backup_byCo.FMVUnrealized, Backup_byCo.CostUnrealized, Backup_byCo.UnrealizedGL
FROM Partners INNER JOIN (InitialPartner_PortfolioCo INNER JOIN Backup_byCo ON InitialPartner_PortfolioCo.PortfolioCoName = Backup_byCo.PortfolioCoName) ON (Partners.PartnerName = InitialPartner_PortfolioCo.CurrentPartner) AND (Partners.PartnerName = InitialPartner_PortfolioCo.InitialPartner)
WHERE (((Partners.PartnerName)=[InitialPartner_PortfolioCo.CurrentPartner] Or (Partners.PartnerName)=[InitialPartner_PortfolioCo.InitialPartner]))
ORDER BY Partners.PartnerName;

And I tried:
SELECT Partners.PartnerName, Backup_byCo.PortfolioCoName, Backup_byCo.FMVUnrealized, Backup_byCo.CostUnrealized, Backup_byCo.UnrealizedGL
FROM Partners INNER JOIN (InitialPartner_PortfolioCo INNER JOIN Backup_byCo ON InitialPartner_PortfolioCo.PortfolioCoName = Backup_byCo.PortfolioCoName) ON (Partners.PartnerName = InitialPartner_PortfolioCo.CurrentPartner) AND (Partners.PartnerName = InitialPartner_PortfolioCo.InitialPartner)
WHERE [InitialPartner_PortfolioCo].[InitialPartner] <> [InitialPartner_PortfolioCo].[CurrentPartner] OR [InitialPartner_PortfolioCo].[InitialPartner] = [InitialPartner_PortfolioCo].[CurrentPartner]
ORDER BY Partners.PartnerName;

Any help is greatly appreciated as this is a timely matter! Thank you so much!

[June7]

Try:

1. UNION query to get the two partner fields combined as a single field, there is no wizard for UNION, must type (or copy/paste) in SQL view of query designer:

SELECT ID, PortfolioCoName, InitialPartner AS Partner, "Initial" As Type FROM InitialPartner_PortfolioCo
UNION SELECT ID, PortfolioCoName, CurrentPartner, "Current" FROM InitialPartner_PortfolioCo;

2. Use that query in another query that joins with Partners and Backup_byCo

[becka11]

Thank you June7! This worked. Only issue is that in the case where the same Partner is listed as both the Initial Partner and the Current Partner for a single company, my new query is double counting that. Can I somehow write in a statement that if for a given company (PortfolioCoName), the same Partner is listed as both Initial and Current (Type), then only pick one? Not sure how I would do something like this. Any help is greatly appreciated!!

This is my new query:
SELECT Partners.PartnerName, TotalPartners.PortfolioCoName, Backup_byCo.FMVUnrealized, Backup_byCo.CostUnrealized, Backup_byCo.UnrealizedGL
FROM (Partners INNER JOIN TotalPartners ON Partners.PartnerName = TotalPartners.Partner) INNER JOIN Backup_byCo ON TotalPartners.PortfolioCoName = Backup_byCo.PortfolioCoName;

[becka11]

Looks like I was able to do it with a WHERE clause added to your UNION query as follows:
SELECT ID, PortfolioCoName, InitialPartner AS Partner, "Initial" As Type FROM InitialPartner_PortfolioCo
UNION SELECT ID, PortfolioCoName, CurrentPartner, "Current" FROM InitialPartner_PortfolioCo WHERE CurrentPartner <> InitialPartner;