Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: help with selecting data

    hi guys! help please..I have a table named Enrollment with 3 columns (EnrollmentID,StudentID,DateOfEnrollment) EnrollmentID is the primary key thus, the value for this column must be unique while StudentID could have same value with other rows. My question is how will I select the latest enrollment of each student based from DateOfEnrollment? Please see sample below. Thanks in advance!


    DB Data
    EnrollmentID | StudentID | DateofEnrollment

    1 1 08/31/2007 10:50:29 AM
    2 2 09/1/2007 10:50:29 AM
    3 1 09/2/2007 10:50:29 AM
    4 2 09/4/2007 10:50:29 AM
    5 3 10/6/2007 10:50:29 AM


    Result that im trying to achieve.
    EnrollmentID | StudentID | DateofEnrollment

    3 1 09/2/2007 10:50:29 AM
    4 2 09/4/2007 10:50:29 AM
    5 3 10/6/2007 10:50:29 AM

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Create a query based on your table.
    Use the Totals button.
    Select Max from the Totals row for your DateofEnrollment field. Leave the others as Group By.

    View the query
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2005
    Posts
    74
    Thanks but...can you please elaborate on that.
    Use the Totals button.
    Where can i find that button?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why do you have an enrolmentID when the combination of studentID and DateofEnrollment is unique?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2005
    Posts
    74
    why use 2 colums to have a unique identifier when you can have one?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    For one, your curent design allows entry of duplicate data...
    Code:
    EnrollmentID | StudentID | DateofEnrollment
    
     3             1           09/2/2007 10:50:29 AM
     4             1           09/2/2007 10:50:29 AM
     5             1           09/2/2007 10:50:29 AM
     6             1           09/2/2007 10:50:29 AM
     7             1           09/2/2007 10:50:29 AM
    But nice try/response
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by daimous
    Thanks but...can you please elaborate on that.
    Where can i find that button?
    It's the one that looks like AutoSum in Excel... the greek sigma symbol on the toolbar.

    You can also use View - Totals instead of clicking the button.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Sep 2005
    Posts
    74
    Thanks for that reply guys..but what i just did is select all the enrollment for a particular user and order it by DateOfENrollment by Descinding and get the first row. hehehe...

Posting Permissions

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