Results 1 to 11 of 11
  1. #1
    gunapriyan is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    8

    Need Query to fetch last row in a table

    Dear Pals,

    Actually I have a table named as "Advance"

    It contains 4 fields,

    1. Emp_Name
    2. Date_of_Advance
    3. Advance
    4. Tot_Advance.

    I have enter three various values for each employee to that table....like

    Katheeja - 19/02/2010 - 2000 - 2000
    Katheeja - 24/11/2011 - 2500 - 4500
    Katheeja - 01/03/2012 - 1000 - 5500
    Guna - 20/02/2010 - 1000 - 1000
    Guna - 25/11/2011 - 2500 - 3500
    Guna - 05/03/2012 - 3000 - 6500

    Actually I need to fetch last row from above.

    To katheeja:

    Katheeja - 01/03/2012 - 1000 - 5500

    To Guna:

    Guna - 05/03/2012 - 3000 - 6500



    I need query for that.

    Thanks in advance.

    - Gunapriyan

  2. #2
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    Is it just the last record form the 3 or will it it be the most recent date of the 3?
    To use the most recent date you can do a Group By on all the fields except the date field where you would use Last.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect you'd want Max rather than Last. See if this helps:

    http://www.baldyweb.com/LastValue.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    gunapriyan is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    8
    Dear SoftwareMatters and Paldy,

    Thanks for your nice reply.

    Actually i need the recent row or last row to each employee to manipulate their advance.

    Already I have tried like as follow,

    select top 1 * from advance where emp_name='Katheeja' order by date_of_advance desc

    But it shows last-1 row.....

    One more thing, i have one key, that is i need the latest date advance which is bought by employee...

    If you give query its very helpful to me.

    Thanks in advance.....

    Waiting for your nice reply.

    Regards,

    Gunapriyan

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try the 2-query solution from the link? It should produce the result you asked for in the first post.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    gunapriyan is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    8
    Dear Paldy,

    Before try that I need another one clarification with you. That is
    my table contains the following 5 rows.

    AdvanceEmp_NameDate_of_AdvanceAdvanceTot_AdvanceKatheeja21/02/20102000.002000.00Katheeja22/02/20103000.004950.00Katheeja03/01/20112600.007600.00Katheeja24/11/20112000.006945.00Katheeja13/03/20122345.009295.00

    I wrote the query to get max(date) like,

    select max(date_of_advance) from advance

    But It returns,

    24/11/2011. I need 13/03/2012...

    If you help that, I can use 2 queries...

    I hope you can understand my situation....

    thanks in advance.

    Regards,

    Gunapriyan

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The formatting there is unreadable, but I would expect you to have a GROUP BY clause on employee, so you get the max date per employee. What is the data type of that date field?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    gunapriyan is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    8
    Dear Friend,

    Sorry for the unreadable.

    It is,

    -----------------------------------
    Katheeja 21/02/2010 2000.00 2000.00
    Katheeja 22/02/2010 3000.00 4950.00
    Katheeja 03/01/2011 2600.00 7600.00
    Katheeja 24/11/2011 2000.00 6945.00
    Katheeja 13/03/2012 2345.00 9295.00
    -----------------------------------------------------------------
    And to set "dd/MM/yyyy" format i have changed the date_of_advance field as "String". Thanks for your find out.

    And i have tried group by clause.....same 24/11/2011 only retrieve...

    Thanks

    Gunapriyan

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're getting the unpredictable result because the field is text. Actually it's quite predictable; "24" is the max value when sorting alphabetically. If you want proper results, the field should be Date/Time. You can format the output on forms and reports.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    gunapriyan is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    8
    Dear Friend,

    Thanks for your timely and continuous help.

    I will correct them.

    Thankyou very much...

    Yours
    GUNAPRIYAN

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help; bedtime here!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 0
    Last Post: 12-21-2009, 12:15 AM
  2. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  3. All table query
    By tallerthantrees in forum Queries
    Replies: 1
    Last Post: 08-20-2009, 03:18 AM
  4. Using same query with different table
    By fashoda in forum Queries
    Replies: 1
    Last Post: 12-14-2005, 12:05 AM
  5. Use same query with different table
    By fashoda in forum Access
    Replies: 1
    Last Post: 12-09-2005, 08:44 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