Results 1 to 6 of 6
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    MS Access - Help with Nested Query (Max version)

    Hi,



    I am having some extreme frustration with writing this query!

    I am trying to group "versioned" records and pull associated data with the highest versioned record.

    Code:
    ---ID-------VERSION-------AGE
       1001           1                 20
       1001           2                 30
       1005           12               15
       1006            1                13
    
    Result of Query:
    
       1001           2                30        
       1005           12              15
       1006            1               13
    I have tried using the nested query with "Select TOP 1 where ID=ID" method but I cannot get it to work.

    I have attached a sample with my SQL string in code. If you could, please have a look and see where I am going wrong!

    Thanks for your help!!!


    TestNestedQuery.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably could have just posted the query SQL.

    Remove the red comma before FROM and the query will run without error.

    SELECT Q_Main.QuoteID, Q_Main.Version, Q_Main.Fruit, Q_Main.Age, FROM Q_Main WHERE (((Q_Main.ID) In (SELECT TOP 1 TEMP.ID FROM Q_Main AS TEMP WHERE TEMP.ID=Q_Main.ID ORDER BY TEMP.version DESC))) ORDER BY Q_Main.QuoteID;

    However, using ID field in WHERE clause won't accomplish, use QuoteID instead.

    Why button code setting listbox RowSource?
    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
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Hi,

    Thanks for reply.

    Using quoteID does not accomplish this. This is the issue i'm having.

    Every instance of each quoteID is displayed which is not how this Query should work.

    Code:
    "SELECT Q_Main.QuoteID, Q_Main.Version, Q_Main.Fruit, Q_Main.Age FROM Q_Main WHERE (((Q_Main.QuoteID) In (SELECT TOP 1 TEMP.QuoteID FROM Q_Main AS TEMP WHERE TEMP.QuoteID=Q_Main.QuoteID ORDER BY TEMP.version DESC))) ORDER BY Q_Main.QuoteID;"

    Regards

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, I meant the WHERE clause of the nested query only, not the outer query.
    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
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Hi,

    This is incredibly annoying.

    This no longer is working. This morning when I replied, the query you provided at least DID SOMETHING. Now it does not.

    This does absolutely nothing:

    Code:
    sql = "SELECT Q_Main.QuoteID, Q_Main.Version, Q_Main.Fruit, Q_Main.Age FROM Q_Main WHERE (((Q_Main.QuoteID) In (SELECT TOP 1 TEMP.QuoteID FROM Q_Main AS TEMP WHERE TEMP.QuoteID=Q_Main.QuoteID ORDER BY TEMP.version DESC))) ORDER BY Q_Main.QuoteID;"
    I can't get any solution here that returns any results.

    Can you please provide a working copy of my DB with your fixes?

    Thank you

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As I said, QuoteID goes in the inner query WHERE clause only.

    sql = "SELECT Q_Main.QuoteID, Q_Main.Version, Q_Main.Fruit, Q_Main.Age FROM Q_Main WHERE (((Q_Main.ID) In (SELECT TOP 1 TEMP.ID FROM Q_Main AS TEMP WHERE TEMP.QuoteID=Q_Main.QuoteID ORDER BY TEMP.version DESC))) ORDER BY Q_Main.QuoteID;"
    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.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-11-2019, 03:06 PM
  2. Replies: 7
    Last Post: 02-05-2018, 08:14 PM
  3. Replies: 6
    Last Post: 02-18-2016, 10:15 AM
  4. Access Version Query
    By jo8701 in forum Database Design
    Replies: 1
    Last Post: 02-16-2012, 03:01 PM
  5. Nested Query Question Access 2007
    By databased in forum Queries
    Replies: 5
    Last Post: 10-15-2010, 07:22 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