Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    12

    Unanswered: Query using the totals (sigma) last feature

    payroll data with the following fields.
    employee id (E_Id)
    pay rate date (PRD)
    pay rate $ (PR$)

    i have ran a query (q1) which sorts the data by E_ID, then by PRD.
    i then used a second query (q2) drawing from the first query (q1).
    within q2 i am trying to group the data and obtain the most recent pay rate which is from the most recent date.

    now i have tried using the totals (sigma) feature in access.
    group E_Id
    Last PRD
    Last PR$

    however, this does not give me the results i am looking for.
    example

    E_Id PRD PR$
    3215 20090727 25
    3215 20100101 33

    sometimes i get the last record and sometimes i do not.
    in this case using last i was expecting the following record.

    E_Id PRD PR$
    3215 20100101 33

    please advise

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You want Max() rather than Last(); Last() doesn't do what you think it does. You may also find this helpful:

    Most recent value
    Paul

  3. #3
    Join Date
    Jan 2011
    Posts
    12

    U solved the problem

    thanks
    i had considered that and figured it would work but thought i could do it all in 1 step.

    what does the "last" function do.

    thanks again.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  5. #5
    Join Date
    Jan 2011
    Posts
    12
    thanks for putting me there. talk about a useless feature in MS access.
    unfortunately i do not know enough about programming for modules to permit me to do what i am looking for when using method #3.
    however, now that i know a work around, i am on my way.

    thanks again.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo.
    Paul

Posting Permissions

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