Results 1 to 3 of 3

Thread: Query question

  1. #1
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    Unanswered: Query question

    Table with 5 fields
    MbrNumber
    Address
    Title
    ChangeCode
    EndDate

    Members can be in this table more than once with multiple ChangeCodes.
    They can be in the table with the same codes and/or different codes.
    Some members are only in the table once.

    I have made a table of only members where the ChangeCode = "D"

    Based on this table, I need each member to appear only once. If they are in this table multiple times, I only want the most recent EndDate record for that member. If the member has 3 "D" records all with the same EndDate, then any one of the records is OK.

    Can anyone help me with the syntax?

    Thank you very much.

    Michael

  2. #2
    Join Date
    Dec 2001
    Posts
    79
    Select MbrNumber, Address, Title, ChangeCode, Max(EndDate) as MaxEndDate
    From MyTable
    Group By MbrNumber, Address, Title, ChangeCode;

    Hope this helps,
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers
    http://www.peterssoftware.com

  3. #3
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    Thanks !!`

    Thank you Peter - it worked wonderfully.

    I appreciate your time and knowledge.

    Sincerely,
    Michael

Posting Permissions

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