Complex MS Access SQL query - how can I acheive this?

[SIMMS7400]

Hi Folks -



I'm trying to create a SQL query (or whatever will work) but having some trouble getting the expected results do the complexity. Allow me to explain. I have attached an excel workbook which contains my two tables and then a tab which shows my expected results.

I need to join [rdAssets] on [rdInvestments] using the [AssetID] column which is obviously available in both tables. However, I want to return the following columns only if [PartnershipAlias] is the same across the PFI codes:

    [rdAssets].[AssetID],
    [rdAssets].[AssetAlias],
    [rdInvestments].[PartnershipID],
    [rdInvestments].[PartnershipAlias]

in the [rdInvestments] table I have highlighed in green the records in green that meet this criteria while the records in red that don't. Please let me know if you need any additional information.

Here is my SQL query but need to add that additional layer of complexity but not sure how?

SELECT 
    [rdAssets].[AssetID],
    [rdAssets].[AssetAlias],
    [rdInvestments].[PartnershipID],
    [rdInvestments].[PartnershipAlias]
FROM [rdAssets] 
INNER JOIN [rdInvestments] ON [rdAssets].[AssetID] = [rdInvestments].[AssetID]
WHERE 
    ([rdAssets].[PartnershipID] IS NULL AND 
    [rdInvestments].[PartnershipID] IS NOT NULL)

[ranman256]

if you are wanting the red items to show (but they wont), sometimes we think they are null but arent.
try:
([rdAssets].[PartnershipID] IS NULL) or ([rdAssets].[PartnershipID] = "")
no additional criteria.

Sometimes the NULL settings dont produce the correct filter.

then if that works, then do it for the [rdInvestments].[PartnershipID] too.

[SIMMS7400]

No, I want the green records to show. Look at the Result tab on my excel sheet. My sql query was only to join the join...need help getting my desired results

[ArviLaanemets]

1. Instead rdInvestments I used rdAssetInvestments as table name - it mirrors better the real use of it. Accordingly I renamed a couple of fields too;
2. The value of AssetAlias is linked to AssetID - so no need to have it in rdAssetInvestments.
3. There is a lot of other table structure problems, like several fields having more than 1 type of information - PartnershipAlias contains alias and date, InvestmentsID containing a real investment id (I'd recommend a separate table for investments too), and something like unique asset investment row number, etc. Without those, probably the query would be much simpler, but correcting all this would take too much time - so I let it be.
The query returning what you did want would be

SELECT DISTINCT ai03.AssetID, asset.AssetAlias, ai.PartnershipID, ai.PartnershipAlias
FROM
     (
          (
               SELECT ai02.AssetID, COUNT(ai02.AssetID) AS AssetCnt2
               FROM
                    (SELECT ai01.AssetID, ai01.PartnershipID, ai01.PartnershipAlias, COUNT(ai01.AssetID) As AssetCnt1 FROM rdAssetInvestments ai01 GROUP BY ai01.AssetID, ai01.PartnershipID, ai01.PartnershipAlias) AS ai02 
               GROUP BY ai02.AssetID 
               HAVING COUNT(ai02.AssetID)=1
          ) AS ai03 INNER JOIN rdAssetInvestments ai ON ai.AssetID = ai03.AssetID
     )
     INNER JOIN rdAssets As asset ON asset.AssetID = ai03.AssetID

[SIMMS7400]

1. Instead rdInvestments I used rdAssetInvestments as table name - it mirrors better the real use of it. Accordingly I renamed a couple of fields too;
2. The value of AssetAlias is linked to AssetID - so no need to have it in rdAssetInvestments.
3. There is a lot of other table structure problems, like several fields having more than 1 type of information - PartnershipAlias contains alias and date, InvestmentsID containing a real investment id (I'd recommend a separate table for investments too), and something like unique asset investment row number, etc. Without those, probably the query would be much simpler, but correcting all this would take too much time - so I let it be.
The query returning what you did want would be

SELECT DISTINCT ai03.AssetID, asset.AssetAlias, ai.PartnershipID, ai.PartnershipAlias
FROM
     (
          (
               SELECT ai02.AssetID, COUNT(ai02.AssetID) AS AssetCnt2
               FROM
                    (SELECT ai01.AssetID, ai01.PartnershipID, ai01.PartnershipAlias, COUNT(ai01.AssetID) As AssetCnt1 FROM rdAssetInvestments ai01 GROUP BY ai01.AssetID, ai01.PartnershipID, ai01.PartnershipAlias) AS ai02 
               GROUP BY ai02.AssetID 
               HAVING COUNT(ai02.AssetID)=1
          ) AS ai03 INNER JOIN rdAssetInvestments ai ON ai.AssetID = ai03.AssetID
     )
     INNER JOIN rdAssets As asset ON asset.AssetID = ai03.AssetID

Wow!!! That worked like a charm!!

I was wondering if there was a way to just return [AssetID] & [AssetAlias] for the situations where there are multiple DIFFERENT [PartnershipID]+[PartnershipAlias] Alias values for a given PFC code? Essentially, return the PFC code based on the RED highlights from my example?

Thank you!

[SIMMS7400]

Wow!!! That worked like a charm!!

I was wondering if there was a way to just return [AssetID] & [AssetAlias] for the situations where there are multiple DIFFERENT [PartnershipID]+[PartnershipAlias] Alias values for a given PFC code? Essentially, return the PFC code based on the RED highlights from my example?

Thank you!

HI, was wondering if you could assist?

[ArviLaanemets]

I'm sure it is possible, but I can't look into it at least for couple of days currently.