Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    65

    Unanswered: last() doesn't return last value of query

    Hi all,

    i have a rough and ready query set so i can quickly find Id numbers not being used,

    SELECT Last(Val([StudentId])) AS Id
    FROM stud_admin_NSTUPERSONAL
    GROUP BY stud_admin_NSTUPERSONAL.SetId, stud_admin_NSTUPERSONAL.LeftSchool
    HAVING (((stud_admin_NSTUPERSONAL.SetId)="Current Ye") AND ((stud_admin_NSTUPERSONAL.LeftSchool)="n"))
    ORDER BY Last(Val([StudentId])) DESC;

    Table: stud_admin_NSTUPERSONAL
    'Current Ye' is a dataset and must be defined.
    im having to chane the id to a number using val as our software stores ID's as text!?!?!?!?!

    the result im getting is:
    Id
    4017

    wheras the last 4 values of my query when its not got the last function are:

    Id
    4090
    4089
    4088
    4087

    why is the query returning 4017 as the last in a list of values that goes to 4090.

    cheers

    greg

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'd use Max. It sounds to me that Last is not actually what you want to use.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    65
    of course! (slaps forehead!) last is for text strings right?

    cheers

    greg

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dubs
    of course! (slaps forehead!) last is for text strings right?
    Not quite. Last() is a somewhat contentious aggregate function. It is specific to Access (to my knowledge) and does not comply with the ANSI SQL standards.

    It returns the "last" created record (based on the grouping, where and having options you enter). So - if the student record for 4017 was created after the records for 4090, 4089 etc then this is the record returned.

    The reason Last() is a little controversial is that the order of the rows in a table has no logical meaning. Since everything is about the key, the whole key and nothing but the key each row is discrete and there is no relationship to other rows in the table other than that they describe similar entities. One entity is not really "before" or "after" any other. But they are in Access
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Access ........ does not comply with the ANSI SQL standards




    lol, not much does. not supporting the (+) joins is a complete joke.

  6. #6
    Join Date
    May 2006
    Posts
    65
    Nice one pootle thats gives me a definitive explantion, think ill avoid the function in future as ill use sql if i ever need to find the last created record in a table

    cheers

    greg

Posting Permissions

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