Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to get last records (MSACCESS 2003)

    Sorry, I could not find my earlier thread to add this to as its a continuation. I have a working query

    mysql = "SELECT Top 7 TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    mysql = mysql & "AS NName, TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out]"
    mysql = mysql & " FROM TXMASTERS LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1]))"
    mysql = mysql & " ORDER BY TXCLIPS.ID2 ASC "

    I am trying to retieve the LAST 7 records of a table, ordered in TXCLIPS.Start order ascending

    I tried amending my last line mysql = mysql & " ORDER BY TXCLIPS.START ASC ", but it does not return the last 7 records.

    Any suggestions, many thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query to get last records (MSACCESS 2003)

    Exactly how do you define the "last 7 records"? Whatever field it is (perhaps a data/time added field), you need to sort it in DESC order, then use the TOP 7 clause.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Thanks Mark. The primary key of the TXCLIPS table is named ID2. In theory as each record has been added in the table the ID2 value should be in ascending order, however the field TXCLIPS.Start should also be in ascending order, however the table contains a mixture of orders in the TXCLIPS.Start entry (Its an external time value) Therefore what I am trying to do is have my query return the last number of records, but sorted in the times field ascending order. Hope that is a bit clearer. Regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query to get last records (MSACCESS 2003)

    I think you will have to use 2 queries. The first one will find the top 7 when sorted in descending order.
    Then create a second query that reverses the sort order of the records returned by the first.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Thenks John. I have managed to get something that works:

    mysql = "SELECT * FROM ( SELECT TOP 5 TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons,"
    mysql = mysql & " TXCLIPS.ID2, TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out],"
    mysql = mysql & " TXCLIPS.ID1 FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1])) "
    mysql = mysql & " ORDER BY TXCLIPS.ID2 DESC ) T"
    mysql = mysql & " ORDER BY ID2 ASC"

    However I am trying to insert another part which concatinates a name, however the query produces no output;

    'mysql = "SELECT * FROM ( SELECT TOP 5 TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    mysql = mysql & " AS NNAME"
    mysql = mysql & " TXCLIPS.ID2, TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out],"
    mysql = mysql & " TXCLIPS.ID1 FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1])) "
    mysql = mysql & " ORDER BY TXCLIPS.ID2 DESC ) T"
    mysql = mysql & " ORDER BY ID2 ASC"

    Although anorther query using the concantinate works:

    ' mysql = "SELECT DISTINCT TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    mysql = mysql & "AS NName, TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out]"
    mysql = mysql & " FROM TXMASTERS LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1]))"
    mysql = mysql & " ORDER BY TXCLIPS.ID2 ASC "

    I cannot see why pasting the bit in the middle query should die? Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to get last records (MSACCESS 2003)

    There's no comma after AS NNAME in the middle one.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Many,many thank Hans. I have been staring at the code over and over, you spotted it, and it works. So greatful yet again.
    All the very best regards and thanks again

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to get last records (MSACCESS 2003)

    I pasted your code into a procedure and inserted a line

    MsgBox mysql

    below it, then ran the procedure. The problem became visible then.
    Attached Images Attached Images
    • File Type: jpg x.jpg (12.9 KB, 0 views)

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Thanks again Hans, I'll remember that one after I have removed the egg on my face. Regards

  10. #10
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Another variation query that I just cannot sort out. It gives me the correct last 3 records, but the field START ends up descending. If I change the last part to ASC it gives me the first 3 records of the table. Hope this is the last of my queries for a while. Thanks


    MYSQL = "SELECT * FROM ( SELECT TOP 3 TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS NName,"
    MYSQL = MYSQL & " TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out],"
    MYSQL = MYSQL & " TXCLIPS.ID1 FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    MYSQL = MYSQL & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1])) "
    MYSQL = MYSQL & " ORDER BY TXCLIPS.START DESC ) T"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to get last records (MSACCESS 2003)

    You have omitted the last line

    MYSQL = MYSQL & " ORDER BY START ASC"

  12. #12
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to get last records (MSACCESS 2003)

    Thanks. I had been trying this, but it kept saying it could not find start.

    MYSQL = MYSQL & " ORDER BY TXCLIPS.START DESC ) T"
    MYSQL = MYSQL & " ORDER BY TXCLIPS.START ASC "

    Home and dry, thanks again.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to get last records (MSACCESS 2003)

    Since you assign the alias T to the subquery, the following should work too:

    MYSQL = MYSQL & " ORDER BY T.START ASC "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •