Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Using Max function and adding a field

    I have a table with two fields - a STUDENT_ID and an ADVISOR_CHANGE_DATE. Most students have multiple dates. The second table has STUDENT_ID, and ADVISOR_ID. I need to show the date when each student most recently changed advisors, and the ID of the advisor.

    I have a query that gives the STUDENT_ID and the Max of the CHANGE_DATE. But when I add in the ADVISOR ID from the other table, the query returns all dates and advisor IDs for each student.

    How do I get Access to only show the most recent date and that Advisor?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    May need to use select top 1 and a group by clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your database engine is SQL Server, you could use Windowing functions to easily get this data.

    If not, you could use a subquery. While this isn't valid MS-Access code, it ought to convey the concept:
    Code:
    CREATE TABLE Student_Advisors (
       student_id       INT         NOT NULL
       FOREIGN KEY (student_id) REFERENCES Persons (person_id)
    ,  advisor_id       INT         NOT NULL
       FOREIGN KEY (advisor_id) REFERENCES Persons (person_id)
    ,  effective_date   DATETIME    NOT NULL
       PRIMARY KEY (student_id, effective_date)
       )
    
    SELECT student_id, advisor_id, effective_date
       FROM Student_Advisors AS a
       WHERE  effective_date = (SELECT Max(z.effective_date)
          FROM Student_Advisors AS z
          WHERE  z.student_id = a.student_id)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On second thought, no you can't. Using two tables to store this data creates an implicit Cartesian join.

    I don't know of any way to solve this without fixing the problem by combining the tables so that the student, advisor, and effective date are all on one row.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Also on second thoughts ....
    this data model is wrong

    have an entity for students
    and entity for advisors
    an entity that defiens the realtions shipbetween a student and an advisor (an intersection table), and anything else pertinent to that intersection such as date appointed and (possibly date dropped*)


    * you might need a 'date dropped' if there is a possibility that a student may have more than onme advisor
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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