Results 1 to 7 of 7
  1. #1
    SIMMS7400 is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2016
    Posts
    7

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


    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:

    Code:
        [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?

    Code:
    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)
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,698
    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.

  3. #3
    SIMMS7400 is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2016
    Posts
    7
    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

  4. #4
    Join Date
    Apr 2017
    Posts
    1,217
    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
    Code:
    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

  5. #5
    SIMMS7400 is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2016
    Posts
    7
    Quote Originally Posted by ArviLaanemets View Post
    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
    Code:
    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!

  6. #6
    SIMMS7400 is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2016
    Posts
    7
    Quote Originally Posted by SIMMS7400 View Post
    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?

  7. #7
    Join Date
    Apr 2017
    Posts
    1,217
    I'm sure it is possible, but I can't look into it at least for couple of days currently.

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

Similar Threads

  1. Replies: 8
    Last Post: 10-17-2018, 11:52 AM
  2. Replies: 3
    Last Post: 02-25-2018, 09:35 AM
  3. Replies: 5
    Last Post: 05-23-2014, 11:08 AM
  4. Very Complex Query Help
    By Epidural in forum Queries
    Replies: 1
    Last Post: 05-14-2012, 12:13 PM
  5. Complex issue in MS access
    By ganeshvenkatram in forum Access
    Replies: 6
    Last Post: 06-14-2011, 11:22 PM

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 - Senior Forums