Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: Access Query Help

    I have a query that retrieves a listing of accounts for each person. I need to modify the query to retrieve the last action for a group of records, by date. Example, one users funding percentage was changed once in August and twice in September. I need to include the one from August, which has a sequence value of 0 and and the one from September that has a sequence value of 1. I've been able to group the records by account and date, but I don't know how to list the highest sequence for a given action date per account. Of course, this sql statement will be used for every account and for multiple people.

    I'd appreciate the help, file with columns and data are attached.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Invisible attachment
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    OK, the attachment didn't take.

    Columns: NAME, ANNUAL_RT, Acct_CD, DIST_PCT, FUNDING_BEG_DT, JOB_EFFSEQ, ACTION_DT

    Data, keeping it simple, think of one user listed 3 times. Action dates are, 8/30/2009, 9/5/2009, and 9/5/2009. Sequence value for 8/30 is 0. Sequence values for 9/5 are 0 and 1. How do you only show the value for 8/30 and the one value from 9/5 with a sequence of 1?

    I have this:
    SELECT NAME, ANNUAL_RT, Acct_CD, DIST_PCT, FUNDING_BEG_DT, JOB_EFFSEQ, ACTION_DT
    FROM DBTHFY10
    GROUP BY NAME, ANNUAL_RT, Acct_CD, DIST_PCT, FUNDING_BEG_DT, JOB_EFFSEQ, ACTION_DT
    ORDER BY Acct_CD, Max(DBTHFY10.ACTION_DT);

Posting Permissions

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